有如下表
ID SPMC SL JE
01 AAA 1 10
01 BBB 1 20
02 CCC 1 30
02 DDD 1 40
02 EEE 2 50
03 FFF 2 60
03 GGG 2 70我想结果如下,按ID相同的累加ID SPMC SL JE ZJE
01 AAA 1 10 30
01 BBB 1 20
02 CCC 1 30 120
02 DDD 1 40
02 EEE 2 50
03 FFF 2 60 130
03 GGG 2 70
ID SPMC SL JE
01 AAA 1 10
01 BBB 1 20
02 CCC 1 30
02 DDD 1 40
02 EEE 2 50
03 FFF 2 60
03 GGG 2 70我想结果如下,按ID相同的累加ID SPMC SL JE ZJE
01 AAA 1 10 30
01 BBB 1 20
02 CCC 1 30 120
02 DDD 1 40
02 EEE 2 50
03 FFF 2 60 130
03 GGG 2 70
from ta a
left join (select id,min(spmc) as spmc ,sum(je)as zje from ta group by id) b
on a.id= b.id and a.spmc = b.spmc
then select sum(je) from tb A where A.id=id end
from tb
select
a.*,b.ZJE
from
tb
left join
(select ID,sum(ZJE) as ZJE from tb group by id)b
on
a.id=b.id
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([ID] varchar(2),[SPMC] varchar(3),[SL] int,[JE] int)
insert [tb]
select '01','AAA',1,10 union all
select '01','BBB',1,20 union all
select '02','CCC',1,30 union all
select '02','DDD',1,40 union all
select '02','EEE',2,50 union all
select '03','FFF',2,60 union all
select '03','GGG',2,70
select tx.*,b from tb tx left join (select id,sum(je) b from [tb] group by id) t
on tx.id=t.id and spmc=(select min(spmc) from tb where id=tx.id)/*
ID SPMC SL JE b
---- ---- ----------- ----------- -----------
01 AAA 1 10 30
01 BBB 1 20 NULL
02 CCC 1 30 120
02 DDD 1 40 NULL
02 EEE 2 50 NULL
03 FFF 2 60 130
03 GGG 2 70 NULL*/
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([ID] varchar(2),[SPMC] varchar(3),[SL] int,[JE] int)
insert [tb]
select '01','AAA',1,10 union all
select '01','BBB',1,20 union all
select '02','CCC',1,30 union all
select '02','DDD',1,40 union all
select '02','EEE',2,50 union all
select '03','FFF',2,60 union all
select '03','GGG',2,70
---查询---
select
a.*,
isnull(b.zje,0) as zje
from
tb a
left join
(select id,min(spmc) as spmc,sum(je) as zje from tb group by id) b
on
a.id=b.id and a.spmc=b.spmc
---结果---
ID SPMC SL JE zje
---- ---- ----------- ----------- -----------
01 AAA 1 10 30
01 BBB 1 20 0
02 CCC 1 30 120
02 DDD 1 40 0
02 EEE 2 50 0
03 FFF 2 60 130
03 GGG 2 70 0(所影响的行数为 7 行)