Mysql 用户ip访问根据省份查询

表名：shop_interview_customer

表结构：customerId空为游客模式

interviedId	customerId	interviewIP	iPdetail
1	1001	192.168.10.208	中国,湖北,武汉
2	1001	192.168.10.208	中国,湖北,武汉
3	1002	188.156.12.222	中国,新疆,乌鲁木齐
4	NULL	155.156.12.222	中国,西藏,拉萨

 

需求，用户访问根据ip判断，同一用户只算一次，游客模式算多次

查询结果应该为：

cou	province
2	湖北
1	新疆
1	西藏

SELECT SUM(too.cou) AS people,too.province FROM (
    SELECT SUM(CASE WHEN se1.customerId IS NOT NULL THEN 1 END)AS cou,se1.province  FROM(
        SELECT 
            COUNT(*)AS cou,shop_interview_customer.customerId,province
        FROM
            shop_interview_customer 
        WHERE
            IPdetail IS NOT NULL 
        GROUP BY province ,shop_interview_customer.customerId ORDER BY cou DESC )AS se1
    GROUP BY se1.province

UNION ALL

    SELECT se1.cou ,se1.province  FROM(
        SELECT 
            COUNT(*)AS cou,shop_interview_customer.customerId,province
        FROM
            shop_interview_customer 
        WHERE
            IPdetail IS NOT NULL 
        GROUP BY province ,shop_interview_customer.customerId ORDER BY cou DESC )AS se1
    WHERE se1.customerId IS NULL
    GROUP BY se1.province
)AS too  GROUP BY too.province ORDER BY SUM(too.cou) DESC LIMIT 10