使用distinct的问题有表ti如下:dm mc sl dj zj
a101 aaaa 2 2.00 4.00
a101 aaaa 3 2.50 7.50
a101 aaaa 3 2.00 6.00
a101 aaaa 4 2.50 10.00
a102 bbbb 5 6.00 30.00
a102 bbbb 4 5.00 20.00
a102 bbbb 3 6.00 18.00
a102 bbbb 2 5.00 10.00要得到
dm mc sl dj zj
a101 aaaa 5 2.00 10.00
a101 aaaa 7 2.50 17.50
a102 bbbb 8 6.00 48.00
a102 bbbb 6 5.00 30.00就是在dm和dj相同的情况下,对sl和zj求和我试用distinct好像不行,只能对一个条件起作用select distinct dm,mc,sum(sl),dj,sum(zj) from t1
a101 aaaa 2 2.00 4.00
a101 aaaa 3 2.50 7.50
a101 aaaa 3 2.00 6.00
a101 aaaa 4 2.50 10.00
a102 bbbb 5 6.00 30.00
a102 bbbb 4 5.00 20.00
a102 bbbb 3 6.00 18.00
a102 bbbb 2 5.00 10.00要得到
dm mc sl dj zj
a101 aaaa 5 2.00 10.00
a101 aaaa 7 2.50 17.50
a102 bbbb 8 6.00 48.00
a102 bbbb 6 5.00 30.00就是在dm和dj相同的情况下,对sl和zj求和我试用distinct好像不行,只能对一个条件起作用select distinct dm,mc,sum(sl),dj,sum(zj) from t1
declare @ti table(dm varchar(4),mc varchar(4),sl int,dj decimal(3,2),zj decimal(4,2))
insert @ti
select 'a101','aaaa',2,2.00,4.00 union all
select 'a101','aaaa',3,2.50,7.50 union all
select 'a101','aaaa',3,2.00,6.00 union all
select 'a101','aaaa',4,2.50,10.00 union all
select 'a102','bbbb',5,6.00,30.00 union all
select 'a102','bbbb',4,5.00,20.00 union all
select 'a102','bbbb',3,6.00,18.00 union all
select 'a102','bbbb',2,5.00,10.00--如果dm同,mc可以不同,这个字段不能要,因为是对dm和dj分组:
select dm,mc,sl=sum(sl),dj,zj=sum(zj) from @ti group by dm,mc,dj/*
dm mc sl dj zj
---- ---- ----------- ----- ----------------------------------------
a101 aaaa 5 2.00 10.00
a101 aaaa 7 2.50 17.50
a102 bbbb 6 5.00 30.00
a102 bbbb 8 6.00 48.00
*/