初次发帖，求捧场

一个学生表stu，字段如下。。。。。


stu_no nvarchar(10) not null //学号
stu_score int, not null //分数
stu_status int //评价 前%20 status = 1, %30 - %40 status = 2......

现表中有学号和分数两个字段如何有效地根据学生成绩设置学生状态。。。。？

什么意思？

SQL code

declare @num int
select @num=count(*) from stu
with cte as
(select no=row_number() over(order by stu_score),stu_no from stu)
update a set stu_status=case when no*1./@num<=0.2 then 1
                             when no*1./@num<=0.4 then 2
                             when no*1./@num<=0.6 then 3
                             when no*1./@num<=0.8 then 4
                             when no*1./@num<=1 then 5 end
from stu  a,cte b where a.stu_no=b.stu_no

SQL code

select stu_no 学号,stu_score 分数
,case when (100*(select count(*) from cc where stu_no<dd.stu_no )/zs )<20 then 1
 when (100*(select count(*) from cc where stu_no<dd.stu_no )/zs )<40 then 2 
when (100*(select count(*) from cc where stu_no<dd.stu_no )/zs )<60 then 3 
when (100*(select count(*) from cc where stu_no<dd.stu_no )/zs )<80 then 4  
when (100*(select count(*) from cc where stu_no<dd.stu_no )/zs )<=100 then 5 end 评价
from cc as dd,(select count(*) zs from cc) as  ee

上面语句可以实现。如果是查询出来的话，你stu_status就没什么用了。
如果你是要把数据写到stu_status就需要用触发器了。每次insert,update,delete后，更新所有的stu_status值。
大概如下：
SQL code

create trigger tr_update_cc_status  for Insert,Update,Delete
as 
update cc
set stu_status=
case when (100*(select count(*) from cc where stu_no<dd.stu_no )/zs )<20 then 1
 when (100*(select count(*) from cc where stu_no<dd.stu_no )/zs )<40 then 2 
when (100*(select count(*) from cc where stu_no<dd.stu_no )/zs )<60 then 3 
when (100*(select count(*) from cc where stu_no<dd.stu_no )/zs )<80 then 4  
when (100*(select count(*) from cc where stu_no<dd.stu_no )/zs )<100 then 5 end 
from cc as dd,(select count(*) zs from cc) as  ee