在SQL表中提取每天按code字段分组的前2条记录出来
临时表#t1如下：
id code listdate
1 001 2012-01-02 09:10:10
2 002 2012-01-03 15:19:01
3 003 2012-01-01 10:10:01
4 004 2012-01-02 08:11:09
5 005 2012-01-02 09:20:01
6 006 2012-01-02 12:12:09
7 007 2012-01-03 13:08:02
如果我想每天取top 2 的数据出来，要求结果如下：
id code listdate
3 003 2012-01-01 10:10:01
4 004 2012-01-02 08:11:09
1 001 2012-01-02 09:10:10
7 007 2012-01-03 13:08:02
2 002 2012-01-03 15:19:01
我要求能否不要用循环和游标，如何写sql语句来写？
[最优解释]
;WITH c1(id, code, listdate)
AS
(
SELECT 1, '001', '2012-01-02 09:10:10' UNION ALL
SELECT 2, '002', '2012-01-03 15:19:01' UNION ALL
SELECT 3, '003', '2012-01-01 10:10:01' UNION ALL
SELECT 4, '004', '2012-01-02 08:11:09' UNION ALL
SELECT 5, '005', '2012-01-02 09:20:01' UNION ALL
SELECT 6, '006', '2012-01-02 12:12:09' UNION ALL
SELECT 7, '007', '2012-01-03 13:08:02'
)
,c2 as
(
SELECT
listdate,
ROW_NUMBER() OVER(PARTITION BY CAST(listdate AS DATE) ORDER BY listdate) rowid,
id
FROM c1 d
)
SELECT *
FROM c2
WHERE rowid < 3
listdate rowid id
------------------- -------------------- -----------
2012-01-01 10:10:01 1 3
2012-01-02 08:11:09 1 4
2012-01-02 09:10:10 2 1
2012-01-03 13:08:02 1 7
2012-01-03 15:19:01 2 2
(5 行受影响)
--方法一05新增：
select id,code,listdate
from (select rid=row_number()over (partition by convert(varchar(10),listdate) order by listdate desc),* from #t1)as t
where rid<=2
--方法二：使用cross apply
select distinct b.*
from #t1 as a
cross apply
(select top(2) * from #t1 where convert(varchar(10),a.listdate)=convert(varchar(10),listdate) order by listdate desc) as b
WITH cte ( pid, code, listdate )
AS ( SELECT ROW_NUMBER() OVER ( PARTITION BY CONVERT(DATE, listdate) ORDER BY id ) pid ,
code ,
listdate
FROM tb
)
SELECT *
FROM TB a
WHERE EXISTS ( SELECT 1
FROM cte b
WHERE a.pid = b.pid
AND a.listdate = b.listdate
AND b.pid <= 2 )
/*
结果
id code listdate
----------- -------------------- -----------------------
2 002 2012-01-03 15:19:01.000
3 003 2012-01-01 10:10:01.000
5 005 2012-01-02 09:20:01.000
6 006 2012-01-02 12:12:09.000
7 007 2012-01-03 13:08:02.000
(5 行受影响)
*/
