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

转载于 2017-06-26 00:35:00 发布 · 181 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：https://my.oschina.net/u/3430557/blog/1028370

文章标签：

#数据库

本文深入解析了一段复杂的SQL查询语句，该语句通过多个LEFT JOIN操作从'user1'表中选择具有特定技能的用户，并使用CASE WHEN语句计算用户拥有的技能数量，最后筛选出至少具备两项技能的用户。

Select a.user_name,b.skill,c.skill,e.skill From user1 a
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
c.skill_level>0
Left join user1_skill e on a.id = e.user_id and e.skill = '浮水' and
e.skill_level>0
;

use test

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;

转载于:https://my.oschina.net/u/3430557/blog/1028370