mysql 50题全面解法

花了断断续续两周左右的时间将50题做完了，自己添加了很多不同的解法。个人强烈推荐第19题和22题的解法；

题目和题目顺序参见：https://mp.weixin.qq.com/s/79l09EPtZpLk5XZSLnQNLw

我自己的解法：有些题目和链接中的做法不相同，个人觉得自己的方法要直观一些。

第一题：
SELECT st.*,s1.s_score as 01_score ,s2.s_score as 02_score FROM student as st,score as s1,score as s2 WHERE s1.s_id = s2.s_id AND st.s_id = s1.s_id AND s1.c_id = 01 AND s2.c_id = 02 AND s1.s_score < s2.s_score;

第三题：
SELECT st.s_id,st.s_name,ROUND(AVG(s.s_score),2) AS avg_score FROM student AS st,score AS s WHERE st.s_id = s.s_id GROUP BY st.s_id HAVING avg_score >= 60;

第四题：
SELECT st.s_id,st.s_name,ROUND(AVG(s.s_score),2) AS avg_score FROM student AS st,score AS s WHERE st.s_id = s.s_id GROUP BY st.s_id HAVING avg_score < 60 UNION SELECT st.s_id,st.s_name,'无成绩' FROM student AS st WHERE st.s_id NOT IN (SELECT DISTINCT(s_id) FROM score);

第五题：
SELECT st.s_id,st.s_name,COUNT(s.c_id) AS course_num,SUM(s.s_score) AS total_score FROM student AS st,score AS s WHERE st.s_id = s.s_id GROUP BY st.s_id;

第六题：
SELECT COUNT(t_name) FROM teacher WHERE t_name LIKE "李%";

第七题：
SELECT st.* FROM student AS st JOIN score AS s ON st.s_id = s.s_id JOIN course AS c ON s.c_id = c.c_id JOIN teacher AS t ON c.t_id = t.t_id AND t.t_name = '张三';

第八题：
SELECT * FROM student WHERE s_id NOT IN (SELECT st.s_id FROM student AS st,score AS s ,course AS c ,teacher AS t WHERE st.s_id  = s.s_id AND s.c_id = c.c_id AND c.t_id = t.t_id AND t.t_name = '张三');

第九题：解法1：

SELECT * FROM student WHERE s_id IN (SELECT s_id FROM score WHERE c_id = 01 OR c_id = 02 GROUP BY s_id HAVING COUNT(c_id) =2 );

第九题：解法2
select st.* FROM student AS st, score AS s1,score AS s2 WHERE st.s_id = s1.s_id AND s1.s_id = s2.s_id AND s1.c_id = 01 AND s2.c_id = 02; 

第十题：
SELECT * FROM student WHERE s_id IN (SELECT s_id FROM score WHERE c_id = '01' AND s_id NOT IN (SELECT s_id FROM score WHERE c_id = '02'));

第十一题：
SELECT * FROM student WHERE s_id NOT IN (SELECT s_id FROM score GROUP BY s_id HAVING COUNT(c_id) = (SELECT COUNT(DISTINCT(c_id)) FROM course));

第十二题：
SELECT * FROM student WHERE s_id IN (SELECT DISTINCT(s_id) FROM score WHERE c_id IN (SELECT c_id FROM score WHERE s_id = 01));

第十三题：
SELECT * FROM student WHERE s_id IN (SELECT DISTINCT(s2.s_id) FROM score AS s1,score AS s2 WHERE s1.s_id = 01 AND s2.s_id <> 01 AND s1.c_id = s2.c_id GROUP BY s2.s_id HAVING COUNT(s2.c_id) = (SELECT COUNT(DISTINCT(c_id)) FROM score WHERE s_id = 01));

第十四题：
SELECT * FROM student WHERE s_id NOT IN (SELECT DISTINCT(s_id) FROM score WHERE c_id IN (SELECT c.c_id FROM course AS c,teacher AS t WHERE c.t_id = t.t_id AND t.t_name = '张三';

第十五题：
SELECT st.s_name,st.s_id,avg(s.s_score) FROM student AS st, score AS s WHERE st.s_id IN (SELECT s_id FROM score WHERE s_score < 60 GROUP BY s_id HAVING count(c_id) >=2) GROUP BY st.s_id;

第十六题：
SELECT st.* FROM student AS st,score AS s WHERE st.s_id = s.s_id AND s.s_score < 60 AND s.c_id = 01 ORDER BY s.s_score DESC;

第十七题：
解法1
SELECT s1.s_id,(SELECT s_score FROM score AS s2 WHERE s2.s_id = s1.s_id AND s2.c_id = 01) AS 语文,
(SELECT s_score FROM score AS s2 WHERE s2.s_id = s1.s_id AND s2.c_id = 02) AS 数学,
(SELECT s_score FROM score AS s2 WHERE s2.s_id = s1.s_id AND s2.c_id = 03) AS 英语,avg(s_score) AS '平均分' FROM score AS s1 GROUP BY s1.s_id ORDER BY 平均分 DESC;

解法2：
SELECT st.s_id,(SELECT s_score FROM score AS s2 WHERE s2.s_id = st.s_id AND s2.c_id = 01) AS 语文,
(SELECT s_score FROM score AS s2 WHERE s2.s_id = st.s_id AND s2.c_id = 02) AS 数学,
(SELECT s_score FROM score AS s2 WHERE s2.s_id = st.s_id AND s2.c_id = 03) AS 英语,(SELECT avg(s_score) FROM score AS s2 WHERE s2.s_id = st.s_id) AS avg_score FROM student AS st ORDER BY avg_score DESC;

第十八题：
解法1：
SELECT DISTINCT c.c_id,c.c_name,
(SELECT max(s_score) FROM score AS s1 WHERE s1.c_id = c.c_id) AS 最高分,
(SELECT min(s_score) FROM score AS s1 WHERE s1.c_id = c.c_id) AS 最低分,
(SELECT avg(s_score) FROM score AS s1 WHERE s1.c_id = c.c_id) AS 平均分, 
((SELECT count(*) FROM score AS s1 WHERE s1.c_id = c.c_id AND s1.s_score >= 60) / (SELECT count(*) FROM score AS s1 WHERE s1.c_id = c.c_id)) AS 及格率, 
((SELECT count(*) FROM score AS s1 WHERE s1.c_id = c.c_id AND s1.s_score BETWEEN 60 AND 70) / (SELECT count(*) FROM score AS s1 WHERE s1.c_id = c.c_id)) AS 中等率 , 
((SELECT count(*) FROM score AS s1 WHERE s1.c_id = c.c_id AND s1.s_score BETWEEN 80 AND 90) / (SELECT count(*) FROM score AS s1 WHERE s1.c_id = c.c_id)) AS 优良率 , 
((SELECT count(*) FROM score AS s1 WHERE s1.c_id = c.c_id AND s1.s_score >= 90) / (SELECT count(*) FROM score AS s1 WHERE s1.c_id = c.c_id)) AS 优秀率 
FROM course AS c;

解法2:
select a.c_id,b.c_name,MAX(s_score),MIN(s_score),ROUND(AVG(s_score),2),

ROUND(100*(SUM(case when a.s_score>=60 then 1 else 0 end)/SUM(case when a.s_score then 1 else 0 end)),2) as 及格率,

ROUND(100*(SUM(case when a.s_score>=70 and a.s_score<=80 then 1 else 0 end)/SUM(case when a.s_score then 1 else 0 end)),2) as 中等率,

ROUND(100*(SUM(case when a.s_score>=80 and a.s_score<=90 then 1 else 0 end)/SUM(case when a.s_score then 1 else 0 end)),2) as 优良率,

ROUND(100*(SUM(case when a.s_score>=90 then 1 else 0 end)/SUM(case when a.s_score then 1 else 0 end)),2) as 优秀率

from score a left join course b on a.c_id = b.c_id GROUP BY a.c_id,b.c_name

第十九题：
SELECT s1.c_id,s1.s_id,s1.s_score ,(SELECT count(*) FROM score AS s2 WHERE s2.c_id = s1.c_id AND s1.s_score < s2.s_score) + 1 AS num FROM score AS s1 ORDER BY c_id ASC,s_score DESC;

第二十题：
##注意排名的字段名不能使用rank或者其大小写形式，否则会出错。
SELECT s_id,sum_score, CASE 
WHEN @prank = sum_score THEN @crank
WHEN @prank := sum_score  THEN @crank := @crank + 1 
END AS 排名
FROM (SELECT s_id,sum(s_score) AS sum_score FROM score GROUP BY s_id ORDER BY sum_score DESC) AS sum_c ,(SELECT @crank := 0,@prank := NULL) AS r ;

第二十一题：
SELECT t.t_name,t.t_id,c.c_id,avg(sc.s_score) AS avg_score FROM score AS sc LEFT JOIN course AS c ON sc.c_id = c.c_id LEFT JOIN teacher AS t ON c.t_id = t.t_id GROUP BY t.t_id,t.t_name,sc.c_id ORDER BY avg_score DESC;

第二十二题：
#因为表中存在相同分数的情况即并列排名的亲狂，所以使用distinct方法过滤字段，可以得到准确的第几名的数据
SELECT sc.c_id,st.*,sc.s_score FROM score AS sc,student AS st WHERE st.s_id = sc.s_id AND (SELECT count(DISTINCT s_score) FROM score AS sc2 WHERE sc2.c_id = sc.c_id AND sc.s_score < sc2.s_score) >=1 AND (SELECT count(DISTINCT s_score) FROM score AS sc2 WHERE sc2.c_id = sc.c_id AND sc.s_score < sc2.s_score) <= 2 ORDER BY sc.c_id ASC,sc.s_score DESC;

第二十三题：
SELECT c.c_id,c.c_name,
ROUND(((SELECT count(*) FROM score AS sc1 WHERE sc1.c_id = c.c_id AND sc1.s_score > 85 AND sc1.s_score <= 100 )/(SELECT count(*) FROM score AS sc2 WHERE sc2.c_id = c.c_id)),2) AS 优秀百分比, 
ROUND(((SELECT count(*) FROM score AS sc1 WHERE sc1.c_id = c.c_id AND sc1.s_score > 70 AND sc1.s_score <= 85 )/(SELECT count(*) FROM score AS sc2 WHERE sc2.c_id = c.c_id)),2) AS 良好百分比,
ROUND(((SELECT count(*) FROM score AS sc1 WHERE sc1.c_id = c.c_id AND sc1.s_score > 60 AND sc1.s_score <= 70 )/(SELECT count(*) FROM score AS sc2 WHERE sc2.c_id = c.c_id)),2) AS 中等百分比,
ROUND(((SELECT count(*) FROM score AS sc1 WHERE sc1.c_id = c.c_id AND sc1.s_score <= 60 )/(SELECT count(*) FROM score AS sc2 WHERE sc2.c_id = c.c_id)),2) AS 不及格百分比 
FROM course AS c;

第二十四题：
#和第20题类似：
SELECT s_id,avg_score, CASE 
WHEN @prank = avg_score THEN @crank
WHEN @prank := avg_score  THEN @crank := @crank + 1 
END AS 排名
FROM (SELECT s_id,avg(s_score) AS avg_score FROM score GROUP BY s_id) sum_c ,(SELECT @crank := 0,@prank := NULL) r ORDER BY avg_score DESC;

第二十五题：

解法1：
SELECT * FROM score AS s1 WHERE (SELECT count(*) FROM score AS s2 WHERE s1.c_id = s2.c_id AND s1.s_score < s2.s_score) < 3  ORDER BY s1.c_id,s1.s_score DESC;

SELECT s1.c_id,s1.s_id,s1.s_score FROM score AS s1 GROUP BY s1.c_id,s1.s_id HAVING (SELECT COUNT(*) FROM score AS s2 WHERE s2.c_id = s1.c_id AND s1.s_score < s2.s_score) <3 ORDER BY s1.c_id,s1.s_score DESC;

解法2：

select a.s_id,a.c_id,a.s_score from score a left join score b on a.c_id = b.c_id and a.s_score<b.s_score group by a.s_id,a.c_id,a.s_score HAVING COUNT(b.s_id)<3 ORDER BY a.c_id,a.s_score DESC;

第二十六题：
SELECT c_id ,count(s_id) AS num FROM score GROUP BY c_id;

第二十七题：
SELECT s.s_id,s_name FROM score AS s,student AS st WHERE s.s_id = st.s_id GROUP BY st.s_id HAVING count(c_id) = 2;

第二十八题：
SELECT s_sex,count(s_id) AS num FROM student GROUP BY s_sex;

第二十九题：
SELECT * FROM student WHERE s_name LIKE "%风%";

第三十题：
SELECT st1.s_name ,count(st1.s_name) AS num FROM student AS st1,student AS st2 WHERE st1.s_name = st2.s_name AND st1.s_id <> st2.s_id AND st1.s_sex = st2.s_sex;

第三十一题：
SELECT * FROM student WHERE YEAR(s_birth) = 1990;

第三十二题：
SELECT c_id,avg(s_score) AS avg_score FROM score GROUP BY c_id ORDER BY avg_score DESC,c_id ASC;

第三十三题：
SELECT st.s_id,st.s_name,avg(s_score) AS avg_score FROM score AS s,student AS st WHERE s.s_id = st.s_id GROUP BY st.s_id HAVING avg_score >= 85;

第三十四题：
SELECT s_name,s_score FROM student AS st, score AS s WHERE st.s_id=  s.s_id AND s.s_score < 60 AND s.c_id = (SELECT c_id  FROM course WHERE  c_name = "数学");

第三十五题：
解法1：利用关联子查询：
SELECT st.s_id,st.s_name,(SELECT s_score FROM score AS s WHERE s.s_id = st.s_id AND s.c_id = (SELECT c_id FROM course WHERE c_name = '语文')) AS 语文,(SELECT s_score FROM score AS s WHERE s.s_id = st.s_id AND s.c_id = (SELECT c_id FROM course WHERE c_name = '数学')) AS 数学,(SELECT s_score FROM score AS s WHERE s.s_id = st.s_id AND s.c_id = (SELECT c_id FROM course WHERE c_name = '英语')) AS 英语,sum(s_score) AS 总分 FROM student AS st LEFT JOIN score AS sc ON st.s_id = sc.s_id GROUP BY st.s_id;

解法2：使用case when参数；
select a.s_id,a.s_name,

SUM(case c.c_name when '语文' then b.s_score else 0 end) as '语文',

SUM(case c.c_name when '数学' then b.s_score else 0 end) as '数学',

SUM(case c.c_name when '英语' then b.s_score else 0 end) as '英语',

SUM(b.s_score) as  '总分'

from student a left join score b on a.s_id = b.s_id 

left join course c on b.c_id = c.c_id 

GROUP BY a.s_id,a.s_name;

第三十六题：
SELECT st.s_name,c.c_id,sc.s_score FROM student AS st,score AS sc ,course AS c WHERE st.s_id  = sc.s_id AND sc.c_id = c.c_id AND sc.s_score >= 70 ORDER BY st.s_name;

第三十七题：
SELECT st.s_name,sc.c_id,c.c_name,sc.s_score FROM student AS st LEFT JOIN score AS sc ON st.s_id = sc.s_id LEFT JOIN course AS c ON sc.c_id = c.c_id WHERE sc.s_score < 60;

第三十八题：
SELECT s_id,s_name FROM student WHERE s_id IN (SELECT s_id FROM score WHERE c_id = 01 AND s_score > 80);

第三十九题：
SELECT c_id ,count(s_id) AS num FROM score GROUP BY c_id;

第四十题：
解法1：对初次查询的结果创建虚拟表，然后与其他数据表结合进行筛选。
SELECT st.*,sc.c_id,sc.s_score FROM student AS st ,score AS sc,(SELECT max(s_score) AS max_score,c_id FROM score WHERE c_id IN (SELECT c_id FROM course WHERE t_id IN (SELECT t_id FROM teacher AS t WHERE t_name = "张三"))) AS tt WHERE st.s_id = sc.s_id AND sc.s_score = tt.max_score and sc.c_id = tt.c_id; 

解法2：
select a.*,b.s_score,b.c_id,c.c_name from student a

LEFT JOIN score b on a.s_id = b.s_id

LEFT JOIN course c on b.c_id=c.c_id

where b.c_id =(select c_id from course c,teacher d where c.t_id=d.t_id and d.t_name='张三')

and b.s_score in (select MAX(s_score) from score where c_id='02');

第四十一题：
SELECT DISTINCT s1.s_id,s1.c_id,s1.s_score FROM score AS s1,score AS s2 WHERE  s1.c_id <> s2.c_id AND s1.s_score = s2.s_score;

select DISTINCT b.s_id,b.c_id,b.s_score from score a,score b where a.c_id != b.c_id and a.s_score = b.s_score;

第四十二题：(和第25题相同的解法）
解法1： left join 连接
SELECT s1.s_id,s1.c_id,s1.s_score FROM score AS s1 LEFT JOIN score AS s2 ON s1.c_id = s2.c_id AND s1.s_score < s2.s_score GROUP BY s1.c_id,s1.s_id HAVING count(s1.s_id) < 2 ORDER BY s1.c_id ASC,s1.s_score DESC;

解法2：
SELECT * FROM score AS s1 WHERE (SELECT count(s_id) FROM score AS s2 WHERE s2.c_id = s1.c_id AND s1.s_score < s2.s_score) < 2 ORDER BY s1.c_id ASC ,s1.s_score DESC;

第四十三题：
SELECT c_id,count(s_id) AS num FROM score GROUP BY c_id HAVING num > 5 ORDER BY num DESC,c_id ASC;

第四十四题：
SELECT s_id FROM score GROUP BY s_id HAVING count(c_id) >= 2;

第四十五题：
SELECT * FROM student WHERE s_id IN (SELECT s_id FROM score GROUP BY s_id HAVING count(c_id) = (SELECT count(DISTINCT c_id) FROM course));

第四十六题：
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

第四十七题：

SELECT s_id,s_name FROM student WHERE s_birth BETWEEN DATE_SUB(CURRENT_DATE,INTERVAL WEEKDAY(NOW()) DAY) AND DATE_ADD(CURRENT_DATE,INTERVAL (6 - WEEKDAY(NOW())) DAY)

select * from student where WEEK(DATE_FORMAT(NOW(),'%Y%m%d'))=WEEK(s_birth)

第四十八题：
SELECT * FROM student WHERE WEEK(NOW()) + 1 = WEEK(s_birth)

第四十九题：
SELECT * FROM student WHERE MONTH(NOW()) = MONTH(s_birth)

第五十题：
SELECT * FROM student WHERE MONTH(NOW()) + 1 = MONTH(s_birth);