【MySQL】每日一题 -20200220

mysql> select * from saletypes;
+---------------+----------+--------+-----------+
| customer_name | province | city   | saletype  |
+---------------+----------+--------+-----------+
| 张三          | 湖北     | 武汉   | 低客单    |
| 李四          | 湖北     | 武汉   | 高客单    |
| 王五          | 湖北     | 武汉   | 高客单    |
| 赵六          | 湖南     | 岳阳   | 高客单    |
| 钱七          | 湖南     | 长沙   | 低客单    |
| 孙八          | 湖南     | 长沙   | 低客单    |
+---------------+----------+--------+-----------+
6 rows in set (0.00 sec)

据客单类别表统计出每个省份每个城市的低客单数和高客单数，如果某城市无低客单记录或高客单记录，其统计数为0

解法1:

mysql> select T.province,T.city,
    -> COALESCE(sum(case when T.saletype='低客单' then 1 else null end ),0) as low_num,
    -> COALESCE(sum(case when T.saletype='高客单' then 1 else null end ),0)as high_num
    -> from saletypes T
    -> group by T.province,T.city;

解法2:

select distinct T.province,T.city,
COALESCE(T1.low_num,0) as low_num ,
COALESCE(T2.high_num,0) as high_num
from saletypes T
left join 
(select province,city,
count(customer_name) as low_num
from saletypes
where saletype='低客单'
group by province,city )T1
on T.province=T1.province and T1.city=T1.city
left join (select province,city,
count(customer_name) as high_num
from saletypes
where saletype='高客单'
group by province,city) T2
on T.province=T2.province and T.city=T2.city;

结果

+----------+--------+---------+----------+
| province | city   | low_num | high_num |
+----------+--------+---------+----------+
| 湖北     | 武汉   |       1 |        2 |
| 湖南     | 岳阳   |       0 |        1 |
| 湖南     | 长沙   |       2 |        0 |
+----------+--------+---------+----------+
3 rows in set (0.00 sec)