sql的练习 - 一期 ：2019/03/26

1.针对 LEFT JOIN 的基本练习

第一题

第一次优化：

SELECT 
teach_id, 
sum(CASE WHEN day_name=1 THEN 1 ELSE 0 END) AS '星期一',
sum(CASE WHEN day_name=2 THEN 1 ELSE 0 END) AS '星期二',
sum(CASE WHEN day_name=3 THEN 1 ELSE 0 END) AS '星期三'
FROM `teach_class`
GROUP BY teach_id;    

----时间上消耗更少，控制力度更大，语句也更加简单

 

第二题：

目的：每个部门的所写的总书两

//先将每个作者写的书统计出来 表A
SELECT author_id, count(*) AS num FROM book_map GROUP BY author_id;

//将表A left Join入部门的关系表：
SELECT a.dp_id,a.author_id,b.num
FROM dept_map AS a
LEFT JOIN (SELECT author_id, count(*) AS num FROM book_map GROUP BY author_id) AS b 
ON a.author_id = b.author_id;

//统计出每个部门出版了多少书籍
SELECT c.deptId , sum(bookNum) bkNum
FROM(
SELECT a.dp_id AS deptId ,a.author_id,b.num AS bookNum
FROM dept_map AS a
LEFT JOIN (SELECT author_id, count(*) AS num FROM book_map GROUP BY author_id) AS b 
ON a.author_id = b.author_id ) AS c
GROUP BY c.deptId;

//left JOIN 合并到部门信息表：输出不懂程序的人也懂的白话文
SELECT c.dp_name AS '编辑部Name' ,d.bkNum AS '出版书籍'
FROM depts AS c
LEFT JOIN (
SELECT c.deptId , sum(bookNum) bkNum
FROM(
SELECT a.dp_id AS deptId ,a.author_id,b.num AS bookNum
FROM dept_map AS a
LEFT JOIN (SELECT author_id, count(*) AS num FROM book_map GROUP BY author_id) AS b 
ON a.author_id = b.author_id ) AS c
GROUP BY c.deptId) AS d 
ON c.dp_id=d.deptId;

第三题：

 

第四题：

 

第一小题： 将国家按州划分统计人口---

SELECT 
(CASE 
WHEN country='中国' THEN '亚洲'
WHEN country='美国' THEN '北美洲'
WHEN country='加拿大' THEN '北美洲'
WHEN country='英国' THEN '欧洲'
WHEN country='法国' THEN '欧洲'
WHEN country='日本' THEN '亚洲'
WHEN country='德国' THEN '欧洲'
WHEN country='墨西哥' THEN '北美洲'
WHEN country='印度' THEN '亚洲'
ELSE '其他' END) AS '州' , SUM(population) AS '人口'
FROM peop_country
GROUP BY CASE country 
WHEN '中国' THEN '亚洲'
WHEN '美国' THEN '北美洲'
WHEN '加拿大' THEN '北美洲'
WHEN '英国' THEN '欧洲'
WHEN '法国' THEN '欧洲'
WHEN '日本' THEN '亚洲'
WHEN '德国' THEN '欧洲'
WHEN '墨西哥' THEN '北美洲'
WHEN '印度' THEN '亚洲'
ELSE '其他' END;

第二小题，不同国家，难于人口的横向对比

SELECT 
country,
SUM(CASE WHEN sex=1 THEN population ELSE 0 END) AS '男',
SUM(CASE WHEN sex=2 THEN population ELSE 0 END) AS '女'
FROM 
peop_country
GROUP BY country;

第三小题： 每个州的男女比例情况--上面2种一结合 so easy ，但最好是自己脑子里过一遍

 

 

 

mysql