22,209
社区成员
发帖
与我相关
我的任务
分享
create table test(bj varchar(10) , crdate datetime)
go
insert into test values
('1班','2016-2-19 08:48:30'),
('5班','2016-2-19 07:48:30'),
('4班','2016-2-19 06:48:30'),
('3班','2016-2-19 05:48:30'),
('2班','2016-2-19 04:48:30'),
('1班','2016-2-19 03:48:30'),
('4班','2016-2-19 02:48:30'),
('4班','2016-2-19 01:48:30'),
('3班','2016-2-18 21:48:30'),
('1班','2016-2-18 17:48:30')
go
with m as (
select row_number() over(partition by bj order by crdate desc) rn,
dense_rank() over(order by crdate desc) rn2,
*
from test
)
select test.* from m inner join test on m.bj = test.bj
where m.rn = 1
order by m.rn2 , m.crdate
go
drop table test
go
(10 行受影响)
bj crdate
---------- -----------------------
1班 2016-02-19 08:48:30.000
1班 2016-02-19 03:48:30.000
1班 2016-02-18 17:48:30.000
5班 2016-02-19 07:48:30.000
4班 2016-02-19 06:48:30.000
4班 2016-02-19 02:48:30.000
4班 2016-02-19 01:48:30.000
3班 2016-02-19 05:48:30.000
3班 2016-02-18 21:48:30.000
2班 2016-02-19 04:48:30.000
(10 行受影响)
order by 提交时间,班级 -- 这里写两个字段,用逗号分开就可以了。
order by 提交时间 desc ,班级 -- desc 可以把最近的放在前面