34,588
社区成员
发帖
与我相关
我的任务
分享
select distinct czje ,COUNT(1) as 人数 from tmp_cz where base_comp_code = 'DFHW' group by czje order by czje desc
select * from # as t where 消费金额 in (select distinct top 10 消费金额 from # where 部门号=t.部门号 order by 消费金额 desc)
--> 测试数据:#
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
*/
--> 测试数据:#
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 # 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
*/
select * from 表名 a
where (select count(distinct 消费金额) from 表名 where 部门号=a.部门号 and 消费金额>=a.消费金额)<=10