SQL语句取重复类型数据问题
select j1.* from (SELECT * FROM [Articles] where [TypeId] in(8,12,4,9,13)) j1
where not exists ( select j2.* from (SELECT * FROM [Articles] where [TypeId] in(8,12,4,9,13)) j2
where j1.ID>j2.ID and j1.[TypeId]=j2.[TypeId] )
order by AddTime desc,clicks desc
这个sql语句取出不重复的文章类型（8,12,4,9,13）数据
但是每个类型的数据只有一条，怎样改成每个以上每个类型取两条数据？？
ID Title TypeID
2017大众13
36现代4
1415万12
8武汉9
7八月8
改成
ID Title TypeID
2017大众13
2022宝马13
38路虎4
36现代4
31优惠12
1415万12
23金杯9
8武汉9
17涨价8
7八月8
怎样改成每个以上每个类型取两条数据？？
[解决办法]
----------------------------------- 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*/
[解决办法]
--获取每个分类下的N条记录 select *from( select *,rn=row_number() over (partition by 类别 order by 日期 desc) from @tb)twhere rn <= N--或者select distinct b.*from @tb across apply (select top N * from @tb where 类别=a.类别 order by newid())b