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