A表有以下几个字段:
ID,ColumnName
B表有以下几个字段:
ID,AID,Amount其中,A.ID是唯一的,B.AID与A.ID对应,现在如果我有一下数据:A表:
ID ColumnName
1 name1
2 name2B表:
ID AID Amount
1 1 2
2 1 4
3 2 5
4 2 7请问怎么写SQL语句查出这样的数据出来:??
A.ID, A.ColumnName, SUM(B.Amount) as SAmount
1 name1 6
2 name2 12
ID,ColumnName
B表有以下几个字段:
ID,AID,Amount其中,A.ID是唯一的,B.AID与A.ID对应,现在如果我有一下数据:A表:
ID ColumnName
1 name1
2 name2B表:
ID AID Amount
1 1 2
2 1 4
3 2 5
4 2 7请问怎么写SQL语句查出这样的数据出来:??
A.ID, A.ColumnName, SUM(B.Amount) as SAmount
1 name1 6
2 name2 12
from
(
select AID,SUM(Amount) as SAmount
from B
group by AID
) C
left join A on A.ID=C.AID
select A.ID, A.ColumnName,C.SAmount
from Aleft join (
select AID,SUM(Amount) as SAmount
from B
group by AID
) C on A.ID=C.AID
这样就完全正确了,谢谢wuyi8808 !
select a.id,a.ColumnName,sum(B.Amount) as SAmount
from A a left jion B b on a.id=b.
group by a.id,a.ColumnName
这种方法可能会麻烦一些,如果A表不仅仅只有id和ColumnName,这两个字段,类似下面这样的语句就不能正常执行。select a.*,sum(B.Amount) as SAmount
from A a left jion B b on a.id=b.
group by a.id,a.ColumnName