取分类的第N条数据

 

DECLARE @t TABLE (name varchar(10), info int) 
INSERT @t
SELECT '张三', 1
UNION ALL
SELECT '张三', 2
UNION ALL
SELECT '张三', 3
UNION ALL
SELECT '李四', 1
UNION ALL
SELECT '李四', 2
UNION ALL
SELECT '王五', 1

--1每个人的第一条记录

SELECT * FROM @t AS t WHERE NOT EXISTS 
   (SELECT * FROM @t WHERE name = t .name AND info < t .info)

--或

SELECT * FROM @t AS t WHERE 
(SELECT COUNT(*) FROM @t WHERE name = t .name AND info < t .info) = 0

--2\每个人的第二条记录  --第N条记录。。。

SELECT * FROM @t AS t WHERE 
(SELECT COUNT(*) FROM @t WHERE name = t .name AND info < t .info) = 1



---取每个新闻分类的前三条 
SELECT *
FROM NEWS o
WHERE ((SELECT COUNT(*)
          FROM news
          WHERE sort_id = o.sort_id AND n_id < o.n_id) < 3)
ORDER BY SORT_ID