50道经典sql练习
# 1 . 查询课程01比课程02分数高的学生信息和01分数02分数：（三种方案：这里选择第三种：子查询，然后条件控制）
select
t1.*,
t2.*
from (select * from scorewhere score.c_id='01') as t1
inner join (select * from score where score.c_id='02') as t2
on t1.s_id=t2.s_id
where t1.s_score>t2.s_score
#------------------------
select
t1.*,
t2.*
from
(select * from score where score.c_id='01') as t1,
(select * from score where score.c_id='02') as t2
where t1.s_score>t2.s_score and t1.s_id=t2.s_id;
#-------------------------
select
s.*,
sc1.s_score as `01`,
sc2.s_score as `02`,
sc3.s_score as `03`
from student s,score sc1,score sc2,score sc3
where s.s_id=sc1.s_id
and s.s_id=sc2.s_id
and s.s_id=sc3.s_id
and sc1.c_id='01'
and sc2.c_id='02'
and sc3.c_id='03'
#and sc1.s_score>sc2.s_score>sc3.s_score
#------------------------
select
s.*,
sc1.s_score as `01`,
sc2.s_score as `02`
from student s
join score sc1
on s.s_id=sc1.s_id and sc1.c_id='01'
left join score sc2
on s.s_id=sc2.s_id and sc2.c_id='02' or sc2.c_id = NULL
where sc1.s_score>sc2.s_score
#-------------------------
SELECT
r.s_id,
sum(r.les01),
sum(r.les02)
from (
select
sc.s_id,
case sc.c_id when '01' then sc.s_score else 0 end as 'les01',
case sc.c_id when '02' then sc.s_score else 0 end as 'les02'
from score sc)r
group by r.s_id having sum(r.les01)>sum(r.les02)
#------------------------
select sc.s_id, case sc.c_id
when '01' then sc.s_score else 0 end as `01课程`,
case sc.c_id
when '03' then sc.s_score else 0 end as `03课程`,
sc.s_score from score sc
# 2、 查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
select
student.*,
AVG(score.s_score)
from score
left join student
on score.s_id=student.s_id
GROUP BY score.s_id
HAVING AVG(score.s_score)>60;
#---------------------------------------------------
select
s.s_id,
s.s_name,
ROUND(AVG(sc.s_score),2)
from student s
join score sc
on s.s_id = sc.s_id
GROUP BY s.s_id,s.s_name
HAVING ROUND(AVG(sc.s_score),2)>=60;
# 3、 查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩（聚合查询，分组后控制）
select
s.s_id,s.s_name,ROUND(AVG(sc.s_score),2)
from student s
join score sc
on s.s_id = sc.s_id
GROUP BY s.s_id,s.s_name
HAVING ROUND(AVG(sc.s_score),2) >=60;
# 4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩，包括有成绩的和无成绩的(关联然后聚合查询)
select
s.s_id,s.s_name,ROUND(AVG(sc.s_score),2) as avg_score
from
student s
left join score sc
on s.s_id = sc.s_id
GROUP BY s.s_id,s.s_name
HAVING avg_score <60
union
select
s.s_id,s.s_name,0 as avg_score
from
student s
where s.s_id
not in (
select distinct s_id from score);
# 5、 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩（聚合查询）
select
s.s_id,s.s_name,count(sc.c_id) as sum_course,sum(sc.s_score) as sum_score
from
student s
left join score sc
on s.s_id=sc.s_id
GROUP BY s.s_id,s.s_name;
# 6、 查询"李"姓老师的数量（模糊查询）
select
count(t_id)
from
teacher
where t_name like '李%';
# 7、 查询学过"张三"老师授课的同学的信息（分数表关联课程表分数表，条件控制找出s_id）
select
s.*
from student s
join score sc
on s.s_id=sc.s_id
where sc.c_id in(
select c_id from course where t_id =(
select t_id from teacher where t_name = '张三'));
#-----------------------------------------
select
DISTINCT s.*
from score sc
left join course c
on sc.c_id=c.c_id
left join teacher t
on c.t_id =t.t_id
left join student s
on sc.s_id=s.s_id;
where t.t_name='张三'
# 8、 查询没学过"张三"老师授课的同学的信息（第一种方案：分数表关联合老师表，条件刨除即可。第二种方案：查学过张三老师课程的学生，然后刨除这部分学生用 notin 控制即可）
select
*
from
student s
where s.s_id not in (
select s.s_id
from student s
join score sc
on s.s_id=sc.s_id
where sc.c_id in(
select c.c_id from course c join teacher t on c.t_id = t.t_id where t_name ='张三'));
#-----------------------------------------------------------
select
sc.s_id ,c.*,t.*
from score sc
left join course c on sc.c_id=c.c_id
left join teacher t on c.t_id=t.t_id
where c.c_id not in (
select course.c_id from course where course.t_id=(select teacher.t_id FROM teacher where teacher.t_name ='张三') );
# 9、 查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息（两个条件控制即可）
select
s.*
from student s,score sc1,score sc2
where s.s_id = sc1.s_id and s.s_id = sc2.s_id and sc1.c_id='01' and sc2.c_id='02';
# 10、 查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息（先查学过”01“和”02“的学生集合，然后一个in一个not in即可）
select
s.*
from student s
where s.s_id in (
select s_id from score where c_id='01' )
and s.s_id not in(
select s_id from score where c_id='02')
# 11、 查询没有学全所有课程的同学的信息（子查询先查所有课程数目，之后用"!="即可）
select
*
from student
where s_id in (
select sc.s_id from score sc GROUP BY sc.s_id having COUNT(sc.c_id)<(select count(*) from course) )
#---------------------
select
s.*
from student s
left join score sc
on sc.s_id=s.s_id
group by s.s_id having count(sc.c_id)<(select count(*) from course)
#----------------------
select
*
from student
where s_id not in(
select
sc.s_id
from score sc
group by s_id
having count(*) =(
select count(distinct c_id) from course
)
)
# 12、 查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息（子查询查01同学的c_id集合，之后用in控制即可）
select
DISTINCT s.s_id ,count(sc.c_id)
from student s
left join score sc
on sc.s_id = s.s_id
where sc.c_id in (
select c_id from score where s_id='01')
group by s.s_id;
#------------------------------
select
*
from student
where s_id in(
select
distinct a.s_id
from score a
where a.c_id in(
select a.c_id from score a where a.s_id='01')
);
# 13、 查询和"01"号的同学学习的课程完全相同的其他同学的信息（多列值完全比较，条件控制采用分层思想：先聚合数目正确的学生，然后再找有这些列值元素的学生，再从中刨除学过01同学没学过的课程的学生）
SELECT
student.*
FROM student
#找到'01'同学学习的课程数相同的学生ids
WHERE s_id IN (
SELECT
s_id
FROM score
GROUP BY s_id HAVING COUNT(s_id) = (
SELECT
COUNT(c_id)
FROM Score
WHERE s_id = '01'
)
)
#找到学过‘01’同学没学过的课程的同学。排除他们
AND s_id NOT IN (
SELECT
s_id
FROM Score
#找到‘01’同学没学过的课程
WHERE c_id IN(
SELECT
DISTINCT c_id
FROM Score
WHERE c_id NOT IN (
#下面的语句是找出‘01’同学学习的课程
SELECT
c_id
FROM score
WHERE s_id = '01'
)
)
GROUP BY s_id
)
#下面的条件是排除01同学
AND s_id NOT IN ('01')
#---------------------------------------------------------------------------------
SELECT
t3.*
FROM
(
SELECT s_id, group_concat(c_id ORDER BY c_id) group1
FROM score
WHERE s_id != '01'
GROUP BY s_id) t1
INNER JOIN (
SELECT group_concat(c_id ORDER BY c_id) group2
FROM score
WHERE s_id = '01'
GROUP BY s_id) t2
ON t1.group1 = t2.group2
INNER JOIN student t3
ON t1.s_id = t3.s_id;
# 14、 查询没学过"张三"老师讲授的任一门课程的学生姓名（先查学过张三的课的的所有学生id）
select
s.s_name
from student s
where s.s_id not in (
select s_id from score where c_id = (
select c_id from course where t_id =(
elect t_id from teacher where t_name = '张三')
)
);
# 15、 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩(先查这些不及格学生按学生分组，查成绩的聚合函数，where聚合之前查<60的，count这些不及格成绩，having分组后>2的得到这些s_id，把avg()加到前面的字段列表即可，关联student表查出s_name即可)
select
s.s_id,s.s_name,ROUND(AVG(sc.s_score))
from student s
left join score sc
on s.s_id = sc.s_id
where s.s_id in(
select s_id from score where s_score<60 GROUP BY s_id having count(1)>=2)
GROUP BY s.s_id,s.s_name
# -----------------------------------
select
x.*,avg(score.s_score)
from score ,
(select
r.s_id
from
(select sc.* from score sc where sc.s_score<'60' )r
group by r.s_id HAVING count(r.c_id)>2)x
where x.s_id=score.s_id
GROUP BY score.s_id
#------------------------------------------
select
s.*,t.*
from student s
inner join
(select sc.s_id ,sc.s_score FROM score sc where sc.c_id='01' and sc.s_score<'60' )t
on s.s_id=t.s_id;
#------------------------------------------------
select
sc.s_id ,AVG(sc.s_score)
from score sc
inner join (
select s.* from student s where s.s_id in(select sc.s_id FROM score sc where sc.c_id='01' and sc.s_score<'60'))r
on r.s_id = sc.s_id
group by sc.s_id
# 16. 检索"01"课程分数小于60，按分数降序排列的学生信息（第一种方案关联score、student然后进行过滤；第二种方案子查询将后面的条件转成id条件）
select
a.*,b.c_id,b.s_score
from
student a,score b
where a.s_id = b.s_id and b.c_id='01' and b.s_score<60 ORDER BY b.s_score DESC;
#-----------------------------------
select
s.*,sc.*
from student s
left join score sc
on s.s_id=sc.s_id
where sc.c_id='01' and sc.s_score<60
# 17. 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩 ( 由列转行：三种方案：这里使用子查询/casewhen函数 )
select
sc.s_id,
(select s_score from score where s_id=sc.s_id and c_id='01') as 语文,
(select s_score from score where s_id=sc.s_id and c_id='02') as 数学,
(select s_score from score where s_id=sc.s_id and c_id='03') as 英语,
round(avg(s_score),2) as 平均分
from score sc
GROUP BY sc.s_id ORDER BY 平均分 DESC;
#--------------------------------------------------------------------------------------------------
SELECT
sc.s_id,
MAX(CASE sc.c_id WHEN '01' THEN sc.s_score END ) 语文,
MAX(CASE sc.c_id WHEN '02' THEN sc.s_score END ) 数学,
MAX(CASE sc.c_id WHEN '03' THEN sc.s_score END ) 英语,
avg(sc.s_score),
s.s_name
FROM Score sc JOIN Student s ON sc.s_id=s.s_id GROUP BY sc.s_id ORDER BY 5 DESC
# 18. 查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率 及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90（列值转行值，三种方案：这里选第二种casewhen条件控制然后聚合统筹，之后关联）
select
sc.c_id,
c.c_name,
MAX(s_score),
MIN(s_score),
ROUND(AVG(s_score),2),
ROUND(100*(SUM(case when sc.s_score>=60 then 1 else 0 end)/SUM(case when sc.s_score then 1 else 0 end)),2) as 及格率,
ROUND(100*(SUM(case when sc.s_score>=70 and sc.s_score<=80 then 1 else 0 end)/SUM(case when sc.s_score then 1 else 0 end)),2) as 中等率,
ROUND(100*(SUM(case when sc.s_score>=80 and sc.s_score<=90 then 1 else 0 end)/SUM(case when sc.s_score then 1 else 0 end)),2) as 优良率,
ROUND(100*(SUM(case when sc.s_score>=90 then 1 else 0 end)/SUM(case when sc.s_score then 1 else 0 end)),2) as 优秀率
from score sc left join course c on sc.c_id = c.c_id GROUP BY sc.c_id,c.c_name
#----------------------------------------------------------------我写错的示例 ，大家不要模仿
select
sc.c_id,
c.c_name,
max(sc.s_score),
min(sc.s_score),
AVG(sc.s_score) ,
((select count(sc.s_id) from score sc group by sc.c_id where sc.s_score>'60' )/(select count(*) from score group by score.c_id ))as overscore
from score sc
left join course c
on sc.c_id=c.c_id
GROUP BY sc.c_id
select * from
(select count(sc.s_id) from score sc where sc.s_score>'60' )/(select count(*)from score )
# 19. 按各科成绩进行排序，并显示排名（mysql没有rank函数）
select sc.s_id,sc.c_id,
@i:=@i +1 as i保留排名,
@k:=(case when @score=sc.s_score then @k else @i end) as rank不保留排名,
@score:=sc.s_score as score
from (
select s_id,c_id,s_score from score GROUP BY s_id,c_id,s_score ORDER BY s_score DESC
)sc,(select @k:=0,@i:=0,@score:=0)s
#--------------------------------------
(select
*
from (select
t1.c_id,
t1.s_score,
(select count(distinct t2.s_score) from score t2 where t2.s_score>=t1.s_score and t2.c_id='01') rank
FROM score t1
where t1.c_id='01'
order by t1.s_score desc) t1)
union
(select
*
from (
select
t1.c_id,
t1.s_score,
(select count(distinct t2.s_score) from score t2 where t2.s_score>=t1.s_score and t2.c_id='02') rank
FROM score t1
where t1.c_id='02'
order by t1.s_score desc) t2)
union
(select
*
from (
select
t1.c_id,
t1.s_score,
(select count(distinct t2.s_score) from score t2 where t2.s_score>=t1.s_score and t2.c_id='03') rank
FROM score t1
where t1.c_id='03'
order by t1.s_score desc) t3)
# 20. 查询学生的总成绩并进行排名（同学-总成绩：一对多，按一分组，查"多"的聚合函数）
select
sc.s_id,
@i:=@i+1 as i,
@k:=(case when @score=sc.sum_score then @k else @i end) as rank,
@score:=sc.sum_score as score
from (
select
s_id,
SUM(s_score) as sum_score
from score
GROUP BY s_id
ORDER BY sum_score DESC)sc,
(
select
@k:=0,
@i:=0,
@score:=0)s
# 21.查询不同老师所教不同课程平均分从高到低显示 （先弄清对应关系，一个老师对应一门课程，即按课程分组统计课程平均分即可）
select
c.t_id,t.t_name,c.c_id,ROUND(avg(s_score),2) as avg_score
from course c
left join score sc
on c.c_id=sc.c_id
left join teacher t
on c.t_id=t.t_id
GROUP BY c.c_id,c.t_id,t.t_name
ORDER BY avg_score DESC;
# 22. 查询所有课程的成绩第2名到第3名的学生信息及该课程成绩（有点难，我也不会写，用的时候再看看，有个印象即可）
select
s.*,r.排名,r.s_score,r.c_id
from (
select sc.s_id,sc.s_score,sc.c_id,@i:=@i+1 as 排名 from score sc,(select @i:=0)s where sc.c_id='01'
ORDER BY sc.s_score DESC )r
left join student s on r.s_id=s.s_id
where 排名 BETWEEN 2 AND 3
UNION
select
s.*,r.排名,r.s_score,r.c_id
from (
select sc.s_id,sc.s_score,sc.c_id,@j:=@j+1 as 排名
from score sc,(select @j:=0)s where sc.c_id='02'
ORDER BY sc.s_score DESC)r
left join student s on r.s_id=s.s_id
where 排名 BETWEEN 2 AND 3
UNION
select
s.*,r.排名,r.s_score,r.c_id
from (
select
sc.s_id,
sc.s_score,
sc.c_id,@k:=@k+1 as 排名
from score sc,(select @k:=0)s
where sc.c_id='03'
ORDER BY sc.s_score DESC)r
left join student s
on r.s_id=s.s_id
where 排名 BETWEEN 2 AND 3;
# 23. 统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比（把列值单独拿出来展示：三种方案，这里采用第二种casewhen实现条件控制和列转行，然后聚合函数，剩下的关联到主表即可）
select
distinct
f.c_name,
a.c_id,
b.`85-100`,
b.百分比,
c.`70-85`,
c.百分比,
d.`60-70`,
d.百分比,
e.`0-60`,
e.百分比
from score a
left join (
select
c_id,
SUM(case when s_score >85 and s_score <=100 then 1 else 0 end) as `85-100`,
ROUND(100*(SUM(case when s_score >85 and s_score <=100 then 1 else 0 end)/count(*)),2) as 百分比
from score
GROUP BY c_id)b
on a.c_id=b.c_id
left join (
select
c_id,
SUM(case when s_score >70 and s_score <=85 then 1 else 0 end) as `70-85`,
ROUND(100*(SUM(case when s_score >70 and s_score <=85 then 1 else 0 end)/count(*)),2) as 百分比
from score
GROUP BY c_id)c
on a.c_id=c.c_id
left join (
select
c_id,
SUM(case when s_score >60 and s_score <=70 then 1 else 0 end) as `60-70`,
ROUND(100*(SUM(case when s_score >60 and s_score <=70 then 1 else 0 end)/count(*)),2) as 百分比
from score
GROUP BY c_id)d
on a.c_id=d.c_id
left join (
select
c_id,
SUM(case when s_score >=0 and s_score <=60 then 1 else 0 end) as `0-60`,
ROUND(100*(SUM(case when s_score >=0 and s_score <=60 then 1 else 0 end)/count(*)),2) as 百分比
from score GROUP BY c_id)e
on a.c_id=e.c_id
left join course f
on a.c_id = f.c_id
# -- 24、查询学生平均成绩及其名次（添加关联一列，排序使用函数）
select
sc.s_id,
@i:=@i+1 as '不保留空缺排名',
@k:=(case when @avg_score=sc.avg_s then @k else @i end) as '保留空缺排名',
@avg_score:=avg_s as '平均分'
from (
select
s_id,
ROUND(AVG(s_score),2) as avg_s
from score
GROUP BY s_id
ORDER BY avg_s DESC)sc,
(
select
@avg_score:=0,
@i:=0,@k:=0)b;
# 25. 查询各科成绩前三名的记录（将列值拿出来单独展示：三种方案。这里选择第一种，自关联，然后由于自关联所以需要关联过滤，分组聚合条件过滤，参考自联，注意不查关联表。因为自关联的关联字段有重复会产生笛卡尔积有不少错误关联数据需要予以排除）
-- 1.选出b表比a表成绩大的所有组
-- 2.选出比当前id成绩大的 小于三个的
select
sc1.s_id,
sc1.c_id,
sc1.s_score
from score sc1
left join score sc2
on sc1.c_id = sc2.c_id and sc1.s_score<sc2.s_score
group by sc1.s_id,sc1.c_id,sc1.s_score HAVING COUNT(sc2.s_id)<3
ORDER BY sc1.c_id,sc1.s_score DESC
# 26、查询每门课程被选修的学生数（课程对学生数：一对多，以课程c_id分组，统计对应"多"字段聚合函数）
select
c_id,
count(s_id)
from score a
GROUP BY c_id
# 27、查询出只有两门课程的全部学生的学号和姓名 （以学生学号为一，以课程数为多，查课程个数）
select
s_id,
s_name
from student
where s_id in(
select s_id from score GROUP BY s_id HAVING COUNT(c_id)=2);
# 28、查询男生、女生人数（分组查询）
select
s_sex,
COUNT(s_sex) as 人数
from student
GROUP BY s_sex
# 29、查询名字中含有"风"字的学生信息(模糊查询)
select * from student where s_name like '%风%';
# 30、查询同名同性学生名单，并统计同名人数 （以学生姓名为一，查学生s_id个数）
select
s.s_name,
s.s_sex,
count(*)
from student s
JOIN student s1
on s.s_id !=s1.s_id and s.s_name = s1.s_name and s.s_sex = s1.s_sex
GROUP BY s.s_name,s.s_sex
# 31、查询1990年出生的学生名单（模糊查询或精确查询）
select s_name from student where s_birth like '1990%'
#------------------------
select s_name from student where year(s_birth) = '1990'
#------------------------
select s_name from student where DATE_FORMAT(s_birth,"%Y") = "1990"
# 32、 查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列 （以课程c_id为一，成绩s_score为多）
select
c_id,
ROUND(AVG(s_score),2) as avg_score
from score
GROUP BY c_id
ORDER BY avg_score DESC,c_id ASC
# 33、 查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩（以学生学号为一的一方分组）
select
sc.s_id,
s.s_name,
ROUND(avg(sc.s_score),2) as avg_score
from score sc
left join student s
on sc.s_id=s.s_id
GROUP BY s_id HAVING avg_score>=85
# 34、查询课程名称为"数学"，且分数低于60的学生姓名和分数 （分数表关联课程表然后过滤条件即可，也可以不管课程表而是把课程名限制条件改为id限制的子查询）
select
s.s_name,
sc.s_score
from score sc
join student s
on s.s_id=sc.s_id
where sc.c_id=(select c_id from course where c_name ='数学') and sc.s_score<60
#-----------------------------
select
sc.*,s.*,c.*
from score sc
left join student s
on s.s_id=sc.s_id
left join course c
on c.c_id=sc.c_id
where sc.s_score<60 and c.c_name='数学'
# 35、 查询所有学生的课程及分数情况（将列值抽取出来单独作为结果展示：三种方案，这里选用第二种使用casewhen然后聚合函数。很明显课程c_id与学生s_id在score表中具备唯一性，等于一个联合主键具备唯一性，将其作为一分组查询）
select
s.s_id,
s.s_name,
SUM(case c.c_name when '语文' then sc.s_score else 0 end) as '语文',
SUM(case c.c_name when '数学' then sc.s_score else 0 end) as '数学',
SUM(case c.c_name when '英语' then sc.s_score else 0 end) as '英语',
SUM(sc.s_score) as '总分'
from student s left join score sc on s.s_id = sc.s_id
left join course c on sc.c_id = c.c_id
GROUP BY s.s_id,s.s_name
# 36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数；（score全表，然后过滤分数>70的记录，得到s_id-c_id然后只查s_id进行去重）
select
s.s_name,
c.c_name,
sc.s_score
from course c
left join score sc
on c.c_id = sc.c_id
left join student s
on s.s_id=sc.s_id
where sc.s_score>=70
# 37、查询不及格的课程(score全表过滤分数<60的记录，得到s_id-c_id然后只查c_id进行去重)
select
sc.s_id,
sc.c_id,
c.c_name,
sc.s_score
from score sc
left join course c
on sc.c_id = c.c_id
where sc.s_score<60
# 38、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名（score表过滤得到s_id然后关联学生表查询学生姓名）；
select
sc.s_id,
s.s_name,
sc.s_score
from score sc
LEFT JOIN student s
on sc.s_id = s.s_id
where sc.c_id = '01'and sc.s_score>=80
# 39、求每门课程的学生人数
select count(*) from score GROUP BY c_id;
# 40、查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩（score关联techer过滤得到s_id，以c_id分组中找到最高分对应的s_id）
-- 查询老师id
select c_id from course c,teacher t where c.t_id=t.t_id and t.t_name='张三'
-- 查询最高分（可能有相同分数）
select MAX(s_score) from score where c_id='02'
-- 查询信息
select
s.*,
sc.s_score,
sc.c_id,
c.c_name
from student s
LEFT JOIN score sc
on s.s_id = sc.s_id
LEFT JOIN course c
on sc.c_id=c.c_id
where sc.c_id =(
select c_id from course c,teacher t where c.t_id=t.t_id and t.t_name='张三')
and sc.s_score in (
select MAX(s_score) from score where c_id='02')
# 41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩(条件是当前表列值相互比较的，采用自关联)
select
DISTINCT sc2.s_id,sc2.c_id,sc2.s_score
from score sc1,score sc2
where sc1.c_id != sc2.c_id and sc1.s_score = sc2.s_score
# 42、查询每门课成绩最好的前两名 （查具体“多”的列，一方面可以自关联然后限制，一方面可以case when然后聚合，一方面可以子查询，后两者适用列值已知的情况下）
select
sc1.s_id,
sc1.c_id,
sc1.s_score
from score sc1
where (
select
COUNT(1)
from score sc2
where sc2.c_id=sc1.c_id and sc2.s_score>=sc1.s_score
)<=2
ORDER BY sc1.c_id
# ------------------------------------------------
select
DISTINCT
sc1.s_id,
sc1.c_id,
sc1.s_score
from score sc1
left join score sc2
on sc2.c_id=sc1.c_id and sc1.s_score<sc2.s_score
group by sc1.s_id,sc1.c_id,sc1.s_score HAVING COUNT(sc2.s_score)<2
# 43、统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
select
c_id,
count(*) as total
from score
GROUP BY c_id HAVING total>5
ORDER BY total,c_id ASC
# 44、检索至少选修两门课程的学生学号（score以学生为“一”分组，查课程“多”的数目）
select
s_id,
count(*) as sel
from score
GROUP BY s_id HAVING sel>=2
# 45、查询选修了全部课程的学生信息（典型的子查询）
select
*
from student
where s_id in(
select s_id from score GROUP BY s_id HAVING count(*)=(select count(*) from course)
)
# 46、查询各学生的年龄
-- 按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一
select
s_birth,
(DATE_FORMAT(NOW(),'%Y')-DATE_FORMAT(s_birth,'%Y') -
(case when DATE_FORMAT(NOW(),'%m%d') > DATE_FORMAT(s_birth,'%m%d') then 0 else 1 end)) as age
from student;
# 47、查询本周过生日的学生
select
*
from student
where WEEK(DATE_FORMAT(NOW(),'%Y%m%d')) = WEEK(s_birth)
#-------------------------------------------------
select * from student where YEARWEEK(s_birth) = YEARWEEK(DATE_FORMAT(NOW(),'%Y%m%d'))
#-------------------------------------------------
select WEEK(DATE_FORMAT(NOW(),'%Y%m%d'))
# 48、查询下周过生日的学生
select
*
from student
where WEEK(DATE_FORMAT(NOW(),'%Y%m%d'))+1 = WEEK(s_birth)
# 49、查询本月过生日的学生
select
*
from student
where MONTH(DATE_FORMAT(NOW(),'%Y%m%d')) = MONTH(s_birth)
# 50、查询下月过生日的学生
select
*
from student
where MONTH(DATE_FORMAT(NOW(),'%Y%m%d'))+1 = MONTH(s_birth)
# 51、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩(同时显示分组查询的一和多字段，使用子查询，然后关联)
select
r.*,x.avg
from (
select
sc.*
from score sc) as r
join # "inner join on" / " , where " /" left join on " / " "right join on "
(select
sc.s_id,
avg(sc.s_score) as avg
from score sc
group by sc.s_id) as x
on r.s_id=x.s_id #where r.s_id=x.s_id
order by r.s_id
# 52、 score中存在成绩的学生信息
select
*
from student
where student.s_id in (
SELECT DISTINCT sc.s_id from score sc);
# 53、 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null ) 4.1 查有成绩的学生信息
SELECT
sc.s_id,s.s_name,
count(sc.c_id),
sum(sc.s_score)
from score sc
left join student s
on s.s_id=sc.s_id
GROUP BY sc.s_id
# 54、 查询“存在01可能没有02”的情况(不存在时显示为 null )（查询学生拥有01可能没有02时的情况，sid\tid\score）
select
*
from (
select * from score where score.c_id='01')t1
left join (
select * from score where score.c_id='02')t2
on t1.s_id=t2.s_id
# 54、 查询没有" 01 "(not in (=01))课程但有" 02 "课程的学生情况
select
*
from score sc
where sc.s_id not in (
select
sc.s_id
from score sc
where sc.c_id = '01'
)
AND sc.c_id= '02';
# 55、 查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩（student+score）
select s.s_id,s.s_name,AVG(sc.s_score) from score sc left join student s on sc.s_id=s.s_id
group by sc.s_id having AVG(sc.s_score)>60
# 56、查询在分数表存在成绩的学生信息
select s.* from student s ,
( select distinct sc.s_id from score sc where sc.s_score is not null)r
where r.s_id=s.s_id;
select s.* from student s
inner join
( select distinct sc.s_id from score sc where sc.s_score is not null)r
where r.s_id=s.s_id;
select s.* from student s where s.s_id in(select distinct sc.s_id from score sc where sc.s_score is not null);
# 57、 查询所有同学的学生编号、学生姓名、选课总数（count）、所有课程的成绩总和sum(score)不会显示没选课的学生：
select * from
(select sc.s_id,count(sc.c_id),sum(sc.s_score)
from score sc
group by sc.s_id)t,
student s
where t.s_id=s.s_id;
#-------------------------------------------------------
select sc.s_id,s.s_name,s.s_sex,s.s_birth,count(sc.c_id),sum(sc.s_score)
from score sc
left join student s
on sc.s_id=s.s_id
group by sc.s_id
1135
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
