新人求教，多表关联查询求和

z060831 发布于 2014-07-18 10:32， 543 次点击

CREATE TABLE A(A CHAR(1),B INT)
CREATE TABLE B(A CHAR(1),C INT)

INSERT INTO A(A,B) VALUES ('a',10)
INSERT INTO A(A,B) VALUES ('a',20)
INSERT INTO A(A,B) VALUES ('a',30)
INSERT INTO A(A,B) VALUES ('b',5)
INSERT INTO A(A,B) VALUES ('b',15)
INSERT INTO B(A,C) VALUES ('a',100)
INSERT INTO B(A,C) VALUES ('a',200)

怎么查询可以得到结果：
A   B   C
a   60  300
b   20   0

4 回复

tlliqi2014-07-20 18:28

两表没相同字段不能查询

volte2014-07-22 15:29

这个就是楼主没解释清楚，表名用A\B表字段也用A\B表述。表达不明确，提问题不上心；
select A.ColumnA, A.SumB, B.ColumnA, B.SumC from
(select ColumnA, sum(ColumnB) as SumB from TableA group by ColumnA) A outer join
(select ColumnA, sum(ColumnC) as SumC from TableB group by ColumnA) B
on A.ColumnA = B.ColumnA

volte2014-07-22 15:29

具体再检查下，随手写的，没测试过！

mxbing19842014-08-09 16:16

回复楼主 z060831

Select A.A,SUM(B) As B,SUM(C) As C
From A Left Join B On A.A=B.A
Group By A.A