MySQL经典50题

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

-- 1 having 可以跟 别名
-- 2 where理解为行筛选,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
);
  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值