SQL分组取前二条记录！比较麻烦！
日期：2014-05-17 浏览次数：20744 次
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
结果居然是下面这样的？根本查询不出来。
一直没有解决，还望各位高手指导！
------解决方案--------------------
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
----------- ----- -----------------------
免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。
