一个 分组排序取前N条记录的问题，困扰一天了，晕

Question

数据如下：　id code 　　tdate cname 　　　tcount
77037 m1301 2012-1-18 上海大陆 46
77038 m1301 2012-1-18 中粮期货 8
77039 m1301 2012-1-18 鲁证期货 8
77040 m1301 2012-1-18 创元期货 7
77041 m1301 2012-1-18 万达期货 5
84182 m1302 2012-1-19 上海大陆 51
84183 m1302 2012-1-19 中粮期货 18
84184 m1302 2012-1-19 鲁证期货 12
84185 m1302 2012-1-19 银河期货 12
84186 m1302 2012-1-19 创元期货 7
84187 m1302 2012-1-19 万达期货 4
91272 m1301 2012-1-20 上海大陆 70
91273 m1301 2012-1-20 一德期货 42
91274 m1301 2012-1-20 万达期货 40
91275 m1301 2012-1-20 重庆新涪 35
91276 m1301 2012-1-20 招商期货 23
91277 m1301 2012-1-20 中粮期货 18
91278 m1301 2012-1-20 银河期货 16
91279 m1301 2012-1-20 广州期货 13
91280 m1301 2012-1-20 鲁证期货 11
91281 m1301 2012-1-20 创元期货 7
91282 m1301 2012-1-20 神华期货 5
91283 m1301 2012-1-20 长江期货 4
91284 m1301 2012-1-20 中州期货 4
91285 m1301 2012-1-20 中证期货 4
1.要求列出　每种Code,每天的数量在前五名的 明细(包括id,code,tdate,cname,tcount)
2.求出 每种Code,每天的数量在前五名的,数量之和.请SQL高手们帮忙

Answer 1

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)求和就子查询

Answer 2

select *,row_number() over(order by cname,tcount) as rownumber
from tb
where rownumber <=5

Answer 3

--> 测试数据：[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
)
go
insert [test]
select 77037,'m1301','2012-1-18','上海大陆',46 union all
select 77038,'m1301','2012-1-18','中粮期货',8 union all
select 77039,'m1301','2012-1-18','鲁证期货',8 union all
select 77040,'m1301','2012-1-18','创元期货',7 union all
select 77041,'m1301','2012-1-18','万达期货',5 union all
select 84182,'m1302','2012-1-19','上海大陆',51 union all
select 84183,'m1302','2012-1-19','中粮期货',18 union all
select 84184,'m1302','2012-1-19','鲁证期货',12 union all
select 84185,'m1302','2012-1-19','银河期货',12 union all
select 84186,'m1302','2012-1-19','创元期货',7 union all
select 84187,'m1302','2012-1-19','万达期货',4 union all
select 91272,'m1301','2012-1-20','上海大陆',70 union all
select 91273,'m1301','2012-1-20','一德期货',42 union all
select 91274,'m1301','2012-1-20','万达期货',40 union all
select 91275,'m1301','2012-1-20','重庆新涪',35 union all
select 91276,'m1301','2012-1-20','招商期货',23 union all
select 91277,'m1301','2012-1-20','中粮期货',18 union all
select 91278,'m1301','2012-1-20','银河期货',16 union all
select 91279,'m1301','2012-1-20','广州期货',13 union all
select 91280,'m1301','2012-1-20','鲁证期货',11 union all
select 91281,'m1301','2012-1-20','创元期货',7 union all
select 91282,'m1301','2012-1-20','神华期货',5 union all
select 91283,'m1301','2012-1-20','长江期货',4 union all
select 91284,'m1301','2012-1-20','中州期货',4 union all
select 91285,'m1301','2012-1-20','中证期货',4
go;with t
as(
select 
px=DENSE_RANK()over(partition by [code] order by tcount desc),
*
from
test
)
--问题一:
select 
id,
code,
tdate,
cname,
tcount
from
t
where 
px<=5
go
/*
id code tdate cname tcount
-----------------------------------------------------------------
91272 m1301 2012-01-20 00:00:00.000 上海大陆 70
77037 m1301 2012-01-18 00:00:00.000 上海大陆 46
91273 m1301 2012-01-20 00:00:00.000 一德期货 42
91274 m1301 2012-01-20 00:00:00.000 万达期货 40
91275 m1301 2012-01-20 00:00:00.000 重庆新涪 35
84182 m1302 2012-01-19 00:00:00.000 上海大陆 51
84183 m1302 2012-01-19 00:00:00.000 中粮期货 18
84184 m1302 2012-01-19 00:00:00.000 鲁证期货 12
84185 m1302 2012-01-19 00:00:00.000 银河期货 12
84186 m1302 2012-01-19 00:00:00.000 创元期货 7
84187 m1302 2012-01-19 00:00:00.000 万达期货 4
*/
--问题二
;with t
as(
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 Totaltcount
from 
t
where
px<=5
/*
id code tdate cname tcount Totaltcount
-----------------------------------------------------------------------------
91272 m1301 2012-01-20 00:00:00.000 上海大陆 70 233
77037 m1301 2012-01-18 00:00:00.000 上海大陆 46 233
91273 m1301 2012-01-20 00:00:00.000 一德期货 42 233
91274 m1301 2012-01-20 00:00:00.000 万达期货 40 233
91275 m1301 2012-01-20 00:00:00.000 重庆新涪 35 233
84182 m1302 2012-01-19 00:00:00.000 上海大陆 51 104
84183 m1302 2012-01-19 00:00:00.000 中粮期货 18 104
84184 m1302 2012-01-19 00:00:00.000 鲁证期货 12 104
84185 m1302 2012-01-19 00:00:00.000 银河期货 12 104
84186 m1302 2012-01-19 00:00:00.000 创元期货 7 104
84187 m1302 2012-01-19 00:00:00.000 万达期货 4 104
*/

Answer 4

---------------------------------
--  Author: liangCK 小梁
--  Title : 查每个分组前N条记录
--  Date  : 2008-11-13 17:19:23
-----------------------------------> 生成测试数据: #T
IF OBJECT_ID('tempdb.dbo.#T') IS NOT NULL DROP TABLE #T
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
--结果
/*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 行受影响)
*/

Answer 5

select *,row_number() over(partition by code,tdate,cname,tcount order by tcount desc) as rownumber
from tb
where rownumber <=5

Answer 6

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) b
where a.tcount = b.tcount

Answer 7

用  where id in 是不是更好呢?

Answer 8

允许名词相同啊 ID应该不行吧 
另外应该是SELECT * FROM TB T
WHERE tcount IN(SELECT TOP 5 tcount with ties FROM TB WHERE CODE=T.CODE AND DATE=T.DATE ORDER BY tcount DESC)

Answer 9

apply要SQL 2005以上版本才支持