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)
