sql一些查询题---sqlserver

表结构

成绩表数据太少了，我加了几条，这是新的成绩表数据

1. 查询课程编号为0001的课程分数比0002的课程分数高的同学的学号和分数；

思路：分别查出成绩表中课程编号是0001和0002的记录，作为两个子查询 inner join起来，这样就形成了一行有学号，0001课程分数，0002课程分数，课程编号的新纪录，然后直接比较就行了。

select a.No '学号',a.Score '0001分数',b.Score '0002分数' from 
(
select t1.No,t1.ClassNo,t1.Score from Score t1 where t1.ClassNo = '0001'
) as a 
inner join 
(
select t2.No,t2.ClassNo,t2.Score from Score t2 where t2.ClassNo = '0002'
) as b
on a.No = b.No
where a.Score>b.Score

结果：

2.平均成绩大于80分的学生的学号和平均成绩

思路：很简单，先按学号分组查出平均成绩，再筛选大于80分的

select a.No,AVG(a.Score) as AvgScore  from Score a 
group by a.No having AVG(a.Score)>80

结果：

3.查询所有学生编号，姓名，选课数量，总成绩

select a.No,a.Name,SUM(b.Score) SumScore,COUNT(c.ClassNo) CourseCount from Student a 
inner join Score b on a.No =b.No 
inner join Course c on c.ClassNo = b.ClassNo
group by a.No,a.Name

考虑到有的学生可能没有成绩，即在成绩表没有数据，那么SumScore会查出null，不太好看，可以加上case when

select a.No,a.Name,
SUM(case when b.Score is null then 0 else b.Score end) SumScore,
COUNT(c.ClassNo) CourseCount from Student a 
inner join Score b on a.No =b.No 
inner join Course c on c.ClassNo = b.ClassNo
group by a.No,a.Name

结果是一样的，因为我们测试数据里学生都有成绩

4.查询姓猴的学生

这个就很简单了，字符串匹配就是

select * from Student a where a.Name like '猴%'

5.查询没学过“孟扎扎”老师课程的学生的学号，姓名

思路：先找学过孟扎扎老师课程的学生，再排除掉他们

select No,Name from Student where No not in(
select No from Score where ClassNo = (
select ClassNo from Course where TeacherNo = (
select TeacherNo from Teacher where TeacherName = '孟扎扎')))

这里需要注意一个问题，如果老师不止教一门课的话，Score 查询条件要用 ClassNo in (xxxxx)

另外也可以用 Inner join 来写

6.查询学过“孟扎扎”老师课程的学生的学号，姓名

思路：把上题的not in 改成 in就行了

7.学过语文和数学的学生学号和姓名

select No,Name from Student where No in (
select No from Score where ClassNo in(
select classNo from
Course where ClassName in ('语文','数学')))

说一下5,6,7三个例子为什么不用Inner join，因为一般情况下，如果不需要展示多张表的数据，我不喜欢用表连接，而且这里因为一个学生有多门课的原因，如果用inner join的话一定会出现重复数据，还得加上distinct，性能要差一点，所以我选择用 in / not in

8.查询选择0002课程的人数，平均分，总分

很简单

select SUM(Score) '总分',AVG(Score) '平均分',COUNT(Score) '人数' from Score where ClassNo = '0002'

9.查找所有课程都低于90分的学生学号

思路：查出所有学生的课程数和所有学生低于90分的课程数，当同一个学生课程数和低于90分的课程数相等时，必然是都低于90分

select a.No from (
select No,COUNT(No) Num from Score group by No
) a
inner join
(
select No,COUNT(No) Num from Score where Score<90 group by No
) b on a.No=b.No
where a.Num = b.Num

10.查找课程学全的学生学号

select stu.No from Student stu
left join Score sc on stu.No = sc.No 
group by stu.No having COUNT(sc.ClassNo) = (select COUNT(ClassNo) from Course)

这里需要注意的是，学生表和成绩表用的左连接，来避免漏掉一门课没都学的学生，按学号分组后，分数表里的记录条数等于课程表的总记录数的学生就是所有课程都学了的学生。

11.跟学号0001同学学的课程完全相同的学生学号和姓名

思路：1.首先如果完全一样，则课程数目肯定一样，也就是可以先找出课程数跟0001一样的同学；

　　2.数目一样不代表课程一样，再排除掉有科目不在0001同学范围内的同学。

select * from student where No in (
select No from Score where No !='0001' 
group by No 
having COUNT(No)= (select COUNT(No) from Score where No = '0001')
) and No not in (
select No from Score where ClassNo not in (
 select ClassNo from Score where No='0001') )

12.查看有2门低于90分的同学的信息

select stu.No,stu.Name,AVG(s.Score) AvgScore from Score s 
inner join Student stu on s.No=stu.No
where s.No in (
select No from Score 
where Score<90
group by No having COUNT(Score)>=2
)
group by stu.No,stu.Name

13.按平均分降序显示每个学生的编号以及各科分数

提供2种思路

第一种，查完后，会有冗余数据，需要在代码里去处理

select a.No,a.ClassNo,a.Score,b.avgScore from Score a 
inner join
(
select No,AVG(Score) avgScore from Score 
group by No ) b on a.No=b.No
group by a.No,a.ClassNo,a.Score,b.avgScore
order by b.avgScore desc

第二种，我们知道需要查哪些课程，所以在sql可以case when 处理一下，一行展示完，这样代码就不用再去判断了

select No,
MAX(case when ClassNo='0001' then Score else null end) '0001',
MAX(case when ClassNo='0002' then Score else null end) '0002',
MAX(case when ClassNo='0003' then Score else null end) '0003',
AVG(Score) avgScore from Score 
group by No
order by avgScore desc

这里用到了聚合函数MAX，其实Min,Avg啥的都行，因为这里用的并不是函数本身的功能，只是因为ClassNo和Score不能出现在group by中，不然就会再按这两个分组，而函数内部的case when 条件写死了，所以只会有一条记录，用啥都是一样的。

14.查询各科成绩最高分，最低分，平均分，课程id，课程名称，及格率，中等率，优良率，优秀率

select a.ClassNo,b.ClassName
,MAX(a.Score) '最高分'
,MIN(a.Score) '最低分'
,AVG(a.Score) '平均分'
,SUM(case when a.Score>=60 then 1 else 0 end)*1.0/COUNT(a.No) '及格率'
,SUM(case when a.Score>60 and a.Score<=70 then 1 else 0 end)*1.0/COUNT(a.No) '中等率'
,SUM(case when a.Score>70 and a.Score<=90 then 1 else 0 end)*1.0/COUNT(a.No) '优良率'
,SUM(case when a.Score>90 then 1 else 0 end)*1.0/COUNT(a.No) '优秀率'
from Score a
inner join Course b on a.ClassNo = b.ClassNo
group by a.ClassNo,b.ClassName

其他没难度，主要说一下这个 xx率的问题，通过case when，符合条件的就用sum累加1，这样得到符合条件的总数，然后除以这门课程的总学生数，注意为什么乘以1.0，因为两个int相除，小于1会变成0，所以x1.0让被除数先变成浮点，这样可以得到小数。

sum换成count也可以实现效果，但是里面的 case when 的 else 必须是 null，不然虽然不符合case when 的条件也会被统计进去，因为虽然是 0 ，但是也是一个数，而null，不算。

15.根据各科成绩排序，并显示排名

15.1可以用row_number() 函数，这个函数其实在分页时用的比较多，partition by 表示排列时按什么字段分组，也可以没有表示不分，order by 表示按分数排序。

这里说一下，如果去掉partition by s.classNo，则排序的会是所有记录，也就是说不分课程只按分数排，如果where条件里限定了课程，那这个partition by 确实用不上。

select s.No,s.ClassNo,s.Score,ROW_NUMBER() over (partition by s.classNo order by s.score desc) '排名' 
from Score s

15.2用rank()函数，用法一样，区别就是同分的人排名一样，如果有2个人同分，那后一位同学名次会跳1位，1-2-2-4这样

select s.No,s.ClassNo,s.Score,rank() over (partition by s.classNo order by s.score desc) '排名' 
from Score s

15.3 dense_rank()，用法还是一样，只是相同分数的同学后面的同学排名时不再跳过名次，1-2-2-3这样

select s.No,s.ClassNo,s.Score,dense_rank() over (partition by s.classNo order by s.score desc) '排名' 
from Score s

16.--查询总成绩并显示排名

没啥可说的，很简单

select s.No,Sum(s.Score) '总分'
,dense_rank() over (order by Sum(s.Score) desc) '排名' 
from Score s 
group by s.No

17. 查找1990年出生的同学

很简单，用内置函数就行了，多列出来几个

select *,YEAR(s.BirthDay),MONTH(s.BirthDay),DAY(s.BirthDay) from Student s 
where YEAR(s.BirthDay) = 1990

18.--查询马化腾老师教的课程的最高分的学生信息和分数

这里需要注意的是，考虑到可能最高分有多个，所以不用top 1这种，而是rank 排序后取 rank是1的

select * from (
select s.No,s.Name,t.TeacherName,c.ClassName,sc.Score,RANK() over(order by sc.Score desc) Num from Score sc
inner join Course c on sc.ClassNo=c.ClassNo
inner join Teacher t on t.TeacherNo=c.TeacherNo
inner join Student s on s.No=sc.No
where t.TeacherName='马化腾' ) as t
where t.Num=1

posted @ 2020-12-23 17:52 luytest 阅读(668) 评论(0) 编辑收藏举报

刷新页面返回顶部

luytest

sql一些查询题---sqlserver

公告