一个 分组排序取前N条记录的问题，困扰一天了，晕
数据如下：
idcode tdatecname tcount
77037m13012012-1-18上海大陆46
77038m13012012-1-18中粮期货8
77039m13012012-1-18鲁证期货8
77040m13012012-1-18创元期货7
77041m13012012-1-18万达期货5
84182m13022012-1-19上海大陆51
84183m13022012-1-19中粮期货18
84184m13022012-1-19鲁证期货12
84185m13022012-1-19银河期货12
84186m13022012-1-19创元期货7
84187m13022012-1-19万达期货4
91272m13012012-1-20上海大陆70
91273m13012012-1-20一德期货42
91274m13012012-1-20万达期货40
91275m13012012-1-20重庆新涪35
91276m13012012-1-20招商期货23
91277m13012012-1-20中粮期货18
91278m13012012-1-20银河期货16
91279m13012012-1-20广州期货13
91280m13012012-1-20鲁证期货11
91281m13012012-1-20创元期货7
91282m13012012-1-20神华期货5
91283m13012012-1-20长江期货4
91284m13012012-1-20中州期货4
91285m13012012-1-20中证期货4
1.要求列出 每种Code,每天的数量在前五名的 明细(包括id,code,tdate,cname,tcount)
2.求出 每种Code,每天的数量在前五名的,数量之和.
请SQL高手们帮忙
[解决办法]
SELECT * FROM TB T
WHERE tcount IN(SELECT TOP 5 tcount FROM TB WHERE CODE=T.CODE AND DATE=T.DATE ORDER BY tcount DESC)
求和就子查询
[解决办法]
select *,row_number() over(order by cname,tcount) as rownumberfrom tbwhere rownumber <=5
[解决办法]
--> 测试数据：[test]if object_id('[test]') is not null drop table [test]create table [test]([id] int,[code] varchar(5),[tdate] datetime,[cname] varchar(8),[tcount] int)goinsert [test]select 77037,'m1301','2012-1-18','上海大陆',46 union allselect 77038,'m1301','2012-1-18','中粮期货',8 union allselect 77039,'m1301','2012-1-18','鲁证期货',8 union allselect 77040,'m1301','2012-1-18','创元期货',7 union allselect 77041,'m1301','2012-1-18','万达期货',5 union allselect 84182,'m1302','2012-1-19','上海大陆',51 union allselect 84183,'m1302','2012-1-19','中粮期货',18 union allselect 84184,'m1302','2012-1-19','鲁证期货',12 union allselect 84185,'m1302','2012-1-19','银河期货',12 union allselect 84186,'m1302','2012-1-19','创元期货',7 union allselect 84187,'m1302','2012-1-19','万达期货',4 union allselect 91272,'m1301','2012-1-20','上海大陆',70 union allselect 91273,'m1301','2012-1-20','一德期货',42 union allselect 91274,'m1301','2012-1-20','万达期货',40 union allselect 91275,'m1301','2012-1-20','重庆新涪',35 union allselect 91276,'m1301','2012-1-20','招商期货',23 union allselect 91277,'m1301','2012-1-20','中粮期货',18 union allselect 91278,'m1301','2012-1-20','银河期货',16 union allselect 91279,'m1301','2012-1-20','广州期货',13 union allselect 91280,'m1301','2012-1-20','鲁证期货',11 union allselect 91281,'m1301','2012-1-20','创元期货',7 union allselect 91282,'m1301','2012-1-20','神华期货',5 union allselect 91283,'m1301','2012-1-20','长江期货',4 union allselect 91284,'m1301','2012-1-20','中州期货',4 union allselect 91285,'m1301','2012-1-20','中证期货',4go;with tas(select px=DENSE_RANK()over(partition by [code] order by tcount desc), *from test)--问题一:select id, code, tdate, cname, tcountfrom twhere px<=5go/*id code tdate cname tcount-----------------------------91272 m1301 2012-01-20 00:00:00.000 上海大陆 7077037 m1301 2012-01-18 00:00:00.000 上海大陆 4691273 m1301 2012-01-20 00:00:00.000 一德期货 4291274 m1301 2012-01-20 00:00:00.000 万达期货 4091275 m1301 2012-01-20 00:00:00.000 重庆新涪 3584182 m1302 2012-01-19 00:00:00.000 上海大陆 5184183 m1302 2012-01-19 00:00:00.000 中粮期货 1884184 m1302 2012-01-19 00:00:00.000 鲁证期货 1284185 m1302 2012-01-19 00:00:00.000 银河期货 1284186 m1302 2012-01-19 00:00:00.000 创元期货 784187 m1302 2012-01-19 00:00:00.000 万达期货 4*/--问题二;with tas(select px=DENSE_RANK()over(partition by [code] order by tcount desc), *from test)select id, code, tdate, cname, tcount, SUM(tcount)over(partition by code) as Totaltcountfrom twhere px<=5/*id code tdate cname tcount Totaltcount-----------------------------------------91272 m1301 2012-01-20 00:00:00.000 上海大陆 70 23377037 m1301 2012-01-18 00:00:00.000 上海大陆 46 23391273 m1301 2012-01-20 00:00:00.000 一德期货 42 23391274 m1301 2012-01-20 00:00:00.000 万达期货 40 23391275 m1301 2012-01-20 00:00:00.000 重庆新涪 35 23384182 m1302 2012-01-19 00:00:00.000 上海大陆 51 10484183 m1302 2012-01-19 00:00:00.000 中粮期货 18 10484184 m1302 2012-01-19 00:00:00.000 鲁证期货 12 10484185 m1302 2012-01-19 00:00:00.000 银河期货 12 10484186 m1302 2012-01-19 00:00:00.000 创元期货 7 10484187 m1302 2012-01-19 00:00:00.000 万达期货 4 104*/
[解决办法]
----------------------------------- 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 行受影响)*/
[解决办法]
select *,row_number() over(partition by code,tdate,cname,tcount order by tcount desc) as rownumberfrom tbwhere rownumber <=5
[解决办法]
select distinct a.*from test a outer apply (select top 5 tcount from test where code = a.code and tdate = a.tdate order by tcount desc) bwhere a.tcount = b.tcount
[解决办法]