一、使用TOP限制结果集行数
- TOP n [ percent ] [WITH TIES ]
- TOP n:取查询结果的前n行数据。
- TOP n percent:取查询结果的前n%行。
- WITH TIES:包括并列的结果。
- TOP谓词写在SELECT单词的后边
(1)查询年龄最大的三个学生的姓名、年龄及所在的系,
包括并列的情况。
SELECT TOP 3 WITH TIES Sn, age, dept`
FROM S
ORDER BY age DESC
注:如果使用WITH TIES选项,则必须使用ORDER BY子句,否则会出现语法错误
(2)查询VB考试成绩最高的前三名的学生的姓名、所在系和
VB考试成绩,包括并列的情况。
SELECT TOP 3 WITH TIES Sn, dept, Score
FROM S JOIN SC on S.Sno = SC.Sno
JOIN C ON C.Cno = SC.Cno
WHERE Cn = 'VB'
ORDER BY Score DESC
(3)查询选课人数最少的两门课程(不包括没有人选的课程),
列出课程号和选课人数。
select top 2 with ties Cno count(Cno) 选课门数
from SC
group by Cno
order by count(Sno)
(4)查询VB考试中成绩最高的前3名学生的姓名,所在系和VB考试成绩包括并列的情况
select top 3 with ties Sn,dept scorce
from S,SC,C
where S.Sno = SC.Sno and SC.Cno=C.Cno and CN = 'VB'
order by scorce desc
(5)查询计算机系选课门数超过2门的学生中,考试平均成绩最高的前2名(包括并列的情况)学生的学号,选课门数和平均成绩
select top 2 with ties S.Sno,count(Cno) 选课门数 AVG(Scorce)
from S,SC
where S.Sno = SC.Sno and dept ='计算机系'
group by S. Sno
having count(Cno)
order by AVG(Scorce)
CASE表达式
- 一种多分支表达式,可以根据条件列表的值返回多个可能结果中的一个 可用在任何允许使用表达式的地方。
- 不是一个完整的T-SQL语句,不能单独执行。
CASE 测试表达式
WHEN 简单表达式1 THEN 结果表达式1
WHEN 简单表达式2 THEN 结果表达式2
…
WHEN 简单表达式n THEN 结果表达式n
[ ELSE 结果表达式n+1 ]
END
-
计算测试表达式,然后按从上到下的书写顺序将测试表达式的值与每个WHEN子句的简单表达式进行比较。
-
如果与测试表达式的值相等,则返回第一个与之匹配的WHEN子句所对应的结果表达式的值。
-
如果与所有测试表达式的值都不匹配:
-
若有ELSE子句,则返回ELSE子句中指定的值
-
若没有子句,则返回NULL
(1)查询选了VB课程的学生的学号、姓名、所在系和成绩,
并对所在系进行如下处理:
“计算机系”:显示“CS”;
“信息管理系”:显示“IM”;
“通信工程系”:显示“COM”。
SELECT S.Sno 学号,Sn 姓名,
CASE dept
WHEN '计算机系' THEN 'CS'
WHEN '信息管理系' THEN 'IM'
WHEN '通信工程系' THEN 'COM'
END AS 所在系,Score 成绩
FROM S join SC ON S.Sno=SC.Sno
JOIN C ON C.Cno = SC.Cno
WHERE Cn = 'VB'
CASE
WHEN 布尔表达式1 THEN 结果表达式1
WHEN 布尔表达式2 THEN 结果表达式2
…
WHEN 布尔表达式n THEN 结果表达式n
[ ELSE 结果表达式n+1 ]
END
- 按从上到下的书写顺序计算每个WHEN子句的布尔表达式。
- 返回第一个取值为TRUE的布尔表达式对应的结果表达式值。
- 如果没有取值为TRUE的布尔表达式:
- 若有ELSE子句,则返回ELSE子句中指定的值
- 若没有子句,则返回NULL。
(2)统计计算机系每个学生的选课门数,包括没有选课的学生。
列出学号、选课门数和选课情况,对选课情况的处理为:
如果选课门数超过4门,则为“多”;
如果选课门数在2~4范围内,则为“一般”;
如果选课门数少于2门,则为“少”;
如果学生没有选课,则“未选”。
将查询结果按选课门数降序排序。
SELECT S.Sno, COUNT(SC.Cno) 选课门数,CASE
WHEN COUNT(SC.Cno) > 4 THEN '多'
WHEN COUNT(SC.Cno) BETWEEN 2 AND 4 THEN '一般'
WHEN COUNT(SC.Cno) BETWEEN 1 AND 2 THEN '少'
WHEN COUNT(SC.Cno) = 0 THEN '未选'
END AS 选课情况
FROM S LEFT JOIN SC ON S.Sno = SC.Sno
WHERE dept = '计算机系'
GROUP BY S.Sno
ORDER BY COUNT(SC.Cno) DESC