本文中的涉及到的表在https://github.com/YangBaohust/my_sql中
本文衔接Mysql - 巧用join来优化sql(https://www.cnblogs.com/ddzj01/p/11346954.html)
1. 行转列
例子：找出取经组中每人打怪的总数，并一行显示
原始数据：
+-----------+-------+ | user_name | kills | +-----------+-------+ | 孙悟空 | 10 | | 孙悟空 | 2 | | 孙悟空 | 12 | | 孙悟空 | 22 | | 猪八戒 | 20 | | 猪八戒 | 17 | | 猪八戒 | 35 | | 沙僧 | 3 | | 沙僧 | 9 | | 沙僧 | 5 | +-----------+-------+
想要的数据：
+-----------+-----------+--------+ | 孙悟空 | 猪八戒 | 沙僧 | +-----------+-----------+--------+ | 46 | 72 | 17 | +-----------+-----------+--------+
sql如下：
select sum(case when user_name='孙悟空' then kills end) '孙悟空',
sum(case when user_name='猪八戒' then kills end) '猪八戒',
sum(case when user_name='沙僧' then kills end) '沙僧'
from user1_kills;
2. 使用子查询避免重复数据
例子：找出取经组中有打怪记录的人
取经组user1
+----+-----------+-----------------------------+---------------------------------+ | id | user_name | comment | mobile | +----+-----------+-----------------------------+---------------------------------+ | 1 | 唐僧 | 旃檀功德佛 | 138245623,021-382349 | | 2 | 孙悟空 | 此人在悟空的朋友圈 | 159384292,022-483432,+86-392432 | | 3 | 猪八戒 | 净坛使者 | 183208243,055-8234234 | | 4 | 沙僧 | 金身罗汉 | 293842295,098-2383429 | | 5 | NULL | 白龙马 | 993267899 | +----+-----------+-----------------------------+---------------------------------+
打怪记录user1_kills
+----+-----------+---------------------+-------+ | id | user_name | timestr | kills | +----+-----------+---------------------+-------+ | 1 | 孙悟空 | 2013-01-10 00:00:00 | 10 | | 2 | 孙悟空 | 2013-02-01 00:00:00 | 2 | | 3 | 孙悟空 | 2013-02-05 00:00:00 | 12 | | 4 | 孙悟空 | 2013-02-12 00:00:00 | 22 | | 5 | 猪八戒 | 2013-01-11 00:00:00 | 20 | | 6 | 猪八戒 | 2013-02-07 00:00:00 | 17 | | 7 | 猪八戒 | 2013-02-08 00:00:00 | 35 | | 8 | 沙僧 | 2013-01-10 00:00:00 | 3 | | 9 | 沙僧 | 2013-01-22 00:00:00 | 9 | | 10 | 沙僧 | 2013-02-11 00:00:00 | 5 | +----+-----------+---------------------+-------+
join写法：
select a.id, a.user_name from user1 a join user1_kills b on (a.user_name = b.user_name);
+----+-----------+ | id | user_name | +----+-----------+ | 2 | 孙悟空 | | 2 | 孙悟空 | | 2 | 孙悟空 | | 2 | 孙悟空 | | 3 | 猪八戒 | | 3 | 猪八戒 | | 3 | 猪八戒 | | 4 | 沙僧 | | 4 | 沙僧 | | 4 | 沙僧 | +----+-----------+
可以看到通过join写法，会存在一些重复记录，此时需要在sql语句中加distinct，即select distinct a.id...
in写法：
select user_name from user1 where user_name in (select user_name from user1_kills2);
+-----------+ | user_name | +-----------+ | 孙悟空 | | 猪八戒 | | 沙僧 | +-----------+
因此不同的写法如果存在结果集数量不一致的情况，可以看看是否有重复数据
3. 多列过滤
例子：查询出取经组中每人打怪最多的日期
user1_kills表，取经路上杀的妖怪数量
+----+-----------+---------------------+-------+ | id | user_name | timestr | kills | +----+-----------+---------------------+-------+ | 1 | 孙悟空 | 2013-01-10 00:00:00 | 10 | | 2 | 孙悟空 | 2013-02-01 00:00:00 | 2 | | 3 | 孙悟空 | 2013-02-05 00:00:00 | 12 | | 4 | 孙悟空 | 2013-02-12 00:00:00 | 22 | | 5 | 猪八戒 | 2013-01-11 00:00:00 | 20 | | 6 | 猪八戒 | 2013-02-07 00:00:00 | 17 | | 7 | 猪八戒 | 2013-02-08 00:00:00 | 35 | | 8 | 沙僧 | 2013-01-10 00:00:00 | 3 | | 9 | 沙僧 | 2013-01-22 00:00:00 | 9 | | 10 | 沙僧 | 2013-02-11 00:00:00 | 5 | +----+-----------+---------------------+-------+
在我的另一篇文章https://www.cnblogs.com/ddzj01/p/11346954.html中提到了两种sql写法
聚合子查询写法：
select * from user1_kills a where a.kills = (select max(b.kills) from user1_kills b where b.user_name = a.user_name);
join写法：
select a.* from user1_kills a join user1_kills b on (a.user_name = b.user_name) group by a.id, a.user_name, a.timestr, a.kills having a.kills = max(b.kills);
这里介绍另一种写法：
select * from user1_kills a where (a.user_name, a.kills) in (select b.user_name, max(b.kills) from user1_kills b group by b.user_name);
+----+-----------+---------------------+-------+ | id | user_name | timestr | kills | +----+-----------+---------------------+-------+ | 4 | 孙悟空 | 2013-02-12 00:00:00 | 22 | | 7 | 猪八戒 | 2013-02-08 00:00:00 | 35 | | 9 | 沙僧 | 2013-01-22 00:00:00 | 9 | +----+-----------+---------------------+-------+
4. 计算个人所得税
salary表
+----+-----------+-----------------+-------+ | id | user_name | comment | money | +----+-----------+-----------------+-------+ | 1 | 唐僧 | 旃檀功德佛 | 35000 | | 2 | 孙悟空 | 斗战胜佛 | 28000 | | 3 | 猪八戒 | 净坛使者 | 15000 | | 4 | 沙僧 | 金身罗汉 | 8000 | +----+-----------+-----------------+-------+
tax表
+----+-------+---------+------+ | id | low | high | rate | +----+-------+---------+------+ | 1 | 0 | 1500 | 0.03 | | 2 | 1500 | 4500 | 0.10 | | 3 | 4500 | 9000 | 0.20 | | 4 | 9000 | 35000 | 0.25 | | 5 | 35000 | 55000 | 0.30 | | 6 | 55000 | 1000000 | 0.35 | +----+-------+---------+------+
例子：算出取经组中每人的个人所得税
计算个人所得税，属于经典题目了，下面看看用sql如何实现
首先，直接对两表进行笛卡尔连接，为了节约篇幅，只取唐僧的数据查看
select * from salary a join tax b order by user_name, low;
+----+-----------+-----------------+-------+----+-------+---------+------+ | id | user_name | comment | money | id | low | high | rate | +----+-----------+-----------------+-------+----+-------+---------+------+ | 1 | 唐僧 | 旃檀功德佛 | 35000 | 1 | 0 | 1500 | 0.03 | | 1 | 唐僧 | 旃檀功德佛 | 35000 | 2 | 1500 | 4500 | 0.10 | | 1 | 唐僧 | 旃檀功德佛 | 35000 | 3 | 4500 | 9000 | 0.20 | | 1 | 唐僧 | 旃檀功德佛 | 35000 | 4 | 9000 | 35000 | 0.25 | | 1 | 唐僧 | 旃檀功德佛 | 35000 | 5 | 35000 | 55000 | 0.30 | | 1 | 唐僧 | 旃檀功德佛 | 35000 | 6 | 55000 | 1000000 | 0.35 | +----+-----------+-----------------+-------+----+-------+---------+------+
可以看到最后两条数据不需要，因为它的low都大于等于money，所以可以加上连接条件a.money > b.low，排除不需要的行
select * from salary a join tax b on (a.money > b.low) order by user_name, low;
+----+-----------+-----------------+-------+----+------+-------+------+ | id | user_name | comment | money | id | low | high | rate | +----+-----------+-----------------+-------+----+------+-------+------+ | 1 | 唐僧 | 旃檀功德佛 | 35000 | 1 | 0 | 1500 | 0.03 | | 1 | 唐僧 | 旃檀功德佛 | 35000 | 2 | 1500 | 4500 | 0.10 | | 1 | 唐僧 | 旃檀功德佛 | 35000 | 3 | 4500 | 9000 | 0.20 | | 1 | 唐僧 | 旃檀功德佛 | 35000 | 4 | 9000 | 35000 | 0.25 | +----+-----------+-----------------+-------+----+------+-------+------+
接下来只需要计算每一行的税费即可，计算的标准则为least(money, high) - low，因此sql如下
select a.user_name, sum((least(a.money, b.high) - b.low) * b.rate) total from salary a join tax b on (a.money > b.low) group by a.user_name;
+-----------+---------+ | user_name | total | +-----------+---------+ | 唐僧 | 7745.00 | | 孙悟空 | 5995.00 | | 沙僧 | 1045.00 | | 猪八戒 | 2745.00 | +-----------+---------+
不管是开发人员还是dba，都应该熟练掌握sql技巧，本文例子取自于慕课网《sql开发技巧》。如果文章对你有帮助，不妨点个赞，支持下博主。