我用SQL:
select name,count(数学) as sx,count(语文) as yw from table1 group by name
union all
select name,count(数学) as sx,count(语文) as yw from table2 group by name
得到的结果为:
name sx yw
aaa 45 50
bbb 50 56
aaa 50 40我现在想把名字一样的成绩相加,上面的SQL如何改?变成:
name sx yw
aaa 95 90
bbb 50 56
select name,count(数学) as sx,count(语文) as yw from table1 group by name
union all
select name,count(数学) as sx,count(语文) as yw from table2 group by name
得到的结果为:
name sx yw
aaa 45 50
bbb 50 56
aaa 50 40我现在想把名字一样的成绩相加,上面的SQL如何改?变成:
name sx yw
aaa 95 90
bbb 50 56
(
select name,count(数学) as sx,count(语文) as yw from table1 group by name
union all
select name,count(数学) as sx,count(语文) as yw from table2 group by name
) table0
group by name
select name,count(数学) as sx,count(语文) as yw from table1,table2 group by name
select name,count(数学) as sx,count(语文) as yw from table1,table2 group by name
看来不行啊
还是需要嵌套
select a.name,count(a.数学) as sx,count(a.语文) as yw from (select * from table1,table2) as a group by a.name
from
(select a.id,a.name,a.num from t1 as a union all select b.id,b.name,b.num from t2 as b )
c
group by name
执行正确两张表结构相同 表结构为
CREATE TABLE [t1] (
[id] [int] NULL ,
[name] [char] (100) COLLATE Japanese_CI_AS NULL ,
[num] [int] NULL
) ON [PRIMARY]
GO