本文内容来自MySQL开发技巧（三）

MySQL开发技巧学习笔记三

子查询的使用场景及其好处

什么是子查询？当一个查询是另一个查询的条件时，称之为子查询

常见的子查询使用场景
1.使用子查询可以避免由于子查询中的数据产生的重复
例如，查询出在user1中有谁打过怪

select user_name from user1 where id in (select user_id from user_kills);

其查询结果为：

如果使用连接查询：

select a.user_name from user1 a join user_kills b on a.id = b.user_id;

其查询结果为：

所以在join时，要对结果集去重

select distinct a.user_name from user1 a join user_kills b on a.id = b.user_id;

2.使用子查询更符合语义，更容易理解

如何在子查询中实现多列过滤
例如，查询出每个打怪人打怪最多的的姓名，日期，数量

使用select user_id,max(kills) as cnt from user_kills group by user_id;可以查询出打怪最多的user_id和kills，其结果为：

然后使用join的方式来连接这个子查询，此方式对所有的数据库都适用

select a.user_name,b.timestr,b.kills from user1 a join user_kills b on a.id = b.user_idjoin (    select user_id,max(kills) as cnt from user_kills group by user_id) c on b.user_id = c.user_id and b.kills = c.cnt;

其查询结果为：

同时在MySQL中有一种独特多列过滤方式：

select a.user_name, b.timestr, b.kills from user1 a  join user_kills b on a.id = b.user_idwhere (b.user_id, b.kills ) in (    select user_id,max(kills) as cnt from user_kills group by user_id);

多属性查询

实例场景说明
增加一张表user_skills，列出了取经四人组的技能，如下：

什么是同一属性的多值过滤
如何查询出同时具有变化和念经这两项技能的人？
可以通过两次关联的方式，第一次取出念经，第二次再取出变化，然后关联

select a.user_name, b.skill, c.skillfrom user1 a join user_skills b on a.id = b.user_id and b.skill='念经'join user_skills c on c.user_id=b.user_id and c.skill='变化'where b.skill_level > 0 and c.skill_level > 0;

其执行的结果为：

同样如果查询三种技能

select a.user_name, b.skill, c.skillfrom user1 a join user_skills b on a.id = b.user_id and b.skill='念经'join user_skills c on c.user_id=b.user_id and c.skill='变化'join user_skills d on d.user_id=c.user_id and d.skill='腾云'where b.skill_level > 0 and c.skill_level > 0 and d.skill_level > 0;

如何取出4中技能中至少具有2种的取经人

select a.user_name,b.skill,c.skill,d.skill,e.skillfrom user1 aleft join user_skills b on a.id=b.user_id and b.skill='念经' and b.skill_level > 0left join user_skills c on a.id=c.user_id and c.skill='变化' and c.skill_level > 0left join user_skills d on a.id=d.user_id and d.skill='腾云' and d.skill_level > 0left join user_skills e on a.id=e.user_id and e.skill='浮水' and e.skill_level > 0;

执行结果为：

select a.user_name,b.skill,c.skill,d.skill,e.skillfrom user1 aleft join user_skills b on a.id=b.user_id and b.skill='念经' and b.skill_level > 0left join user_skills c on a.id=c.user_id and c.skill='变化' and c.skill_level > 0left join user_skills d on a.id=d.user_id and d.skill='腾云' and d.skill_level > 0left join user_skills e on a.id=e.user_id and e.skill='浮水' and e.skill_level > 0where (case when b.skill is not null then 1 else 0 end) +(case when c.skill is not null then 1 else 0 end) +(case when d.skill is not null then 1 else 0 end) +(case when e.skill is not null then 1 else 0 end) >= 2;

执行后的结果：

使用Group by 实现多属性查询

select a.user_namefrom user1 a join user_skills b on a.id = b.user_idwhere b.skill in ('念经','变化','腾云','浮水') and b.skill_level > 0group by user_name having count(*) >= 2;

计算累进税

什么累进税？最常见的累进税-个人所得税