比如我有下面这张表
表T(id 为人员唯一id是varchar类型,xk是学科char类型,cj是成绩int类型)
id xk cj
001 数学 99
002 数学 98
003 数学 88
004 数学 76
005 数学 65
001 语文 88
002 语文 92
003 语文 88
规则为:优秀的名额规定为20%,良好的为30%,一般的为30%,合格的为20%(比如数据学科总共有100人,那么为修改就是20个人,按平均分排序确定类别)。
怎么用简单的方法来获取每个类别的人员列表呢。
表T(id 为人员唯一id是varchar类型,xk是学科char类型,cj是成绩int类型)
id xk cj
001 数学 99
002 数学 98
003 数学 88
004 数学 76
005 数学 65
001 语文 88
002 语文 92
003 语文 88
规则为:优秀的名额规定为20%,良好的为30%,一般的为30%,合格的为20%(比如数据学科总共有100人,那么为修改就是20个人,按平均分排序确定类别)。
怎么用简单的方法来获取每个类别的人员列表呢。
当然是多个,不能少了。
就算10了个。
我知道语句当然不难,性能好的语句就难了,我想不出性能好的语句才到这里问的。
select
'001', '数学', 99
union all select
'002', '数学', 98
union all select
'003', '数学', 88
union all select
'004', '数学', 76
union all select
'005', '数学', 65
union all select
'001', '语文', 88
union all select
'002', '语文', 92
union all select
'003', '语文', 88
select *,
case when id in (select top 20 PERCENT id from @t where xk=a.xk order by cj desc) then '优秀'
when id in (select top 20 PERCENT id from @t where xk=a.xk order by cj) then '合格'
when id in (select top 50 PERCENT id from @t where xk=a.xk order by cj desc) and
id not in (select top 20 PERCENT id from @t where xk=a.xk order by cj desc) then '良好'
when id in (select top 50 PERCENT id from @t where xk=a.xk order by cj) and
id not in (select top 20 PERCENT id from @t where xk=a.xk order by cj) then '一般'
end as class
from @t a
---------- ---------- ----------- -----
001 数学 99 优秀
002 数学 98 良好
003 数学 88 良好
004 数学 76 一般
005 数学 65 合格
001 语文 88 合格
002 语文 92 优秀
003 语文 88 良好(所影响的行数为 8 行)
insert @t select 1,'sx',99
union all select 2,'sx',98
union all select 3,'sx',88
union all select 4,'sx',77
union all select 5,'sx',65
union all select 2,'yw',92
union all select 1,'yw',88
union all select 3,'yw',88declare @x table (id int identity(1,1),lvl varchar(10),cj int)
insert @x select '优秀',min(ac) from
(select top 20 percent *
from (select id,avg(cj) ac from @t group by id) x
order by ac desc) xinsert @x select '良好',min(ac) from
(select top 50 percent *
from (select id,avg(cj) ac from @t group by id) x
order by ac desc) xinsert @x select '一般',min(ac) from
(select top 80 percent *
from (select id,avg(cj) ac from @t group by id) x
order by ac desc) xinsert @x select '合格',min(ac)
from (select id,avg(cj) ac from @t group by id) xselect a.*,b.lvl
from (select id,avg(cj) ac from @t group by id) a
inner join @x b
on a.ac>=b.cj
where not exists(select 1 from @x where cj<=a.ac and cj>b.cj)
order by ac desc
/*结果
2 95 优秀
1 93 良好
3 88 良好
4 77 一般
5 65 合格
*/