表 A: ID Name Amount 1 A 1 2 B 2 3 C 3 表B ID Name Amount 1 A 1 2 A 4 3 B 5 4 D 6 要得到结果: Name Amount A 6 B 7 C 3 D 6
----------------------------------------------
-
一个笨方法,应该还有更好的语句。 select s1.name, ((select sum(amount) from t1 where name = s1.name)+(select sum(amount) from t2 where name = s1.name)) as amount from t1 s1 group by name
1楼:不好意思,问题问错了! 问题如下: 表 A: ID Name Amount 1 A 1 2 B 2 3 C 3 表B ID Name Amount 1 A 1 2 A 4 3 B 5 4 D 6 要得到结果: Name A.Amount B.Amount A 1 5 (1+4) B 2 5 C 3 D 6 如果用以下 SQL 语句, select A.name,A1.Amount,A2.Amount from( select name from A1 Group By Name union all select name from A2 Group By Name )A LEFT JOIN A1 ON A.Name=A1.Name LEFT JOIN A2 ON A.Name=A2.Name Group By A.name ,A1.Amount,A2.Amount 得到的错误答案如下: Name Amount Amount A 1 1 A 1 4 B 2 5 C 3 Null D Null 6
----------------------------------------------
-
还是这个笨方法,应该还有更好的语句。 select s1.name, (select sum(amount) from t1 where name = s1.name) as Aamount, (select sum(amount) from t2 where name = s1.name) as Bamount from t1 s1 group by name