34,575
社区成员
发帖
与我相关
我的任务
分享
create table ceshi
(
id int not null,
GYS nvarchar(50) null,
HTD nvarchar(50) null,
SCS nvarchar(50) null
)
create table xinxi
(
xxid int not null,
id int not null,--ceshi表的id
number numeric(18,1) null
)
insert into ceshi values(1,'A1','B1','C1')
insert into ceshi values(2,'A1','B2','C1')
insert into ceshi values(3,'A2','B1','C1')
insert into ceshi values(4,'A2','B2','C2')
insert into ceshi values(5,'A3','B1','C1')
insert into ceshi values(6,'A3','B2','C2')
insert into xinxi values(1,1,18.3)
insert into xinxi values(2,2,16.1)
insert into xinxi values(3,3,21.5)
insert into xinxi values(4,4,22.0)
insert into xinxi values(5,5,3.9)
insert into xinxi values(6,6,7.4)
--想要的结果
GYS sum SCS
------------- ----------------- -----------------
A1 34.4 C1
A2 21.5 C1
A2 22.0 C2
A3 3.9 C1
A3 7.4 C2
select GYS,sum(number) sum,SCS from ceshi a inner join xinxi b on a.id=b.id group by GYS,SCS
SELECT A.GYS,SUM(B.number) [sum],A.SCS FROM ceshi A LEFT JOIN xinxi B ON A.id=B.id GROUP BY A.GYS,A.SCS
select GYS
,SUM(number) as SUM
,SCS
from ceshi a join xinxi b on a.id=b.xxid
group by GYS,SCS
/*
GYS SUM SCS
A1 34.4 C1
A2 21.5 C1
A2 22.0 C2
A3 3.9 C1
A3 7.4 C2
*/
select gys,sum(number) as [sum],scs from ceshi a,xinxi b where a.id=b.id group by gys,scs