数据如下：
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高手们帮忙
------解决方案--------------------
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)
求和就子查询
------解决方案--------------------
- SQL code
select *,row_number() over(order by cname,tcount) as rownumberfrom tbwhere rownumber <=5
------解决方案--------------------
- SQL code
--> 测试数据：[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*/