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

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 tempdbGO--为了简便，我略去了一些不重要的字段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=2SELECT * 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 codeSELECT 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 codeSELECT test.*FROM testWHERE 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.0002           1003  2012-08-24 00:00:00.0007           1003  2012-08-25 00:00:00.0008           1003  2012-08-25 00:00:00.00010          1003  2012-08-26 00:00:00.00011          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
----------- ----- -----------------------