sql分组查询 TOP N

Question

我有一个表,就几千数据而已,下面我随便写点数据意思下
部门号   员工编号   消费金额
a      001             100
a      002             100
a      003              90
a      004              85
a      005              85
a      006              80
a      007              70
a      008               60 
b      009              100
b      010              100
b      012               100
b      013                90
b      014                85
b      015                85
b      016               80
b      017               70
b      018                60 
b      019                95
C      020               200
C      021                200
C      022                170
C      023                210
C      024                180
我要达到的效果就是按部门分组,排出消费最多的前10位(这里的前10位消费额相同的是人是并列的 比如a部门的001和002消费了100块当然是并列第一了,004, 005消费了90元就是并列第三 ,我要的结果就是各部门消费第一至第十记录)    
不知道我这样说大家明白不明白. 

Answer 1

不明白你需要什么样的数据，这里选前2位的数据，你看看哪个是你要的结果--> 测试数据：#
if object_id('tempdb.dbo.#') is not null drop table #
create table #(部门号 varchar(8), 员工编号 varchar(8), 消费金额 int)
insert into #
select 'a', '001', 100 union all
select 'a', '002', 100 union all
select 'a', '003', 90 union all
select 'a', '004', 85 union all
select 'a', '005', 85 union all
select 'a', '006', 80 union all
select 'a', '007', 70 union all
select 'a', '008', 60 union all
select 'b', '009', 100 union all
select 'b', '010', 100 union all
select 'b', '012', 100 union all
select 'b', '013', 90 union all
select 'b', '014', 85 union all
select 'b', '015', 85 union all
select 'b', '016', 80 union all
select 'b', '017', 70 union all
select 'b', '018', 60 union all
select 'b', '019', 95 union all
select 'C', '020', 200 union all
select 'C', '021', 200 union all
select 'C', '022', 170 union all
select 'C', '023', 210 union all
select 'C', '024', 180select * from # as t where 员工编号 in (select top 2 员工编号 from # where 部门号=t.部门号 order by 消费金额 desc)
/*
部门号   员工编号 消费金额
-------- -------- -----------
a        001      100
a        002      100
b        010      100
b        012      100
C        021      200
C        023      210
*/select * from # as t where 消费金额 in (select distinct top 2 消费金额 from # where 部门号=t.部门号 order by 消费金额 desc)
/*
部门号   员工编号 消费金额
-------- -------- -----------
a        001      100
a        002      100
a        003      90
b        009      100
b        010      100
b        012      100
b        019      95
C        020      200
C        021      200
C        023      210
*/

Answer 2

--> 测试数据：#
if object_id('tempdb.dbo.#') is not null drop table #
create table #(部门号 varchar(8), 员工编号 varchar(8), 消费金额 int)
insert into #
select 'a', '001', 100 union all
select 'a', '002', 100 union all
select 'a', '003', 90 union all
select 'a', '004', 85 union all
select 'a', '005', 85 union all
select 'a', '006', 80 union all
select 'a', '007', 70 union all
select 'a', '008', 60 union all
select 'b', '009', 100 union all
select 'b', '010', 100 union all
select 'b', '012', 100 union all
select 'b', '013', 90 union all
select 'b', '014', 85 union all
select 'b', '015', 85 union all
select 'b', '016', 80 union all
select 'b', '017', 70 union all
select 'b', '018', 60 union all
select 'b', '019', 95 union all
select 'C', '020', 200 union all
select 'C', '021', 200 union all
select 'C', '022', 170 union all
select 'C', '023', 210 union all
select 'C', '024', 180--每个部门消费前十名？
select [排名],[部门号],[员工编号],[消费金额] from
(--如果排名号要连续比如 1,1,2,2,3 则用dense_rank()
select rank() over(partition by [部门号] order by [消费金额] desc) [排名],* 
from #
) t
 where t.[排名] < 10 order by [部门号],[消费金额] desc
/* 
排名                   部门号      员工编号     消费金额
-------------------- -------- -------- -----------
1                    a        001      100
1                    a        002      100
3                    a        003      90
4                    a        004      85
4                    a        005      85
6                    a        006      80
7                    a        007      70
8                    a        008      60
1                    b        009      100
1                    b        010      100
1                    b        012      100
4                    b        019      95
5                    b        013      90
6                    b        014      85
6                    b        015      85
8                    b        016      80
9                    b        017      70
1                    C        023      210
2                    C        020      200
2                    C        021      200
4                    C        024      180
5                    C        022      170
*/

Answer 3

select * from # as t where 消费金额 in (select distinct top 10 消费金额 from # where 部门号=t.部门号 order by 消费金额 desc)

Answer 4

hokor 大哥,你这个我测试了,有点不对.我就拿一个部门的数据给你看吧.
select distinct czje ,COUNT(1) as 人数 from tmp_cz where base_comp_code = 'DFHW' group by czje order by czje  desc这个我是查出一个部门里前10的充值金额
然后跟你的那个查询对比下
(人数) czje
1 250.00
1 220.00
12 200.00
2 180.00
5 150.00
1 110.00
71 100.00
2 90.00
1 80.00
1 70.00
1 60.00
34 50.00
2 40.00
1 20.00
1 -0.50
1 -0.87
1 -1.50
1 -36.80
用你那个查询出来的结果到200这里就没了.

Answer 5

only_endure这个就更不行了.我一开始就测试过了

Answer 6

断了是什么意思？你想要排名不间断？比如两个并列1，排名为1,1,2? or  1,1,3, or 1,2,3?
如果第一种用 dense_rank()
第二种用  rank()
得三种用 row_number()

Answer 7

你看我贴出来的那个部门的前10的金额
用你的那个查出来只查到了金额200那里就没下文了.正确的应该查到
(人数) czje
1 250.00
1 220.00
12 200.00
2 180.00
5 150.00
1 110.00
71 100.00
2 90.00
1 80.00
1 70.00
这个

Answer 8

select * from 表名 a
where (select count(distinct 消费金额) from 表名 where 部门号=a.部门号 and 消费金额>=a.消费金额)<=10

Answer 9

select distinct dept, money into #tmp from tab
select * from #tmp a where money in (select top 10 money from #tmp where dept=a.dept order by money desc)
------------
要显示人数自己 select ...count into #tmp ... group by

Answer 10

排名 部门编号 人员编号 消费金额
1 A A001 200
1 A A002 200
1 A A003 200
2 A A004 180
2 A A005 180
3 A A006 150
4 A A007 130
5 A A008 120
6 A A009 110
7 A A010 100
7 A A011 100
8 A A012 90
8 A A013 90
10 A A014 80
1 B B001 300
2 B B002 200
2 B B003 200
3 B B004 190
3 B B005 190
3 B B006 190
4 B B007 180
5 B B008 170
6 B B009 160
7 B B010 150
8 B B011 120
8 B B012 120
8 B B013 120
9 B B014 100
9 B B015 100
10 B B016 50
10 B B017 50
1 C C001 280
1 C C002 280
2 C C003 270
3 C C004 260
4 C C005 250
5 C C006 240
6 C C007 230
6 C C008 230
7 C C009 200
8 C C010 200
7 C C011 200
8 C C012 150
8 C C013 150
9 C C014 100
9 C C015 100
10 C C016 60
10 C C017 60
达到的效果就是这样的,不过排名那列是没有的.我写上去只是为了大家方便理解

Answer 11

我就贴2个部门人数比较多的数据出来吧.给大家看看
base_comp_code Base_Personnel_ID czje
GKTX GKTX173 600.00
GKTX GKTX282 500.00
GKTX GKTX222 350.00
GKTX GKTX099 300.00
GKTX GKTX221 300.00
GKTX GKTX280 300.00
GKTX GKTX212 290.00
GKTX GKTX057 250.00
GKTX GKTX204 250.00
GKTX GKTX265 250.00
GKTX GKTX284 250.00
GKTX GKTX171 250.00
GKTX GKTX095 200.00
GKTX GKTX237 200.00
GKTX GKTX104 200.00
GKTX GKTX190 200.00
GKTX GKTX029 200.00
GKTX GKTX192 200.00
GKTX GKTX111 200.00
GKTX GKTX252 200.00
GKTX GKTX085 200.00
GKTX GKTX257 200.00
GKTX GKTX160 200.00
GKTX GKTX246 200.00
GKTX GKTX059 200.00
GKTX GKTX243 200.00
GKTX GKTX267 200.00
GKTX GKTX182 200.00
GKTX GKTX308 200.00
GKTX GKTX238 200.00
GKTX GKTX008 200.00
GKTX GKTX102 200.00
GKTX GKTX161 200.00
GKTX GKTX011 200.00
GKTX GKTX153 200.00
GKTX GKTX003 200.00
GKTX GKTX298 200.00
GKTX GKTX272 180.00
GKTX GKTX114 170.00
GKTX GKTX131 170.00
GKTX GKTX315 170.00
GKTX GKTX043 162.50
GKTX GKTX307 150.00
GKTX GKTX113 150.00
GKTX GKTX026 150.00
GKTX GKTX041 150.00
GKTX GKTX186 150.00
GKTX GKTX236 150.00
GKTX GKTX012 150.00
GKTX GKTX107 150.00
GKTX GKTX199 150.00
GKTX GKTX013 150.00
GKTX GKTX021 150.00
GKTX GKTX193 150.00
GKTX GKTX006 150.00
GKTX GKTX249 150.00
GKTX GKTX275 140.00
GKTX GKTX306 140.00
GKTX GKTX312 130.00
GKTX GKTX278 130.00
GKTX GKTX311 130.00
GKTX GKTX263 120.00
GKTX GKTX030 110.00
GKTX GKTX065 100.00
GKTX GKTX141 100.00
GKTX GKTX092 100.00
GKTX GKTX036 100.00
GKTX GKTX264 100.00
GKTX GKTX164 100.00
GKTX GKTX150 100.00
GKTX GKTX143 100.00
GKTX GKTX210 100.00
GKTX GKTX074 100.00
GKTX GKTX268 100.00
GKTX GKTX040 100.00
GKTX GKTX303 100.00
GKTX GKTX302 100.00
GKTX GKTX317 100.00
GKTX GKTX061 100.00
GKTX GKTX273 100.00
GKTX GKTX005 100.00
GKTX GKTX305 100.00
GKTX GKTX132 100.00
GKTX GKTX096 100.00
GKTX GKTX138 100.00
GKTX GKTX172 100.00
GKTX GKTX119 100.00
GKTX GKTX281 100.00
GKTX GKTX207 100.00
GKTX GKTX045 100.00
GKTX GKTX020 100.00
GKTX GKTX240 100.00
GKTX GKTX318 100.00
GKTX GKTX229 100.00
GKTX GKTX134 100.00
GKTX GKTX167 100.00
GKTX GKTX244 100.00
GKTX GKTX097 100.00
GKTX GKTX196 100.00
GKTX GKTX234 100.00
GKTX GKTX283 100.00
GKTX GKTX010 100.00
GKTX GKTX052 100.00
GKTX GKTX094 100.00
GKTX GKTX076 100.00
GKTX GKTX047 100.00
GKTX GKTX313 100.00
GKTX GKTX101 100.00
GKTX GKTX121 100.00
GKTX GKTX007 100.00
GKTX GKTX269 100.00
GKTX GKTX088 100.00
GKTX GKTX175 100.00
GKTX GKTX014 100.00
GKTX GKTX022 100.00
GKTX GKTX159 100.00
GKTX GKTX279 100.00
GKTX GKTX027 100.00
GKTX GKTX194 100.00
GKTX GKTX162 100.00
GKTX GKTX025 100.00
GKTX GKTX051 100.00
GKTX GKTX032 100.00
GKTX GKTX015 92.50
GKTX GKTX016 87.00
GKTX GKTX031 80.00
GKTX GKTX087 50.00
GKTX GKTX310 50.00
GKTX GKTX299 50.00
GKTX GKTX271 50.00
GKTX GKTX198 50.00
GKTX GKTX039 50.00
GKTX GKTX152 50.00
GKTX GKTX262 50.00
GKTX GKTX319 50.00
GKTX GKTX002 50.00
GKTX GKTX259 50.00
GKTX GKTX291 50.00
GKTX GKTX100 50.00
GKTX GKTX069 50.00
GKTX GKTX200 50.00
GKTX GKTX066 50.00
GKTX GKTX314 50.00
GKTX GKTX019 50.00
GKTX GKTX018 50.00
GKTX GKTX055 50.00
GKTX GKTX217 50.00
GKTX GKTX053 50.00
GKTX GKTX309 50.00
GKTX GKTX184 50.00
GKTX GKTX117 50.00
GKTX GKTX301 50.00
GKTX GKTX316 50.00
GKTX GKTX165 30.00
GKTX GKTX034 20.00
GKTX GKTX084 20.00
GKTX GKTX304 20.00
GKTX GKTX274 -90.50
HWGS HWGS013 586.00
HWGS HWGS053 221.00
HWGS HWGS049 203.50
HWGS HWGS012 200.00
HWGS HWGS031 192.00
HWGS HWGS024 192.00
HWGS HWGS032 191.50
HWGS HWGS055 190.50
HWGS HWGS056 180.50
HWGS HWGS034 176.00
HWGS HWGS039 176.00
HWGS HWGS071 176.00
HWGS HWGS037 176.00
HWGS HWGS050 176.00
HWGS HWGS015 176.00
HWGS HWGS048 176.00
HWGS HWGS023 176.00
HWGS HWGS030 176.00
HWGS HWGS038 175.50
HWGS HWGS003 175.00
HWGS HWGS061 174.00
HWGS HWGS069 173.00
HWGS HWGS028 173.00
HWGS HWGS060 170.00
HWGS HWGS046 169.00
HWGS HWGS021 168.50
HWGS HWGS057 168.50
HWGS HWGS066 167.00
HWGS HWGS058 167.00
HWGS HWGS026 166.00
HWGS HWGS065 165.50
HWGS HWGS045 164.50
HWGS HWGS009 164.50
HWGS HWGS068 160.00
HWGS HWGS067 160.00
HWGS HWGS072 160.00
HWGS HWGS063 157.50
HWGS HWGS041 156.50
HWGS HWGS010 156.50
HWGS HWGS047 155.00
HWGS HWGS014 155.00
HWGS HWGS002 145.50
HWGS HWGS059 144.50
HWGS HWGS064 141.00
HWGS HWGS033 140.50
HWGS HWGS040 137.50
HWGS HWGS062 135.00
HWGS HWGS029 134.50
HWGS HWGS004 132.00
HWGS HWGS075 128.00
HWGS HWGS070 128.00
HWGS HWGS077 128.00
HWGS HWGS074 128.00
HWGS HWGS073 128.00
HWGS HWGS076 128.00
HWGS HWGS054 126.00
HWGS HWGS006 124.50
HWGS HWGS042 112.00
HWGS HWGS080 104.00
HWGS HWGS083 104.00
HWGS HWGS052 102.00
HWGS HWGS018 100.00
HWGS HWGS001 99.50
HWGS HWGS022 92.00
HWGS HWGS005 84.00
HWGS HWGS084 80.00
HWGS HWGS082 80.00
HWGS HWGS020 76.00
HWGS HWGS043 75.50
HWGS HWGS025 56.00
HWGS HWGS008 33.00