sql实现在评分记录集中去掉最高、最低百分之N评分记录

最新推荐文章于 2023-04-13 16:01:26 发布

smartcore

最新推荐文章于 2023-04-13 16:01:26 发布

阅读量2k

点赞数 1

分类专栏：昨日系列文章标签： sql 去掉最高最低数据库

本文链接：https://blog.youkuaiyun.com/smartcore/article/details/79051420

版权

昨日系列专栏收录该内容

14 篇文章

订阅专栏

sql的优势之一是使用单一的命令能够访问到一个记录集，其重要的特点就是非过程化。

在很多企业流行着360评分体系。比如，有A、B、C三个员工竞选某要职，HR组织吃瓜群众位对其三位进行评分，出于不清楚的原因，HR在拿到群众们的评分结果后，会先对某侯选人的得分结果记录集进行排序，再按比例去掉记录两头（最高、最低）的N%评分记录，最后再进行后续计算。

本文是直接解决以上的“去掉记录两头的N%评分记录”的问题的。最少有以下几种方案：

（本文代码基于SQL Server2005，关于@tb_eval表变量的定义见本文最后的附录）

-- 去掉最高20%、最低20%评分

select *
from
@tb_eval a01
where a01.id not in (select top 20 percent id from @tb_eval a02 where a01.empid=a02.empid order by a02.score desc)
and a01.id not in (select top 20 percent id from @tb_eval a02 where a01.empid=a02.empid order by a02.score asc)
order by empid, score desc

-- 去掉最高20%、最低20%评分

select * 
from 
(
	select row_number() over (partition by empid order by score desc) rowno, *
	from @tb_eval
) a01
where rowno > (select cast(round(count(*)*0.2,0) as int) padcnt from @tb_eval a02 where a01.empid=a02.empid)
and rowno <= (select (count(*) - cast(round(count(*)*0.2,0) as int)) padcnt from @tb_eval a02 where a01.empid=a02.empid)

-- 去掉最高20%、最低20%评分

select a01.*
from 
(
	select row_number() over (partition by empid order by score desc) rowno, *
	from @tb_eval
) a01
left join 
(
	select empid, cast(round(count(*)*0.2,0) as int) topcnt, cast(round(count(*)*0.2,0) as int) lastcnt, count(*) as totalcnt 
	from @tb_eval 
	group by empid
) a02
on a01.empid=a02.empid
where a01.rowno>a02.topcnt and a01.rowno <= (a02.totalcnt - a02. lastcnt)

本问题其实像是大学时代的课后练习作业，写作本文的原因主要是想说明sql的非过程化，你不应该使用游标去完成本任务。

附录：

1、完整的代码

-- 人员表（本例不使用）
declare @tb_emp table table
(
    id    int,				-- 人员ID
    name    varchar(64)		-- 人员名称
);

insert into @tb_emp (id, name) values(1, 'emp01');
insert into @tb_emp (id, name) values(2, 'emp02');
insert into @tb_emp (id, name) values(3, 'emp03');
insert into @tb_emp (id, name) values(4, 'emp04');
insert into @tb_emp (id, name) values(5, 'emp05');
insert into @tb_emp (id, name) values(6, 'emp06');
insert into @tb_emp (id, name) values(7, 'emp07');
insert into @tb_emp (id, name) values(8, 'emp08');
insert into @tb_emp (id, name) values(9, 'emp09');
insert into @tb_emp (id, name) values(10, 'emp10');
insert into @tb_emp (id, name) values(11, 'emp11');
insert into @tb_emp (id, name) values(12, 'emp12');
insert into @tb_emp (id, name) values(13, 'emp13');
insert into @tb_emp (id, name) values(14, 'emp14');
insert into @tb_emp (id, name) values(15, 'emp15');
insert into @tb_emp (id, name) values(16, 'emp16');
insert into @tb_emp (id, name) values(17, 'emp17');
insert into @tb_emp (id, name) values(18, 'emp18');
insert into @tb_emp (id, name) values(19, 'emp19');
insert into @tb_emp (id, name) values(20, 'emp20');
insert into @tb_emp (id, name) values(21, 'emp21');
insert into @tb_emp (id, name) values(22, 'emp22');
insert into @tb_emp (id, name) values(23, 'emp23');
insert into @tb_emp (id, name) values(24, 'emp24');
insert into @tb_emp (id, name) values(25, 'emp25');


-- 评分表
declare @tb_eval table
(
	    id	int not null identity(1,1) primary key,		-- 评分表自动编号
	    empid int,						-- 被评分人员ID
	    score decimal(10, 2),				-- 评分
            examinerid int					-- 评分人员ID
);

-- 给empid为1, 3, 5的评分
insert into @tb_eval (empid, score, examinerid) values (1,9, 4);
insert into @tb_eval (empid, score, examinerid) values (1,76, 19);
insert into @tb_eval (empid, score, examinerid) values (1,37, 10);
insert into @tb_eval (empid, score, examinerid) values (1,90, 13);
insert into @tb_eval (empid, score, examinerid) values (1,29, 7);
insert into @tb_eval (empid, score, examinerid) values (1,6, 2);
insert into @tb_eval (empid, score, examinerid) values (1,69, 17);
insert into @tb_eval (empid, score, examinerid) values (1,76, 5);
insert into @tb_eval (empid, score, examinerid) values (1,60, 15);
insert into @tb_eval (empid, score, examinerid) values (3,13, 20);
insert into @tb_eval (empid, score, examinerid) values (3,49, 6);
insert into @tb_eval (empid, score, examinerid) values (3,72, 25);
insert into @tb_eval (empid, score, examinerid) values (3,84, 2);
insert into @tb_eval (empid, score, examinerid) values (3,4, 22);
insert into @tb_eval (empid, score, examinerid) values (3,17, 11);
insert into @tb_eval (empid, score, examinerid) values (3,74, 18);
insert into @tb_eval (empid, score, examinerid) values (3,66, 21);
insert into @tb_eval (empid, score, examinerid) values (3,62, 8);
insert into @tb_eval (empid, score, examinerid) values (3,4, 4);
insert into @tb_eval (empid, score, examinerid) values (3,66, 13);
insert into @tb_eval (empid, score, examinerid) values (3,41, 3);
insert into @tb_eval (empid, score, examinerid) values (3,94, 16);
insert into @tb_eval (empid, score, examinerid) values (5,87, 24);
insert into @tb_eval (empid, score, examinerid) values (5,71, 14);
insert into @tb_eval (empid, score, examinerid) values (5,39, 22);
insert into @tb_eval (empid, score, examinerid) values (5,55, 12);
insert into @tb_eval (empid, score, examinerid) values (5,13, 19);
insert into @tb_eval (empid, score, examinerid) values (5,73, 16);
insert into @tb_eval (empid, score, examinerid) values (5,45, 10);
insert into @tb_eval (empid, score, examinerid) values (5,9, 15);
insert into @tb_eval (empid, score, examinerid) values (5,4, 8);
insert into @tb_eval (empid, score, examinerid) values (5,31, 11);

-- 全部评分按empid分组后，每组从高到低评分
select row_number() over (partition by empid order by score desc) rowno, * 
from @tb_eval

-- 去掉最高20%、最低20%评分
select *
from 
@tb_eval a01 
where a01.id not in (select top 20 percent id from @tb_eval a02 where a01.empid=a02.empid order by a02.score desc) 
and a01.id not in (select top 20 percent id from @tb_eval a02 where a01.empid=a02.empid order by a02.score asc) 
order by empid, score desc 

-- 去掉最高20%、最低20%评分
select a01.*
from 
(
	select row_number() over (partition by empid order by score desc) rowno, * 
	from @tb_eval 
) a01 
left join 
(
	select empid, cast(round(count(*)*0.2,0) as int) topcnt, cast(round(count(*)*0.2,0) as int) endcnt, count(*) as totalcnt 
	from @tb_eval 
	group by empid 
) a02 
on a01.empid=a02.empid 
where a01.rowno>a02.topcnt and a01.rowno <= (a02.totalcnt - a02.endcnt) 

-- 去掉最高20%、最低20%评分
select * 
from 
( 
	select row_number() over (partition by empid order by score desc) rowno, * 
	from @tb_eval 
) a01 
where rowno > (select cast(round(count(*)*0.2,0) as int) padcnt from @tb_eval a02 where a01.empid=a02.empid) 
and rowno <= (select (count(*) - cast(round(count(*)*0.2,0) as int)) padcnt from @tb_eval a02 where a01.empid=a02.empid)

2、未进行去头、去尾的结果：

rowno	id	empid	score	examinerid
1	4	1	90	13
2	2	1	76	19
3	8	1	76	5
4	7	1	69	17
5	9	1	60	15
6	3	1	37	10
7	5	1	29	7
8	1	1	9	4
9	6	1	6	2
1	22	3	94	16
2	13	3	84	2
3	16	3	74	18
4	12	3	72	25
5	17	3	66	21
6	20	3	66	13
7	18	3	62	8
8	11	3	49	6
9	21	3	41	3
10	15	3	17	11
11	10	3	13	20
12	14	3	4	22
13	19	3	4	4
1	23	5	87	24
2	28	5	73	16
3	24	5	71	14
4	26	5	55	12
5	29	5	45	10
6	25	5	39	22
7	32	5	31	11
8	27	5	13	19
9	30	5	9	15
10	31	5	4	8

3、去头、去尾后的结果：

rowno	id	empid	score	examinerid
3	8	1	76	5
4	7	1	69	17
5	9	1	60	15
6	3	1	37	10
7	5	1	29	7
4	12	3	72	25
5	17	3	66	21
6	20	3	66	13
7	18	3	62	8
8	11	3	49	6
9	21	3	41	3
10	15	3	17	11
3	24	5	71	14
4	26	5	55	12
5	29	5	45	10
6	25	5	39	22
7	32	5	31	11
8	27	5	13	19