Mysql - 开发技巧(二)
本文中的涉及到的表在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开发技巧》。如果文章对你有帮助，不妨点个赞，支持下博主。
最新文章
- Cross-Origin Request Blocked: The Same Origin Policy disallows reading the remote resource at http://localhost:9001/api/size/get. (Reason: CORS header 'Access-Control-Allow-Origin' missing).
- clob型不能用 distinct，以及转换clob类型方法
- 不可或缺 Windows Native (12) - C++: 引用类型
- 深入理解memcached
- [二]JQueryMobile常用的组件介绍
- <一> jQuery 简单介绍
- SQL Server数据类型
- 简单说说NoHTTP
- AntData.ORM框架 之 读写分离
- Zookeeper 启蒙
- jQ append 添加html 及字符串拼接
- Eclipse 各版本号
- C#对windows的IP网络测试(ping ip)
- NBU 还原LINUX ORACLE RAC数据库(MIDDB)
- Daily Scrumming* 2015.12.17（Day 9）
- AI单挑Dota 2世界冠军：被电脑虐哭……
- codevs 2010 求后序遍历
- kafka 怎么保证的exactly once
- 采用dlopen、dlsym、dlclose加载动态链接库
- CodeForces 347A Difference Row (水题)