本文内容来自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_id
join (
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_id
where (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.skill
from 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.skill
from 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.skill
from user1 a
left join user_skills b on a.id=b.user_id and b.skill='念经' and b.skill_level > 0
left join user_skills c on a.id=c.user_id and c.skill='变化' and c.skill_level > 0
left join user_skills d on a.id=d.user_id and d.skill='腾云' and d.skill_level > 0
left 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.skill
from user1 a
left join user_skills b on a.id=b.user_id and b.skill='念经' and b.skill_level > 0
left join user_skills c on a.id=c.user_id and c.skill='变化' and c.skill_level > 0
left join user_skills d on a.id=d.user_id and d.skill='腾云' and d.skill_level > 0
left join user_skills e on a.id=e.user_id and e.skill='浮水' and e.skill_level > 0
where
(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_name
from user1 a
join user_skills b on a.id = b.user_id
where b.skill in ('念经','变化','腾云','浮水') and b.skill_level > 0
group by user_name having count(*) >= 2;
计算累进税
什么累进税?最常见的累进税-个人所得税