Sqlserver 2008R2 先排序后分组

1.创建测试表

CREATe table CS(SPDM varchar(10),sl int,dj numeric(18,2),rq varchar(100)
)

2.插入数据

insert into CS
select '000',1,40,'2020-01-01'
union all
select '000',3,20,'2020-01-11'
union all
select '000',2,50,'2020-01-11'
union all
select '000',3,10,'2020-01-21'
union all
select '000',1,10,'2020-01-22'
union all
select '000',1,40,'2020-01-24'
union all
select '000',1,50,'2020-01-24'
union all
select '000',1,20,'2020-01-25'

3.先排序后分组

select spdm,dj,sl from (
select a.SPDM,a.dj,b.sl,a.rq from(
select SPDM,dj,MAX(rq) as rq from CS  group by SPDM,dj
) a
left join 
(select SPDM,DJ,SUM(sl) as sl from CS group by SPDM,dj)b on b.dj=a.dj and b.SPDM=a.SPDM 
) a order by rq desc```