--统计本班的平均成绩,如果平均分在70分以上,则显示“成绩优秀”,并显示出最好成绩及学员姓名,如果在70分以下则显示“成绩较差”,并显示出最差成绩及学员姓名。
declare @score int
select @score=AVG(score) from Score
IF(@score>70)
BEGIN
print '成绩优秀'
--select Max(score)as '成绩',max(studentname)as '姓名' from score,student where student.studentid=score.studentid
select top 1 studentname as '姓名' from score,student where student.studentid=score.studentid order by score desc
END
ELSE
BEGIN
print '成绩较差'
--select MIN(score)as '成绩',min(studentname)as '姓名' from score,student where student.studentid=score.studentid
select top 1 studentname as '姓名' from score,student where student.studentid=score.studentid order by score asc
END
-- 本次考试成绩较差,假定要提分,确保每人成绩都通过。提分规则很简单,先每人都加2分,看是否都通过,如果没有全部通过,每人再加2分,再看是否都通过,如此反复提分,直到所有人都通过为止 ,提分不能超过100分
declare @score int
while(1=1)
begin
select top 1 @score=score.score from score order by score.score asc
if(@score>=60)
break;
UPDATE score set score=score+2
UPDATE score set score=100 where score>100
end
select convert(nvarchar(8),student.studentname)as '姓名',score.score as '成绩',
case
when score>=90 then '优'
when score>=80 then '良'
when score>=60 then '中'
else '不及格'
end AS '评价'
from score join student on score.studentid=student.studentid
-- 根据如下规则对机试成绩进行反复加分,直到平均分超过85分为止。请编写T-SQL语句实现:
--90分以上: 不加分
--80-89分: 加1分
--70-79分: 加2分
--60-69分: 加3分
--60分以下: 加4分
declare @score int
while(1=1)
begin
select @score=AVG(score) from score
if(@score>85)
break;
UPDATE score set score=score+1 where score >=80 and score<=89
UPDATE score set score=score+2 where score >=70 and score<=79
UPDATE score set score=score+3 where score >=60 and score<=69
UPDATE score set score=score+4 where score <=60
end
--把13-1等一系列这样的数字分离
select txt from demo
order by CONVERT(int, left(txt,charindex('-',txt)-1)),
CONVERT (int,right(txt,len(txt)-charindex('-',txt)))
select ID,txt ,substring(txt,1,charindex('-',txt)-1),
STUFF(txt,1,charindex('-',txt),'') from demo
数据库中几种常见的题型
最新推荐文章于 2024-01-20 20:06:43 发布