MySQL开发技巧（三）

课程地址：https://www.imooc.com/learn/449

第1章 课程介绍

1-1 内容介绍

第2章 如何在子查询中测试两个值

2-1 [MySQL] 子查询的使用场景及其好处

2-2 [MySQL] 如何在子查询中实现多列过滤

select a.user_name,b.timestr,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.kill=c.cnt;

第3章 多属性查询

3-1 实例场景说明

3-2 [MySQL] 什么是同一属性的多值过滤

3-3 [MySQL] 使用关联方式实现多属性查询（一）

3-4 [MySQL] 使用关联方式实现多属性查询（二）

select a.user_name,b.skill,c.skill,d.skill,e.skill
from user1 a
left join user1_skill b on a.id=b.user_id and b.skill='念经' and b.skill_level>0
left join user1_skill c on a.id=c.user_id and c.skill='变化' and c.skill_level>0
left join user1_skill d on a.id=d.user_id and d.skill='腾云' and d.skill_level>0
left join user1_skill 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;

3-5 [MySQL] 使用Group by 实现多属性查询

第4章 如何计算累进税类问题

4-1 [MySQL] 计算累进税需求分析

4-2 [MySQL] 如何计算累进税

select a.user_name,money,low,high,rate
from user1 a join taxRate b on a.money>b.low
order by user_name;

select a.user_name,money,low,high,
least(monwy-low,high-low) as curmoney
,rate
from user1 a join taxRate b on a.money>b.low
order by user_name,low;