23 MySQL中case when的用法
SELECT c_id, c_name, COUNT(1) AS '总人数', COUNT(CASE WHEN s_score BETWEEN 85 AND 100+1 THEN 1 ELSE NULL END) AS '85-100人数', ROUND(COUNT(CASE WHEN s_score BETWEEN 85 AND 100+1 THEN 1 ELSE NULL END)/COUNT(1), 2) AS '85-100百分比',
COUNT(CASE WHEN s_score BETWEEN 70 AND 85 THEN 1 ELSE NULL END) AS '70-84人数', ROUND(COUNT(CASE WHEN s_score BETWEEN 70 AND 85 THEN 1 ELSE NULL END)/COUNT(1), 2) AS '70-84百分比',
COUNT(CASE WHEN s_score BETWEEN 60 AND 69 THEN 1 ELSE NULL END) AS '60-69人数', ROUND(COUNT(CASE WHEN s_score BETWEEN 60 AND 69 THEN 1 ELSE NULL END)/COUNT(1), 2) AS '60-69百分比',
COUNT(CASE WHEN s_score BETWEEN 0 AND 59 THEN 1 ELSE NULL END) AS '0-59人数', ROUND(COUNT(CASE WHEN s_score BETWEEN 0 AND 59 THEN 1 ELSE NULL END)/COUNT(1), 2) AS '0-59百分比'
FROM course
JOIN score USING(c_id)
GROUP BY c_id, c_name;
33 MySQL中having
SELECT s_id, s_name, ROUND(AVG(s_score), 1) AS avg_score
FROM student
JOIN score USING(s_id)
GROUP BY s_id, s_name
HAVING avg_score>=85;
34 MySQL中多表联结
SELECT c_name, s_name, s_score
FROM student s
JOIN score sc USING(s_id)
JOIN course c USING(c_id)
WHERE c_name='数学' AND s_score<60;
35 MySQL中 列转行
SELECT s_id, s_name,
SUM(CASE WHEN c_name='语文' THEN s_score ELSE 0 END) AS '语文',
SUM(CASE WHEN c_name='数学' THEN s_score ELSE 0 END) AS '数学',
SUM(CASE WHEN c_name='英语' THEN s_score ELSE 0 END) AS '英语'
FROM student
JOIN score USING (s_id)
JOIN course USING (c_id)
GROUP BY s_id, s_name;
SELECT s_id, s_name,
SUM(CASE WHEN c_name='语文' THEN s_score ELSE NULL END) AS '语文',
SUM(CASE WHEN c_name='数学' THEN s_score ELSE NULL END) AS '数学',
SUM(CASE WHEN c_name='英语' THEN s_score ELSE NULL END) AS '英语'
FROM student
JOIN score USING (s_id)
JOIN course USING (c_id)
GROUP BY s_id, s_name;
38 MySQL中where和and用法
SELECT s.s_id, s_name
FROM student s
JOIN score sc
ON s.s_id=sc.s_id
WHERE c_id='01' AND s_score>=80;
39 MySQL中group by 分组用法
SELECT c_name, c.c_id, COUNT(1) AS 人数
FROM course c
LEFT JOIN score s
USING(c_id)
GROUP BY c_name, c.c_id;
40 MySQL中多个子查询
SELECT s.s_id, s_name, s_brith, s_sex, s_score
FROM student s
JOIN score sc USING(s_id)
WHERE (c_id, s_score) IN(
SELECT c_id, MAX(s_score)
FROM score
JOIN course USING(c_id)
JOIN teacher USING(t_id)
WHERE t_name='张三'
GROUP BY c_id
);