sqlserver聯合查詢
㈠ sqlserver多表聯合查詢
selectc.discount
froma,b,cwherea.hw_name=b.hw_name
andb.sort_id=c.sort_id
anda.hw_name='蘋果'
㈡ sqlserver 觸發器 多表聯查
CREATE TRIGGER [dbo].[表1_UPDATE_表2] --這里是觸發器名稱，最好一目瞭然的
ON [dbo].[表1]
FOR UPDATE,INSERT,DELETE --更新,插入,刪除時觸發（根據實際情況，可以只用一個）
AS
BEGIN
--下面是你的語句，我沒有修改，只是調整了排版和大小寫
UPDATE 表2
SET name=0,age=''
FROM 表2 INNER JOIN
(
SELECT DISTINCT ABS(id) AS billid,sale_z.ele_sordercode
FROM 表3
WHERE ele_sordercode<>''
) 表3
ON 表2.tid=表3.id LEFT JOIN
(
SELECT MAX(id) AS id, referbillid
FROM 表1
WHERE referbillid<>0
GROUP BY referbillid
) 表1 ON 表3.id=表1.id
WHERE name=1 AND refer.id IS NULL
END
㈢ sqlserver 同時查詢多表數據
在sqlserver中新建個視圖，很直觀看，相查哪個數據，sql語句直接就出來了。
㈣ SQLServer兩張表聯合查詢（一對多）...
select t1.U_Id,t1.U_Name,sum(isnull(t2.G_Price,0)) as G_Price
from Users t1 left join Goods t2
on t1.U_Id=t2.G_UId
group by t1.U_Id,t1.U_Name
order by t1.U_Id
㈤ SQLserver 聯合查詢排序問題
不知道你原來的sql語句啥樣子啊。。。
SELECT
ISNULL(sale_item, '總計') AS item,
CASE
WHEN GROUPING(sale_item) = 1
AND GROUPING(STR(DATEPART(qq, sale_date))) = 1
THEN '總計'
WHEN GROUPING(sale_item) = 0
AND GROUPING(STR(DATEPART(qq, sale_date))) = 1
THEN '小計'
ELSE
STR(DATEPART(qq, sale_date))
END AS sale_q,
SUM(sale_money) [money]
FROM
sale_report
GROUP BY
sale_item, STR(DATEPART(qq, sale_date)) WITH ROLLUP;
item sale_q money
---- ---------- ----------------------------------------
A 1 180810.00
A 2 182819.00
A 3 184828.00
A 4 184828.00
A 小計 733285.00
B 1 180.00
B 2 455.00
B 3 735.00
B 4 1012.00
B 小計 2382.00
C 1 1398.00
C 2 1426.00
C 3 1457.00
C 4 1457.00
C 小計 5738.00
T 3 100.00
T 小計 100.00
總 總計 741505.00
(18 行受影響)
<hr/>
如果希望 總計、小計顯示在上面， 那麼 ORDER BY 設置一下.
SELECT
ISNULL(sale_item, '總計') AS item,
CASE
WHEN GROUPING(sale_item) = 1
AND GROUPING(STR(DATEPART(qq, sale_date))) = 1
THEN '總計'
WHEN GROUPING(sale_item) = 0
AND GROUPING(STR(DATEPART(qq, sale_date))) = 1
THEN '小計'
ELSE
STR(DATEPART(qq, sale_date))
END AS sale_q,
SUM(sale_money) [money]
FROM
sale_report
GROUP BY
sale_item, STR(DATEPART(qq, sale_date)) WITH ROLLUP
ORDER BY
GROUPING(sale_item) desc, item,
GROUPING(STR(DATEPART(qq, sale_date))) desc
item sale_q money
---- ---------- ----------------------------------------
總 總計 741505.00
A 小計 733285.00
A 1 180810.00
A 2 182819.00
A 3 184828.00
A 4 184828.00
B 小計 2382.00
B 1 180.00
B 2 455.00
B 3 735.00
B 4 1012.00
C 小計 5738.00
C 1 1398.00
C 2 1426.00
C 3 1457.00
C 4 1457.00
T 小計 100.00
T 3 100.00
(18 行受影響)
㈥ sqlserver多表查詢
既然三個表都是有關聯的，先單表統計
，再合起來統計。。
select
A.*,BC.sum_B,BC.sum_C
from
A表
as
A
join
(
select
單位編碼,sum_B,sum_C
from
(select
單位編碼,sum(數據B)
as
sum_B
from
B表
group
by
單位編碼)
as
B
join
(select
單位編碼,sum(數據C)
as
sum_C
from
C表
group
by
單位編碼)
as
C
on
B.單位編碼=C.單位編碼
)
as
BC
on
A.單位編碼=BC.單位編碼
簡化下：
select
A.*,B.sum_B,C.sum_C
from
A表
as
A
join
(select
單位編碼,sum(數據B)
as
sum_B
from
B表
group
by
單位編碼)
as
B
on
A.單位編碼=B.單位編碼
join
(select
單位編碼,sum(數據C)
as
sum_C
from
C表
group
by
單位編碼)
as
C
on
A.單位編碼=C.單位編碼
㈦ sqlserver 兩表聯合查詢，且查詢指定日期下，所有類別的數據，無數據為Null
select B.MonthID,B.[Month],B.[Order],A.[Date],sum(isnull(A.Price,0))as Price from pp2 A left join pp B
on A.MonthID=B.MonthID
where B.[Order]<=5
group by B.MonthID,B.[Month],B.[Order],A.[Date]
order by B.MonthID
把表名換掉就行了。
㈧ 「sqlserver」三表聯如何查詢「sql」語句
假設學生表叫student,課程表叫class,選課表叫choose
1.三層嵌套的問題
select student.name from student where student.id IN
(select choose.sid from choose where choose.cid NOT IN
(select class.id from class where class.teacher='李明'))
2.一個內連接，一個嵌套
select student.name,avg(choose.score) from
student inner join choose on student.id=choose.sid
where student.id IN
(select choose.sid from choose
where choose.score<'60'
group by choose.sid
having count(choose.sid)>=2)
gruop by student.id
3.一個聯合查詢，一個嵌套查詢
select student.name from student
where student.id IN
(select c1.sid from choose c1 where choose.cid='1'
union
select c2.sid from choose c2 where choose.cid='2'
on c1.sid=c2.sid
)
4.其實就是自連接查詢和行列交換的問題：
select student.id,
(case choose.id when '1' then choose.score end) as 1號課成績,
(case choose.id when '2' then choose.score end) as 2號課成績,
from student inner join choose on student.id=choose.sid sc1,
student inner join choose on student.id=choose.sid sc2
where sc1.id='1'
and sc2.id='2'
and sc1.score>sc2.score