记录select的选择_SQL经典50题解答过程记录

学生表（Student），课程表（Course），教师表（Teacher），以及成绩表（SC）
学生表（Student ）有四个字段 sid--学生id，sname--学生姓名，sage--学生年龄，ssex--学生性别

create table Student(sid varchar(10),sname varchar(10),sage datetime,ssex nvarchar(10));
insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '1990-05-20' , '男');
insert into Student values('04' , '李云' , '1990-08-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-03-01' , '女');
insert into Student values('07' , '郑竹' , '1989-07-01' , '女');
insert into Student values('08' , '王菊' , '1990-01-20' , '女');

课程表（Course）有三个字段，cid--课程id，cname--课程名，tid--教师id

create table Course(cid varchar(10),cname varchar(10),tid varchar(10));
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');

教师表（Teacher）有两个字段，tid--教师id，tname--教师姓名

create table Teacher(tid varchar(10),tname varchar(10));
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');

成绩表（SC）有三个字段，sid--学生id，cid--课程id，score--成绩

create table SC(sid varchar(10),cid varchar(10),score decimal(18,1));
insert into SC values('01' , '01' , 80);
insert into SC values('01' , '02' , 90);
insert into SC values('01' , '03' , 99);
insert into SC values('02' , '01' , 70);
insert into SC values('02' , '02' , 60);
insert into SC values('02' , '03' , 80);
insert into SC values('03' , '01' , 80);
insert into SC values('03' , '02' , 80);
insert into SC values('03' , '03' , 80);
insert into SC values('04' , '01' , 50);
insert into SC values('04' , '02' , 30);
insert into SC values('04' , '03' , 20);
insert into SC values('05' , '01' , 76);
insert into SC values('05' , '02' , 87);
insert into SC values('06' , '01' , 31);
insert into SC values('06' , '03' , 34);
insert into SC values('07' , '02' , 89);
insert into SC values('07' , '03' , 98);

---

1.查询"01"课程比"02"课程成绩高的学生的信息及课程分数

SELECT * from student
RIGHT JOIN
(SELECT t1.sid,class1,class2
FROM
(SELECT sid,score as class1 FROM sc where sc.CId='01')as t1,
(SELECT sid,score as class2 FROM sc where sc.CId='02')as t2
where t1.sid=t2.sid and t1.class1>t2.class2)r
on student.SId=r.sid

#第二种
SELECT A.*,B.score
FROM (SELECT * FROM sc WHERE CId='01')A
LEFT JOIN (SELECT * FROM sc WHERE CId='02')B
ON A.sid=B.sid
WHERE A.score>B.score;

1.1 查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )

SELECT * 
FROM 
(SELECT * FROM sc WHERE sc.CId='01')as t1
LEFT JOIN
(SELECT * from sc WHERE sc.CId='02')as t2
on t1.sid=t2.sid

1.2 查询同时存在01和02课程的情况

SELECT *
FROM
(SELECT * FROM sc WHERE sc.CId='01')as t1,
(SELECT * FROM sc WHERE sc.CId='02')as t2
WHERE t1.sid=t2.sid

1.3 查询选择了02课程但没有01课程的情况

where & not in

SELECT *
from sc
where sc.SId NOT IN 
(SELECT SId FROM sc where sc.CId='01')
and sc.CId='02'

2.查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩

group by & join

SELECT s.sid,sname,AVG(sc.score)
FROM 
student as s INNER JOIN sc
on s.SId=sc.SId
GROUP BY s.SId
HAVING AVG(sc.score)>60

子查询

SELECT * 
FROM
(SELECT sid,AVG(sc.score) FROM sc
GROUP BY SId
HAVING AVG(sc.score)>60)r
LEFT JOIN student as s on s.sid=r.sid

3.查询在 SC 表存在成绩的学生信息

Distinct

SELECT DISTINCT student.*
FROM student,sc
where student.SId=sc.SId

4.查询所有同学的学生编号、学生姓名、选课总数、所有课程的成绩总和

gruop by & sum & join & count

SELECT s.*,COUNT(sc.cid),SUM(score)
FROM student as s LEFT JOIN sc ON s.sid=sc.SId
GROUP BY s.SId;

5.查询「李」姓老师的数量

SELECT COUNT(*)
FROM teacher
WHERE Tname LIKE ('李%')

6.查询学过「张三」老师授课的同学的信息

SELECT s.*
FROM student as s INNER JOIN sc ON s.sid=sc.SId
INNER JOIN course as c on c.CId=sc.CId
INNER JOIN teacher as t on t.TId=c.TId
WHERE Tname='张三'

#第二种解法
SELECT * FROM student 
WHERE SId in(SELECT DISTINCT sid FROM sc 
WHERE cid=(SELECT CId FROM course
WHERE tid=(SELECT tid FROM teacher WHERE tname='张三')));

7.查询没有学全所有课程的同学的信息

SELECT s.*,COUNT(cid)
FROM sc RIGHT JOIN student as s 
on sc.sid=s.SId
GROUP BY sc.SId
HAVING COUNT(CId)<(SELECT COUNT(DISTINCT CId)
FROM course)

8.查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息

SELECT s.*
FROM sc INNER JOIN student as s
on sc.sid=s.sid
WHERE sc.cid in 
(SELECT cid
FROM sc 
WHERE sc.sid='01')
AND sc.sid !='01'
GROUP BY sc.SId

#第二种
SELECT * FROM student 
WHERE SId in (SELECT sid FROM sc 
WHERE cid in (SELECT cid FROM sc WHERE sId='01')) AND sid!='01';

9.查询和" 01 "号的同学学习的课程完全相同的其他同学的信息

SELECT * FROM student 
WHERE sid in 
(SELECT sid FROM
(SELECT * from sc WHERE cid in
(SELECT cid FROM sc
WHERE sid='01'))b
GROUP BY SId
HAVING COUNT(cid)=(SELECT COUNT(cid) FROM sc WHERE sid='01'))
AND sid !='01'

#第二种
select * from student where sid in (select b.sid from sc b where b.sid not in 
(select a.sid from sc a where a.cid not in (select cid from sc where sid = '01')) 
and b.sid <> '01' group by b.sid 
having count(1)  = (select count(1) from sc where sid = '01'));
#(1） count(1) 会统计表中的所有的记录数，包含字段为null 的记录。
#（2） count(字段) 会统计该字段在表中出现的次数，忽略字段为null 的情况。即不统计字段为null 的记录。

10.查询没学过"张三"老师讲授的任一门课程的学生姓名

SELECT sname FROM student 
WHERE sid NOT IN (
SELECT sid FROM sc 
LEFT JOIN course ON sc.cid=course.cid
LEFT JOIN teacher ON course.tid=teacher.tid 
WHERE tname='张三' )

11.查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

SELECT sname,s.sid,AVG(sc1.score)as avg_score
FROM (SELECT * FROM sc WHERE score<60)sc1,
student as s 
WHERE sc1.sid=s.SId
GROUP BY sc1.sid
HAVING COUNT(sc1.cid)>=2

12.检索" 01 "课程分数小于 60，按分数降序排列的学生信息

SELECT s.* 
from sc join student as s on s.SId=sc.SId 
WHERE cid='01' AND score<60
ORDER BY score DESC

13.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

SELECT sc.*,avg_score
FROM sc LEFT JOIN 
(SELECT sc.sid,AVG(sc.score)as avg_score 
from sc
GROUP BY sc.SId)r
ON sc.SId=r.sid
ORDER BY avg_score DESC

14.查询各科成绩最高分、最低分和平均分,以如下形式显示：

课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列。

SELECT 
cid AS 课程ID,
COUNT(sid) AS 课程人数,
MAX(score) AS 最高分,
MIN(score) AS 最低分,
AVG(score) AS 平均分,
SUM(及格) / COUNT(sid) AS 及格率,
SUM(中等) / COUNT(sid) AS 中等率,
SUM(优良) / COUNT(sid) AS 优良率,
SUM(优秀) / COUNT(sid) AS 优秀率 
FROM
(SELECT *,
CASE WHEN score >= 60 THEN 1 ELSE 0 END AS 及格,
CASE WHEN score >= 70 AND score<80 THEN 1  ELSE 0 END AS 中等, 
CASE WHEN score >= 80 AND score<90 THEN 1  ELSE 0 END AS 优良,
CASE WHEN score >= 90 THEN 1 ELSE 0 END AS 优秀
FROM sc) a 
GROUP BY cid 
ORDER BY COUNT(sid) DESC,cid

15.按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺

SELECT 
  a.*,
  COUNT(a.score) AS 排名 
FROM
  sc AS a 
  LEFT JOIN sc AS b 
    ON a.cid = b.cid 
    AND a.score < b.score 
GROUP BY a.cid,
  a.sid 
ORDER BY a.cid,排名 

15.1 按各科成绩进行行排序，并显示排名， Score 重复时合并名次

SELECT 
  a.*,
  COUNT(b.score)+1 AS 排名 
FROM
  sc AS a 
  LEFT JOIN sc AS b 
    ON a.cid = b.cid 
    AND a.score < b.score 
GROUP BY a.cid,
  a.sid 
ORDER BY a.cid,
  排名 ;

16.查询学生的总成绩，并进行排名，总分重复时保留名次空缺

SELECT 
  a.*,
  @rank := @rank + 1 AS rank 
FROM
  (SELECT 
    sid,
    SUM(score) 
  FROM
    sc 
  GROUP BY sid 
  ORDER BY SUM(score) DESC) a,
  (SELECT 
    @rank := 0) b ;

16.1 查询学生的总成绩，并进行排名，总分重复时不保留名次空缺

SELECT 
  a.*,
  CASE
    WHEN @fscore = a.sumscore 
    THEN @rank 
    WHEN @fscore := a.sumscore 
    THEN @rank := @rank + 1 
  END AS 排名 
FROM
  (SELECT 
    sc.sid,
    SUM(score) AS sumscore 
  FROM
    sc 
  GROUP BY sid 
  ORDER BY SUM(score) DESC) AS a,
  (SELECT 
    @rank := 0,
    @fscore := NULL) AS t ;

17. 统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比

SELECT 
sc.CId as 课程编号,
cname as 课程名称,
SUM(case WHEN score>=0 and score<60 THEN 1 ELSE 0 END)as '[60-0]',
SUM(CASE WHEN score>=0 and score<60 THEN 1 ELSE 0 END)/COUNT(sid)as'[60-0]百分比 ',
SUM(CASE WHEN score>=60 and score<70 THEN 1 ELSE 0 END)as '[70-60]',
SUM(CASE WHEN score>=60 and score<70 THEN 1 ELSE 0 END)/COUNT(sid)as '[70-60]百分比' ,
SUM(case WHEN score>=70 and score<85 THEN 1 ELSE 0 END)as '[85-70]',
SUM(CASE WHEN score>=70 and score<85 THEN 1 ELSE 0 END)/COUNT(sid)as'[85-70]百分比 ',
SUM(CASE WHEN score>=85 and score<100 THEN 1 ELSE 0 END)as '[100-85]',
SUM(CASE WHEN score>=85 and score<100 THEN 1 ELSE 0 END)/COUNT(sid)as '[100-85]百分比' 
FROM sc
JOIN course
on sc.cid=course.CId
group BY sc.CId,Cname

18.查询各科成绩前三名的记录

SELECT a.*,COUNT(b.score)+1 as rank
FROM sc as a
LEFT JOIN sc as b
on a.CId=b.CId
AND a.score<b.score
GROUP BY a.CId,a.SId
HAVING rank<=3
ORDER BY a.CId,rank

19.查询每门课程被选修的学生数

SELECT cid,COUNT(sid)
FROM sc 
GROUP BY SId

20.查询出只选修两门课程的学生学号和姓名

SELECT sc.sid,sname
FROM sc INNER JOIN student  
on sc.sid=student.SId
GROUP BY sc.SId
HAVING COUNT(cid)<=2

21. 查询男生、女生人数

SELECT ssex,COUNT(ssex)
FROM student
GROUP BY Ssex

SELECT 
SUM(case WHEN ssex='男' THEN 1 ELSE 0 END)as '男生数量',
SUM(case WHEN ssex='女' THEN 1 ELSE 0 END)as '女生数量'
FROM student

22. 查询名字中含有「风」字的学生信息

SELECT *
FROM student 
WHERE Sname LIKE '%风%'

23查询同名同性学生名单，并统计同名人数

SELECT sname,COUNT(sname)
FROM student 
GROUP BY Sname
having COUNT(sname)>1

24.查询 1990 年出生的学生名单

SELECT *
FROM student 
WHERE year(Sage) like '1990%'

25.查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列

SELECT CId,AVG(score)
FROM sc
GROUP BY CId
ORDER BY AVG(score) DESC,cid 

26.查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩

SELECT sc.sid,sname,AVG(score)
FROM sc,student
WHERE sc.sid=student.SId
GROUP BY sc.sId
HAVING AVG(score)>=85

SELECT sc.sid,sname,AVG(score)
FROM sc INNER JOIN student
ON sc.sid=student.SId
GROUP BY sc.sId
HAVING AVG(score)>=85

27.查询课程名称为「数学」，且分数低于 60 的学生姓名和分数

SELECT Sname,score
from sc INNER JOIN course 
on sc.cid=course.CId
and course.cname='数学'
AND sc.score<60
INNER JOIN student as s
on s.sid=sc.SId

28. 查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）

SELECT Sname,sc.cid,sc.score
FROM student as s INNER JOIN sc
on s.sid=sc.sid

29.查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数

SELECT Sname,sc.cid,sc.score
FROM student as s INNER JOIN sc
on s.sid=sc.sid
and score>70

30.查询不及格的课程

select * from SC where score<60

31. 查询课程编号为01且课程成绩在80分以上的学生的学号和姓名

select A.Sid,B.Sname from (select * from SC where score>80 and Cid=01)A
left join Student B on A.Sid=B.Sid

32. 求每门课程的学生人数

select Cid,COUNT(*)学生人数 from SC group by Cid

33. 成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩

select top 1* from SC 
where Cid=(select Cid from Course where Tid=(select Tid from Teacher where Tname='张三')) 
order by score desc

34. 成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩

select *from(select *,DENSE_RANK()over (order by score desc)A 
from SC 
where Cid=(select Cid from Course where Tid=(select Tid from Teacher where Tname='张三')))B
where B.A=1

35. 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩

select C.Sid,max(C.Cid)Cid,max(C.score)score from SC C 
left join(select Sid,avg(score)A from SC group by Sid)B 
on C.Sid=B.Sid
where C.score=B.A
group by C.Sid
having COUNT(0)=(select COUNT(0)from SC where Sid=C.Sid)

36. 查询每门功成绩最好的前两名

select * from
(select *,ROW_NUMBER()over(partition by Cid order by score desc)A from SC)B
where B.A<3

37.统计每门课程的学生选修人数（超过5人的课程才统计）。
要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列.

select Cid,COUNT(Sid)选修人数 from SC 
group by Cid
having COUNT(Sid)>5
order by 选修人数 desc,Cid

38. 检索至少选修两门课程的学生学号

select Sid from SC
group by Sid
having COUNT(Cid)>=2

39. 查询选修了全部课程的学生信息

select Sid from SC 
group by Sid 
having count(Cid)=(select distinct COUNT(0)a from Course)

40. 查询各学生的年龄，只按年份来算

select Sid,datediff(yy,Sage,GETDATE())年龄 from Student

41. 按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一

select *,(case when convert(int,'1'+substring(CONVERT(varchar(10),Sage,112),5,8))
<convert(int,'1'+substring(CONVERT(varchar(10),GETDATE(),112),5,8)) 
then datediff(yy,Sage,GETDATE()) 
else datediff(yy,Sage,GETDATE())-1 
end)age 
from Student 

42. 查询本周过生日的学生

select *,(case when datename(wk,convert(datetime,(convert(varchar(10),year(GETDATE()))
+substring(convert(varchar(10),Sage,112),5,8))))=DATENAME(WK,GETDATE()) 
then 1 else 0 end)生日提醒
from Student

43. 查询下周过生日的学生

select *,(case when datename(wk,convert(datetime,(convert(varchar(10),year(GETDATE()))+
substring(convert(varchar(10),Sage,112),5,8))))=DATENAME(WK,GETDATE())+1 
then 1 else 0 end)生日提醒
from Student

44. 查询本月过生日的学生

 select *,(case when month(convert(datetime,(convert(varchar(10),year(GETDATE()))
+substring(convert(varchar(10),Sage,112),5,8))))=month(GETDATE())
then 1 else 0 end)生日提醒
from Student

45. 查询下月过生日的学生

select *,(case when month(convert(datetime,(convert(varchar(10),year(GETDATE()))
+substring(convert(varchar(10),Sage,112),5,8))))=month(GETDATE())+1
then 1 else 0 end)生日提醒
from Student