sql题:联表查询+分组

题干：给定一个店铺直播间的用户付款时间表和用户标签表，表结构如下

mysql> desc shop_pay;
+----------+----------+------+-----+---------+-------+
| Field    | Type     | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+-------+
| shop_id  | int(11)  | YES  |     | NULL    |       |
| room_id  | int(11)  | YES  |     | NULL    |       |
| user_id  | int(11)  | YES  |     | NULL    |       |
| pay_time | datetime | YES  |     | NULL    |       |
+----------+----------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> desc user_label;
+---------+----------+------+-----+---------+-------+
| Field   | Type     | Null | Key | Default | Extra |
+---------+----------+------+-----+---------+-------+
| user_id | int(11)  | YES  |     | NULL    |       |
| gender  | char(40) | YES  |     | NULL    |       |
+---------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)

insert into shop_pay values(101,201,301,now());
insert into shop_pay values(101,201,302,now());
insert into shop_pay values(101,201,303,now());
insert into shop_pay values(101,201,304,now());
insert into shop_pay values(101,202,301,now());
insert into shop_pay values(101,202,302,now());
insert into shop_pay values(101,202,303,now());
insert into shop_pay values(101,202,304,now());
insert into shop_pay values(102,201,301,now());
insert into shop_pay values(102,201,302,now());
insert into shop_pay values(102,201,303,now());
insert into shop_pay values(102,201,304,now());
insert into shop_pay values(102,201,305,now());
insert into shop_pay values(102,201,306,now());
insert into shop_pay values(102,201,307,now());
insert into shop_pay values(101,201,308,now());

insert into user_label values(301,'male');
insert into user_label values(302,'male');
insert into user_label values(303,'male');
insert into user_label values(304,'female');
insert into user_label values(305,'female');
insert into user_label values(306,null);

一、统计店铺直播间维度的男女用户人数

select 
    shop_id,
    room_id,
    sum(case gender when 'male' then 1 else 0 end) as male,
    sum(case gender when 'female' then 1 else 0 end) as female
from 
    (select distinct shop_id,room_id,user_id from shop_pay) a 
    inner join 
    user_label b 
    on a.user_id=b.user_id 
group by 
    shop_id,room_id;

select 
    shop_id,
    room_id,
    sum(if(gender='male',1,0)) as male,
    sum(if(gender='female',1,0)) as female 
from 
    (select distinct shop_id,room_id,user_id from shop_pay) a 
    inner join 
    user_label b 
    on a.user_id=b.user_id 
group by 
    shop_id,room_id;

二、找出某个店铺直播间的男女用户人数

select 
    shop_id,
    room_id,
    sum(case gender when 'male' then 1 else 0 end) as male,
    sum(case gender when 'female' then 1 else 0 end) as female
from 
    (select distinct shop_id,room_id,user_id from shop_pay) a 
    inner join 
    user_label b 
    on a.user_id=b.user_id 
group by 
    shop_id,room_id
having
    shop_id=101 and room_id=201;

三、找出店铺直播间没有标签的用户数

select 
    shop_id,
    room_id,
    count(a.user_id) 
from 
    (select distinct shop_id,room_id,user_id from shop_pay) a 
    left join 
    user_label b 
    on a.user_id=b.user_id 
where 
    gender is null 
    group by shop_id,room_id;