有如下 三个 表
test1
id name
1 A
2 Btest2
id test1_id qty
1 1 1
3 1 2
4 2 1
5 2 2
6 2 3test3
id test2_id qty
1 1 1
2 1 2
4 5 4
5 1 5
6 6 6
主从关系为test2是 test1的 子表,test3是 test2的 子表 现在需要求得如下 结果
test1的自增ID test1的name test2的sum和 test3的sum和
1 a 3 8
2 b 6 10
但我使用
SELECT A.ID,A.NAME,
ISNULL(SUM(B.QTY),0) AS BQTY,ISNULL(SUM(C.QTY),0) AS CQTY
FROM TEST1 A LEFT OUTER JOIN TEST2 B ON A.ID = B.TEST1ID
LEFT OUTER JOIN TEST3 C ON B.ID = C.TEST2ID
GROUP BY A.ID,A.NAME生成的结果是 1 A 5 8
2 B 6 10请 给位高手帮忙看看有没有什么解决方案,目前不打算考虑使用 临时表或表变量等
test1
id name
1 A
2 Btest2
id test1_id qty
1 1 1
3 1 2
4 2 1
5 2 2
6 2 3test3
id test2_id qty
1 1 1
2 1 2
4 5 4
5 1 5
6 6 6
主从关系为test2是 test1的 子表,test3是 test2的 子表 现在需要求得如下 结果
test1的自增ID test1的name test2的sum和 test3的sum和
1 a 3 8
2 b 6 10
但我使用
SELECT A.ID,A.NAME,
ISNULL(SUM(B.QTY),0) AS BQTY,ISNULL(SUM(C.QTY),0) AS CQTY
FROM TEST1 A LEFT OUTER JOIN TEST2 B ON A.ID = B.TEST1ID
LEFT OUTER JOIN TEST3 C ON B.ID = C.TEST2ID
GROUP BY A.ID,A.NAME生成的结果是 1 A 5 8
2 B 6 10请 给位高手帮忙看看有没有什么解决方案,目前不打算考虑使用 临时表或表变量等
ISNULL(SUM(B.QTY),0) AS BQTY,ISNULL(SUM(C.QTY),0) AS CQTY
FROM TEST1 A LEFT OUTER JOIN TEST2 B ON A.ID = B.TEST1ID
LEFT OUTER JOIN (Select TEST2ID, SUM(QTY) As QTY From TEST3 Group By TEST2ID) C ON B.ID = C.TEST2ID
GROUP BY A.ID,A.NAME
from test1 as a
inner Join test2 as b on a.id=b.test1_id
inner Join test3 as c on a.id=c.test2_id
group by a.id,a.name
(id Int,
name Varchar(10))
Insert test1 Select 1, 'A'
Union All Select 2, 'B'Create Table test2
(id Int,
test1id Int,
qty Int)
Insert test2 Select 1, 1, 1
Union All Select 3, 1, 2
Union All Select 4, 2, 1
Union All Select 5, 2, 2
Union All Select 6, 2, 3Create Table test3
(id Int,
test2id Int,
qty Int)
Insert test3 Select 1, 1, 1
Union All Select 2, 1, 2
Union All Select 4, 5, 4
Union All Select 5, 1, 5
Union All Select 6, 6, 6
GOSELECT A.ID,A.NAME,
ISNULL(SUM(B.QTY),0) AS BQTY,ISNULL(SUM(C.QTY),0) AS CQTY
FROM TEST1 A LEFT OUTER JOIN TEST2 B ON A.ID = B.TEST1ID
LEFT OUTER JOIN (Select TEST2ID, SUM(QTY) As QTY From TEST3 Group By TEST2ID) C ON B.ID = C.TEST2ID
GROUP BY A.ID,A.NAME
GO
Drop Table TEST1, TEST2, TEST3
--Result
/*
ID NAME BQTY CQTY
1 A 3 8
2 B 6 10
*/
Select a.id,a.name,sum(b.qty) as sumt2,sum(c.qty) as sumt3
from test1 as a
inner Join test2 as b on a.id=b.test1_id
inner Join test3 as c on a.id=c.test2_id
group by a.id,a.name
--------這個語句得到的結果和樓主要的是不一樣的