假设有一张表Test:
有两个字段:A和B
A B
1 1
1 2
1 3
. .
. .
2 1
2 2
2 3
. .
. .
3 1
3 2
. .
按字段A分组,我要选择1的前10条,2的前10条,3的前10条。。
事先不知道A有多少种情况。
请问大家这Sql语句怎么写?谢谢
有两个字段:A和B
A B
1 1
1 2
1 3
. .
. .
2 1
2 2
2 3
. .
. .
3 1
3 2
. .
按字段A分组,我要选择1的前10条,2的前10条,3的前10条。。
事先不知道A有多少种情况。
请问大家这Sql语句怎么写?谢谢
--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
--结果
/*ID GID Author Title Date
---- ----------- ----------------------------- --------------------------------------- -----------------------
003 1 格罗夫Groff.J.R. SQL完全手册 2009-07-01 00:00:00.000
004 1 KalenDelaney SQLServer2005技术内幕存储引擎 2008-08-01 00:00:00.000
005 2 Alex.Kriegel.Boris.M.Trukhnov SQL宝典 2007-10-05 00:00:00.000
007 2 胡百敬 SQLServer2005数据库开发详解 2008-06-15 00:00:00.000
009 3 赵松涛 SQLServer2005系统管理实录 2008-10-01 00:00:00.000
010 3 黄占涛 SQL技术手册 2006-01-01 00:00:00.000(6 行受影响)
*/
where b in(select top 10 from tb where a=t.a order by b)
分组取最大N条记录方法征集
SELECT *
FROM [Test] AS t
WHERE 10 >= (
SELECT COUNT(1)
FROM [Test] AS s
WHERE t.[A] = s.[A]
AND t.[B] <= s.[B]
)
--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)
给讲讲吧 第一个为什么WHERE GID=T.GID就行 其实不都是一个表么还=啥 不明白啊