SQL分组取前二条记录！比较麻烦！

SQL分组取前2条记录！比较麻烦！急急急！
SELECT id, convert(varchar, datepart(dd, riqi)) as nDay, biaoti,zhai,xmlx,xmid,ifpz,yg_bh,riqi FROM xingzheng_daiban_shixiang AS T WHERE (datepart(yy, riqi) = '2012' and datepart(mm, riqi) = '8' and yg_bh='1003' and ifpz='0') ORDER BY riqi

我要查员工编号为1003的员工，在2012年8月份每天需要办理的事情。

得到了下面的数据：

现在我需要按照日期顺序，显示该员工每天的前2条记录。

SELECT id, convert(varchar, datepart(dd, riqi)) as nDay, biaoti,zhai,xmlx,xmid,ifpz,yg_bh,riqi FROM xingzheng_daiban_shixiang AS T WHERE (datepart(yy, riqi) = '2012' and datepart(mm, riqi) = '8' and yg_bh='1003' and ifpz='0')
and id in (select top 2 id from xingzheng_daiban_shixiang as b where b.riqi=t.riqi order by id)
ORDER BY riqi

结果居然是下面这样的？根本查询不出来。

一直没有解决，还望各位高手指导！

------解决方案--------------------

SQL code

USE tempdb
GO
--为了简便，我略去了一些不重要的字段
CREATE TABLE test 
(
id INT IDENTITY(1,1),
yg_bh VARCHAR(4),
riqi DATETIME
)
--插入测试数据,每个日期3~4条
INSERT INTO test(yg_bh,riqi ) VALUES('1003','2012-08-24')
INSERT INTO test(yg_bh,riqi ) VALUES('1003','2012-08-24')
INSERT INTO test(yg_bh,riqi ) VALUES('1003','2012-08-24')
INSERT INTO test(yg_bh,riqi ) VALUES('1003','2012-08-25')
INSERT INTO test(yg_bh,riqi ) VALUES('1003','2012-08-25')
INSERT INTO test(yg_bh,riqi ) VALUES('1003','2012-08-25')
INSERT INTO test(yg_bh,riqi ) VALUES('1003','2012-08-26')
INSERT INTO test(yg_bh,riqi ) VALUES('1003','2012-08-26')
INSERT INTO test(yg_bh,riqi ) VALUES('1003','2012-08-26')
INSERT INTO test(yg_bh,riqi ) VALUES('1003','2012-08-26')


--取出某组的前N条数据
DECLARE @n INT=2


SELECT * FROM test a 
WHERE EXISTS (SELECT 1 FROM 
(SELECT id,NTILE(@n) OVER(PARTITION BY CONVERT(DATE,riqi ) ORDER BY id) groups,yg_bh,riqi 
FROM test) b WHERE a.id=b.id AND b.groups=1)

------解决方案--------------------

SQL code


SELECT test.*
FROM test 
    INNER JOIN (
        SELECT id,ROW_NUMBER() OVER (PARTITION BY riqi ORDER BY id) AS XuHao
        FROM test) AS t
        ON test.id = t.id AND t.XuHao <= 2

------解决方案--------------------
或者用关联子查询也可以。

SQL code


SELECT test.*
FROM test
WHERE test.id IN (
    SELECT TOP(2) t.id
    FROM test AS t
    WHERE test.riqi = t.riqi
    )
/*----------- ----- -----------------------
1           1003  2012-08-24 00:00:00.000
2           1003  2012-08-24 00:00:00.000
7           1003  2012-08-25 00:00:00.000
8           1003  2012-08-25 00:00:00.000
10          1003  2012-08-26 00:00:00.000
11          1003  2012-08-26 00:00:00.000

(6 行受影响)
*/

------解决方案--------------------
USE tempdb
GO
--为了简便，我略去了一些不重要的字段
CREATE TABLE test  
(
id INT IDENTITY(1,1),
yg_bh VARCHAR(4),
riqi DATETIME
)
--插入测试数据,每个日期3~4条
INSERT INTO test(yg_bh,riqi ) VALUES('1003','2012-08-24')
INSERT INTO test(yg_bh,riqi ) VALUES('1003','2012-08-24')
INSERT INTO test(yg_bh,riqi ) VALUES('1003','2012-08-24')
INSERT INTO test(yg_bh,riqi ) VALUES('1003','2012-08-25')
INSERT INTO test(yg_bh,riqi ) VALUES('1003','2012-08-25')
INSERT INTO test(yg_bh,riqi ) VALUES('1003','2012-08-25')
INSERT INTO test(yg_bh,riqi ) VALUES('1003','2012-08-26')
INSERT INTO test(yg_bh,riqi ) VALUES('1003','2012-08-26')
INSERT INTO test(yg_bh,riqi ) VALUES('1003','2012-08-26')
INSERT INTO test(yg_bh,riqi ) VALUES('1003','2012-08-26')


--取出某组的前N条数据
DECLARE @n INT=2


SELECT * FROM test a  
WHERE EXISTS (SELECT 1 FROM  
(SELECT id,NTILE(@n) OVER(PARTITION BY CONVERT(DATE,riqi ) ORDER BY id) groups,yg_bh,riqi  
FROM test) b WHERE a.id=b.id AND b.groups=1)



id yg_bh riqi
----------- ----- -----------------------

SQL分组取前二条记录！比较麻烦！

相关推荐