一张新闻表有A,B,C三类新闻,我想一条sql语句提取三类新闻各10条(不要用union,存储过程)。
补充问一下：有没有办法定制每类的条数
------解决方案--------------------
- SQL code
select *from( select *,rn=row_number() over (partition by 类别 order by 日期 desc) from tb)twhere rn <= 10--按日期最新的来取前十
------解决方案--------------------
- SQL code
select distinct b.*from tb across apply (select top 3 * from tb where 类别=a.类别 order by newid())b
------解决方案--------------------
- SQL code
----------------------------------- Author: liangCK 小梁-- Title : 查每个分组前N条记录-- Date : 2008-11-13 17:19:23-----------------------------------> 生成测试数据: #TIF OBJECT_ID('tempdb.dbo.#T') IS NOT NULL DROP TABLE #TCREATE TABLE #T (ID VARCHAR(3),GID INT,Author VARCHAR(29),Title VARCHAR(39),Date DATETIME)INSERT INTO #TSELECT '001',1,'邹建','深入浅出SQLServer2005开发管理与应用实例','2008-05-10' UNION ALLSELECT '002',1,'胡百敬','SQLServer2005性能调校','2008-03-22' UNION ALLSELECT '003',1,'格罗夫Groff.J.R.','SQL完全手册','2009-07-01' UNION ALLSELECT '004',1,'KalenDelaney','SQLServer2005技术内幕存储引擎','2008-08-01' UNION ALLSELECT '005',2,'Alex.Kriegel.Boris.M.Trukhnov','SQL宝典','2007-10-05' UNION ALLSELECT '006',2,'飞思科技产品研发中心','SQLServer2000高级管理与开发','2007-09-10' UNION ALLSELECT '007',2,'胡百敬','SQLServer2005数据库开发详解','2008-06-15' UNION ALLSELECT '008',3,'陈浩奎','SQLServer2000存储过程与XML编程','2005-09-01' UNION ALLSELECT '009',3,'赵松涛','SQLServer2005系统管理实录','2008-10-01' UNION ALLSELECT '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,DateFROM( SELECT rid=ROW_NUMBER() OVER(PARTITION BY GID ORDER BY Date DESC),* FROM #T) AS TWHERE rid<=2--4.使用APPLYSELECT DISTINCT b.*FROM #T AS aCROSS 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.000004 1 KalenDelaney SQLServer2005技术内幕存储引擎 2008-08-01 00:00:00.000005 2 Alex.Kriegel.Boris.M.Trukhnov SQL宝典 2007-10-05 00:00:00.000007 2 胡百敬 SQLServer2005数据库开发详解 2008-06-15 00:00:00.000009 3 赵松涛 SQLServer2005系统管理实录 2008-10-01 00:00:00.000010 3 黄占涛 SQL技术手册 2006-01-01 00:00:00.000(6 行受影响)*/--得到每组前几条数据--假設每組Col1中， Col3不會重復--建立測試環境Create Table TEST(Col1 Varchar(10), Col2 Varchar(10), Col3 Int)--插入數據Insert TEST Select 'BD1V','Label', 4Union All Select 'BD1V', 'BATT', 2Union All Select 'BD1V', 'ODD', 3Union All Select 'BD1V', 'HDD', 5Union All Select 'BD1V', 'LCD', 1Union All Select 'BD1W','HDD', 3Union All Select 'BD1W','RAM', 8Union All Select 'BD1W','TP CABLE', 5Union All Select 'BD1W','LCD', 6Union All Select 'BD1W','Label', 2Union All Select 'BL3', 'LCD CABLE', 7Union All Select 'BL3', 'LABEL', 6Union All Select 'BL3', 'LCD', 5Union All Select 'BL3', 'RAM', 1Union All Select 'BL3D', 'Label', 4GO--測試--方法一：Select Col1, Col2, Col3 From TEST AWhere (Select Count(*) From TEST Where Col1 = A.Col1 And Col3 > A.Col3) < 3Order By Col1, Col3 Desc--方法二：Select Col1, Col2, Col3 From TEST AWhere Exists (Select Count(*) From TEST Where Col1 = A.Col1 And Col3 > A.Col3 Having Count(*) < 3)Order By Col1, Col3 Desc--方法三：Select Col1, Col2, Col3 From TEST AWhere Col3 In (Select TOP 3 Col3 From TEST Where Col1 = A.Col1 Order By Col3 Desc)Order By Col1, Col3 DescGO--刪除測試環境Drop Table TEST--結果/*Col1 Col2 Col3BD1V HDD 5BD1V Label 4BD1V ODD 3BD1W RAM 8BD1W LCD 6BD1W TP CABLE 5BL3 LCD CABLE 7BL3 LABEL 6BL3 LCD 5BL3D Label 4*/