CREATE
TABLE
#T (ID
VARCHAR
(
3
),GID
INT
,Author
VARCHAR
(
29
),Title
VARCHAR
(
39
),Date
DATETIME
)
INSERT INTO #T
SELECT ' 001 ' , 1 , ' 邹建 ' , ' 深入浅出SQLServer2005开发管理与应用实例 ' , ' 2008-05-10 ' UNION ALL
SELECT ' 002 ' , 1 , ' 胡百敬 ' , ' SQLServer2005性能调校 ' , ' 2008-03-22 ' UNION ALL
SELECT ' 003 ' , 1 , ' 格罗夫Groff.J.R. ' , ' SQL完全手册 ' , ' 2009-07-01 ' UNION ALL
SELECT ' 004 ' , 1 , ' KalenDelaney ' , ' SQLServer2005技术内幕存储引擎 ' , ' 2008-08-01 ' UNION ALL
SELECT ' 005 ' , 2 , ' Alex.Kriegel.Boris.M.Trukhnov ' , ' SQL宝典 ' , ' 2007-10-05 ' UNION ALL
SELECT ' 006 ' , 2 , ' 飞思科技产品研发中心 ' , ' SQLServer2000高级管理与开发 ' , ' 2007-09-10 ' UNION ALL
SELECT ' 007 ' , 2 , ' 胡百敬 ' , ' SQLServer2005数据库开发详解 ' , ' 2008-06-15 ' UNION ALL
SELECT ' 008 ' , 3 , ' 陈浩奎 ' , ' SQLServer2000存储过程与XML编程 ' , ' 2005-09-01 ' UNION ALL
SELECT ' 009 ' , 3 , ' 赵松涛 ' , ' SQLServer2005系统管理实录 ' , ' 2008-10-01 ' UNION ALL
SELECT ' 010 ' , 3 , ' 黄占涛 ' , ' SQL技术手册 ' , ' 2006-01-01 '
-- SQL查询如下:
-- 按GID分组,查每个分组中Date最新的前2条记录
-- 1.字段ID唯一时:
SELECT * FROM #T AS T WHERE ID IN ( SELECT TOP 2 ID FROM #T WHERE GID = T.GID ORDER BY Date DESC )
-- 2.如果ID不唯一时:
SELECT * FROM #T AS T WHERE 2 > ( SELECT COUNT ( * ) FROM #T WHERE GID = T.GID AND Date > T.Date)
-- SQL Server 2005 使用新方法
-- 3.使用ROW_NUMBER()进行排位分组
SELECT ID,GID,Author,Title,Date
FROM
(
SELECT rid = ROW_NUMBER() OVER (PARTITION BY GID ORDER BY Date DESC ), *
FROM #T
) AS T
WHERE rid <= 2
-- 4.使用APPLY
SELECT DISTINCT b. *
FROM #T AS a
CROSS APPLY
(
SELECT TOP ( 2 ) * FROM #T WHERE a.GID = GID ORDER BY Date DESC
) AS b
INSERT INTO #T
SELECT ' 001 ' , 1 , ' 邹建 ' , ' 深入浅出SQLServer2005开发管理与应用实例 ' , ' 2008-05-10 ' UNION ALL
SELECT ' 002 ' , 1 , ' 胡百敬 ' , ' SQLServer2005性能调校 ' , ' 2008-03-22 ' UNION ALL
SELECT ' 003 ' , 1 , ' 格罗夫Groff.J.R. ' , ' SQL完全手册 ' , ' 2009-07-01 ' UNION ALL
SELECT ' 004 ' , 1 , ' KalenDelaney ' , ' SQLServer2005技术内幕存储引擎 ' , ' 2008-08-01 ' UNION ALL
SELECT ' 005 ' , 2 , ' Alex.Kriegel.Boris.M.Trukhnov ' , ' SQL宝典 ' , ' 2007-10-05 ' UNION ALL
SELECT ' 006 ' , 2 , ' 飞思科技产品研发中心 ' , ' SQLServer2000高级管理与开发 ' , ' 2007-09-10 ' UNION ALL
SELECT ' 007 ' , 2 , ' 胡百敬 ' , ' SQLServer2005数据库开发详解 ' , ' 2008-06-15 ' UNION ALL
SELECT ' 008 ' , 3 , ' 陈浩奎 ' , ' SQLServer2000存储过程与XML编程 ' , ' 2005-09-01 ' UNION ALL
SELECT ' 009 ' , 3 , ' 赵松涛 ' , ' SQLServer2005系统管理实录 ' , ' 2008-10-01 ' UNION ALL
SELECT ' 010 ' , 3 , ' 黄占涛 ' , ' SQL技术手册 ' , ' 2006-01-01 '
-- SQL查询如下:
-- 按GID分组,查每个分组中Date最新的前2条记录
-- 1.字段ID唯一时:
SELECT * FROM #T AS T WHERE ID IN ( SELECT TOP 2 ID FROM #T WHERE GID = T.GID ORDER BY Date DESC )
-- 2.如果ID不唯一时:
SELECT * FROM #T AS T WHERE 2 > ( SELECT COUNT ( * ) FROM #T WHERE GID = T.GID AND Date > T.Date)
-- SQL Server 2005 使用新方法
-- 3.使用ROW_NUMBER()进行排位分组
SELECT ID,GID,Author,Title,Date
FROM
(
SELECT rid = ROW_NUMBER() OVER (PARTITION BY GID ORDER BY Date DESC ), *
FROM #T
) AS T
WHERE rid <= 2
-- 4.使用APPLY
SELECT DISTINCT b. *
FROM #T AS a
CROSS APPLY
(
SELECT TOP ( 2 ) * FROM #T WHERE a.GID = GID ORDER BY Date DESC
) AS b
分类:
SqlServer
本文转自快乐就好博客园博客,原文链接:http://www.cnblogs.com/happyday56/archive/2009/09/11/1564769.html,如需转载请自行联系原作者