create table tb(cpspcode varchar(10),cpscode varchar(10),ipsquantity int) insert into tb values('A1','E', 20) insert into tb values('A1','F', 36) insert into tb values('A1','D', 4) insert into tb values('A2','E', 50) insert into tb values('A2','F', 72) insert into tb values('A2','D', 7)select max(cpspcode) cpspcode, cpscode , sum(ipsquantity) ipsquantity from tb group by cpscode drop table tb/* cpspcode cpscode ipsquantity ---------- ---------- ----------- A2 D 11 A2 E 70 A2 F 108(所影响的行数为 3 行) */
create table #(cpspcode nvarchar(10), cpscode nvarchar(10), ipsquantity decimal(9,6)) insert # select 'A1','E',20.000000 union all select 'A1','F',36.000000 union all select 'A1','D',4.000000 union all select 'A2','E',50.000000 union all select 'A2','F',72.000000 union all select 'A2','D',7.000000select cpspcode = max(cpspcode),cpscode,ipsquantity = cast(sum(ipsquantity) as int) from # group by cpscode A2 D 11 A2 E 70 A2 F 108
----创建测试数据 declare @t table(cpspcode varchar(10),cpscode varchar(10),ipsquantity int) insert @t select 'A1', 'E', 20.000000 union all select 'A1', 'F', 36.000000 union all select 'A1', 'D', 4.000000 union all select 'A2', 'E', 50.000000 union all select 'A2', 'F', 72.000000 union all select 'A2', 'D', 7.000000----查询 SELECT cpspcode = (select top 1 cpspcode from @t where cpscode = a.cpscode ORDER BY ipsquantity DESC), a.cpscode,sum(a.ipsquantity) as ipsquantity FROM @t as a GROUP BY a.cpscode/*结果 cpspcode cpscode ipsquantity ---------- ---------- ----------- A2 D 11 A2 E 70 A2 F 108 */
--写的比较复杂,汗~~~ declare @table table(cpspcode varchar(10),cpscode varchar(10),ipsquantity int) insert into @table values('A1','E',20) insert into @table values('A1','F',36) insert into @table values('A1','D',4) insert into @table values('A2','E',50) insert into @table values('A2','F',72) insert into @table values('A2','D',7)select c.cpspcode,c.cpscode,sum(c.ipsquantity)as ipsquantity from (select (select cpspcode from @table a where a.cpscode+cast(a.ipsquantity as varchar(10))in (select cpscode+cast(max(ipsquantity)as varchar(10))as a from @table where cpscode=a.cpscode group by cpscode)and a.cpscode=b.cpscode) as cpspcode,b.cpscode,b.ipsquantity from @table b)c group by c.cpspcode,c.cpscode order by c.cpspcode,c.cpscode /* cpspcode cpscode ipsquantity ---------- ---------- ----------- A2 D 11 A2 E 70 A2 F 108(3 row(s) affected) */
insert into tb values('A1','E', 20)
insert into tb values('A1','F', 36)
insert into tb values('A1','D', 4)
insert into tb values('A2','E', 50)
insert into tb values('A2','F', 72)
insert into tb values('A2','D', 7)select max(cpspcode) cpspcode, cpscode , sum(ipsquantity) ipsquantity from tb group by cpscode
drop table tb/*
cpspcode cpscode ipsquantity
---------- ---------- -----------
A2 D 11
A2 E 70
A2 F 108(所影响的行数为 3 行)
*/
insert #
select 'A1','E',20.000000 union all
select 'A1','F',36.000000 union all
select 'A1','D',4.000000 union all
select 'A2','E',50.000000 union all
select 'A2','F',72.000000 union all
select 'A2','D',7.000000select cpspcode = max(cpspcode),cpscode,ipsquantity = cast(sum(ipsquantity) as int)
from #
group by cpscode A2 D 11
A2 E 70
A2 F 108
declare @t table(cpspcode varchar(10),cpscode varchar(10),ipsquantity int)
insert @t
select 'A1', 'E', 20.000000 union all
select 'A1', 'F', 36.000000 union all
select 'A1', 'D', 4.000000 union all
select 'A2', 'E', 50.000000 union all
select 'A2', 'F', 72.000000 union all
select 'A2', 'D', 7.000000----查询
SELECT cpspcode = (select top 1 cpspcode from @t where cpscode = a.cpscode ORDER BY ipsquantity DESC),
a.cpscode,sum(a.ipsquantity) as ipsquantity
FROM @t as a GROUP BY a.cpscode/*结果
cpspcode cpscode ipsquantity
---------- ---------- -----------
A2 D 11
A2 E 70
A2 F 108
*/
declare @table table(cpspcode varchar(10),cpscode varchar(10),ipsquantity int)
insert into @table values('A1','E',20)
insert into @table values('A1','F',36)
insert into @table values('A1','D',4)
insert into @table values('A2','E',50)
insert into @table values('A2','F',72)
insert into @table values('A2','D',7)select c.cpspcode,c.cpscode,sum(c.ipsquantity)as ipsquantity from
(select
(select cpspcode from @table a where a.cpscode+cast(a.ipsquantity as varchar(10))in
(select cpscode+cast(max(ipsquantity)as varchar(10))as a from @table
where cpscode=a.cpscode group by cpscode)and a.cpscode=b.cpscode)
as cpspcode,b.cpscode,b.ipsquantity from @table b)c
group by c.cpspcode,c.cpscode
order by c.cpspcode,c.cpscode
/*
cpspcode cpscode ipsquantity
---------- ---------- -----------
A2 D 11
A2 E 70
A2 F 108(3 row(s) affected)
*/