题目:用户表a, 用户标签表b, 现在要达成多对的关系, 还需要做什么? 查出每个用户标签下男女分别多少人?


解析: 已知users   tags   ; 新建一个中间表user_tag; 可以想象成爱好

// users

uid name    sex
1   aaa      1
2   bbb      1
3   ccc      2
4   ddd      1
5   e        2

// tags

tid name
1   睡觉
2   看书
3   听歌
4   游戏
5   跑步

// user_tag

id  uid tid
1   1   1
2   1   2
3   1   5
4   2   2
5   2   3
6   3   1
7   3   2
8   3   5
11  4   1
12  4   5


一步步的sql分析

1 以user_tag为主体, 联表查询tags 出内容

select user_tag.id,user_tag.uid,user_tag.tid,tags.name as tag_name from user_tag left join tags on user_tag.tid=tags.tid
id  uid tid tag_name
1   1   1   睡觉
2   1   2   看书
3   1   5   跑步
4   2   2   看书
5   2   3   听歌
6   3   1   睡觉
7   3   2   看书
8   3   5   跑步
11  4   1   睡觉
12  4   5   跑步


2 把上面的当做以个整体, 联表查询users; 使3个表合成一个表

select a.id,a.uid,a.tid,a.tag_name,users.name,users.sex from (select user_tag.id,user_tag.uid,user_tag.tid,tags.name as tag_name from user_tag left join tags on user_tag.tid=tags.tid) as a  left join users  on a.uid=users.uid
id  uid tid tag_name    name    sex
1   1   1   睡觉         aaa     1
2   1   2   看书         aaa     1
3   1   5   跑步         aaa     1
4   2   2   看书         bbb     1
5   2   3   听歌         bbb     1
6   3   1   睡觉         ccc     2
7   3   2   看书         ccc     2
8   3   5   跑步         ccc     2
11  4   1   睡觉         ddd     1
12  4   5   跑步         ddd     1


3 根据tag分组, 分别计算男女的和

sum(case when sex='1' then 1 else 0 end) as 男生数,
sum(case when sex='2' then 1 else 0 end) as 女生数


最终sql语句如下:

select tid,tag_name,sum(case when sex='1' then 1 else 0 end) as 男生数,
sum(case when sex='2' then 1 else 0 end) as 女生数  from (select a.id,a.uid,a.tid,a.tag_name,users.name,users.sex from (select user_tag.id,user_tag.uid,user_tag.tid,tags.name as tag_name from user_tag left join tags on user_tag.tid=tags.tid) as a  left join users  on a.uid=users.uid ) as aa group by tag_name;
tid tag_name    男生数 女生数
3   听歌         1      0
2   看书         2      1
1   睡觉         2      1
5   跑步         2      1

// 优化, 以tags表为主, 把第一次的left join 变成 right join

select tid,tag_name,sum(case when sex='1' then 1 else 0 end) as 男生数,
sum(case when sex='2' then 1 else 0 end) as 女生数  from (select a.id,a.uid,a.tid,a.tag_name,users.name,users.sex from (select user_tag.id,user_tag.uid,user_tag.tid,tags.name as tag_name from user_tag right join tags on user_tag.tid=tags.tid) as a  left join users  on a.uid=users.uid ) as aa group by tag_name;
tid tag_name    男生数 女生数
3   听歌          1      0
    游戏          0      0
2   看书          2      1
1   睡觉          2      1
5   跑步          2      1

// 进一步的优化

select tid,tag_name,sum(case when sex='1' then 1 else 0 end) as 男生数,
sum(case when sex='2' then 1 else 0 end) as 女生数  from (select a.id,a.uid,a.tid,a.tag_name,users.name,users.sex from (select user_tag.id,user_tag.uid,tags.tid,tags.name as tag_name from user_tag right join tags on user_tag.tid=tags.tid) as a  left join users  on a.uid=users.uid ) as aa group by tag_name order by tid asc;
tid tag_name    男生数 女生数
1   睡觉          2      1
2   看书          2      1
3   听歌          1      0
4   游戏          0      0
5   跑步          2      1