日期:2014-05-17  浏览次数:20570 次

求一SQL语句:根据一个级别表,判断属于那级别加上该级别所赠的分数
SQL code
--等级表
/*******************************
范围:0到60包括开头不包括结尾
level_add:分数属于该级别要加的分数
想要结果:
原始分数(score)、级别ID(level_id)、属于该级别加后的分数(score+level_add)
*******************************/
CREATE TABLE stu_level
(
    level_id INT,
    level_name VARCHAR(20),
    level_start INT,
    level_end INT,
    level_add INT
)
INSERT INTO stu_level 
SELECT 1,'差',0,60,10 UNION
SELECT 2,'良',60,80,5 UNION 
SELECT 3,'优',80,100,0

--分数表
CREATE TABLE stu_score
(
    score INT
)
INSERT INTO stu_score 
SELECT 50 UNION 
SELECT 55 UNION 
SELECT 65 UNION 
SELECT 85


------解决方案--------------------
SQL code

CREATE TABLE stu_level
(
    level_id INT,
    level_name VARCHAR(20),
    level_start INT,
    level_end INT,
    level_add INT
)

INSERT INTO stu_level 
SELECT 1,'差',0,60,10 UNION
SELECT 2,'良',60,80,5 UNION 
SELECT 3,'优',80,100,0

CREATE TABLE stu_score
(
    score INT
)
INSERT INTO stu_score 
SELECT 50 UNION 
SELECT 55 UNION 
SELECT 65 UNION 
SELECT 85


update a 
set a.score=a.score+b.level_add
from stu_score a
left join stu_level b
on a.score between b.level_start and b.level_end

select * from stu_score

/*
score
-----------
60
65
70
85

(4 row(s) affected)
*/

------解决方案--------------------
SQL code
update a set 
level_add =level_add+score
from stu_level as a,stu_score as b
where b.score between a.level_start and a.level_end

select * from stu_level

/*
level_id    level_name           level_start level_end   level_add
----------- -------------------- ----------- ----------- -----------
1           差                    0           60          60
2           良                    60          80          70
3           优                    80          100         85

(3 行受影响)


*/