编写MySQL查询：按邮箱出现次数分组并拆分各国家对应数据

要实现你期望的输出，我们需要先统计每个邮箱在单个国家中的出现次数，再按这个次数分组，通过条件聚合拆分各国家的数据。以下是对应的SQL语句：

SELECT
    cnt AS count,
    SUM(CASE WHEN country = '_it' THEN cnt ELSE 0 END) AS `_it`,
    SUM(CASE WHEN country = '_hu' THEN cnt ELSE 0 END) AS `_hu`,
    SUM(CASE WHEN country = '_de' THEN cnt ELSE 0 END) AS `_de`,
    SUM(CASE WHEN country = '_pl' THEN cnt ELSE 0 END) AS `_pl`
FROM (
    -- 子查询：统计每个邮箱在对应国家的出现次数
    SELECT 
        email,
        country,
        COUNT(*) AS cnt
    FROM pinkpanda.shop_orders
    GROUP BY email, country
) AS email_country_stats
GROUP BY cnt
ORDER BY cnt;

逻辑说明 #

1. 子查询部分：按email和country分组，计算每个邮箱在单个国家里的订单次数，得到每个邮箱-国家组合的出现次数cnt。
2. 外层查询：
   • 按cnt（邮箱在国家的出现次数）分组；
   • 使用SUM(CASE ...)实现条件聚合，对每个国家分别统计对应cnt分组的订单总数，与你给出的期望输出完全匹配。

若需统计邮箱数量而非订单数 #

如果你的需求是统计「对应次数下各国家的邮箱个数」（比如count=2时_it应为1，代表有1个邮箱在_it出现2次），只需把SUM替换为COUNT即可：

SELECT
    cnt AS count,
    COUNT(CASE WHEN country = '_it' THEN 1 END) AS `_it`,
    COUNT(CASE WHEN country = '_hu' THEN 1 END) AS `_hu`,
    COUNT(CASE WHEN country = '_de' THEN 1 END) AS `_de`,
    COUNT(CASE WHEN country = '_pl' THEN 1 END) AS `_pl`
FROM (
    SELECT 
        email,
        country,
        COUNT(*) AS cnt
    FROM pinkpanda.shop_orders
    GROUP BY email, country
) AS email_country_stats
GROUP BY cnt
ORDER BY cnt;

内容的提问来源于stack exchange，提问作者Valor_