日期:2014-05-17 浏览次数:20707 次
;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&