1. 爱易网页
  2. MSSQL教程
  3. SQL分组取前二条记录！比较麻烦！

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

日期：2014-05-17　 浏览次数：20628 次

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
----------- ----- -----------------------

相关资料更多>

  1. 请问：查询表中每个类别的第一条记录的sql。内详
  2. 急一个数据查询的有关问题，感觉有点难
  3. 队列转化的,别复制
  4. sqlserver2008数据实时同步技术实测小结，高分求解
  5. 问一个SQL语句的写法解决办法
  6. 深圳天源迪科计算机有限公司合肥分公司如何样
  7. sql server 存储过程将一系列查询结果输出到txt文档中保存？解决方法
  8. 零基础，想学数据库做BI,求时间路线图！解决方案
  9. 这条话语为什么不行