TB1 ID SF CZF DS ZSF CBYF SBID1 100 100 5 200 200812 001
2 100 100 5 200 200812 002
3 50 50 2 100 200811 001
4 50 50 2 100 200811 002
5 80 80 4 160 200812 003效果是:
ID SF CZF DS ZSF CBYF SBID1 150 150 7 300 200812 0015 80 80 4 160 200812 003
2 100 100 5 200 200812 002
3 50 50 2 100 200811 001
4 50 50 2 100 200811 002
5 80 80 4 160 200812 003效果是:
ID SF CZF DS ZSF CBYF SBID1 150 150 7 300 200812 0015 80 80 4 160 200812 003
2 100 100 5 200 200812 002 YH001
3 50 50 2 100 200811 001 YH001
4 50 50 2 100 200811 002 YH001
5 80 80 4 160 200812 003 YH002效果是DAH相同且 CBYF不同值 其它字段值相加啊:
ID SF CZF DS ZSF CBYF SBID DAH1 150 150 7 300 200812 001 YH0015 80 80 4 160 200812 003 YH002
go
create table [TB1]([ID] int,[SF] int,[CZF] int,[DS] int,[ZSF] int,[CBYF] int,[SBID] varchar(3),[DAH] varchar(5))
insert [TB1]
select 1,100,100,5,200,200812,'001','YH001' union all
select 2,100,100,5,200,200812,'002','YH001' union all
select 3,50,50,2,100,200811,'001','YH001' union all
select 4,50,50,2,100,200811,'002','YH001' union all
select 5,80,80,4,160,200812,'003','YH002'select min(a.id) AS ID,B.SF,B.CZF,B.DS,B.ZSF,MAX(A.CBYF) AS CBYF,MIN(B.SBID) AS SBID,B.DAH
from TB1 a,
(SELECT SUM(SF) AS SF,SUM(CZF) AS CZF,SUM(DS) AS DS,SUM(ZSF) AS ZSF,SBID,DAH FROM TB1 GROUP BY SBID,DAH) b
where a.sbid=b.sbid
GROUP BY B.SF,B.CZF,B.DS,B.ZSF,B.DAH
ORDER BY ID
--测试结果:
ID SF CZF DS ZSF CBYF SBID DAH
----------- ----------- ----------- ----------- ----------- ----------- ---- -----
1 150 150 7 300 200812 001 YH001
5 80 80 4 160 200812 003 YH002(所影响的行数为 2 行)
知其然而不知其所以然,所以结果感觉像拼凑起来的
ID int,
SF int,
CZF int,
DS int,
ZSF int,
CBYF varchar(300),
SBID varchar(300),
DAH varchar(300)
) insert into @TB1 values(1,100,100,5,200,'200812','001','YH001')
insert into @TB1 values(2,100,100,5,200,'200812','002','YH001')
insert into @TB1 values(3,50,50,2,100,'200811','001','YH001')
insert into @TB1 values(4,50,50,2,100,'200811','002','YH001')
insert into @TB1 values(5,80,80,4,160,'200812','003','YH002')
select * from @TB1
select min(a.ID) as ID,B.SF,B.CZF,B.DS,B.ZSF,B.CBYF as CBYF,min(B.SBID) as SBID from @TB1 a,
(select min(ID) as ID,sum(SF) as SF,sum(CZF) as CZF,sum(DS) as DS,sum(ZSF) as ZSF,max(CBYF) as CBYF,SBID from @TB1 group by SBID) B
where a.ID= B.ID
GROUP BY B.SF,B.CZF,B.DS,B.ZSF,B.CBYF
order by ID