SQL PIVOT按国家统计2010年至今用户下单次数区间报错求解

报错原因 #

1. PIVOT操作中IN子句里的[4-10]、[10-100]是字符串类的区间标签，而子查询返回的totalpay是数值型的下单次数值，二者类型不匹配无法直接映射，所以触发类型转换和值不匹配的报错。
2. 原有WHERE条件date like '%2010%'仅能筛选2010年的数据，不符合“2010年至今”的统计需求。
3. 统计逻辑缺失：需要先按「国家+用户ID」维度统计每个用户的累计下单次数，再将次数归类到对应区间，最后按国家聚合统计各区间的数量，原有逻辑直接按国家+count分组不符合需求。

修正后SQL（适配SQL Server语法） #

SELECT *
FROM (
    -- 第二层：给每个用户的下单次数打区间标签
    SELECT 
        Country,
        user_order_cnt,
        -- 映射区间标签，可按需调整边界划分规则
        CASE 
            WHEN user_order_cnt = 1 THEN '1'
            WHEN user_order_cnt = 2 THEN '2'
            WHEN user_order_cnt = 3 THEN '3'
            WHEN user_order_cnt BETWEEN 4 AND 10 THEN '4-10'
            WHEN user_order_cnt BETWEEN 11 AND 100 THEN '10-100'
            ELSE NULL -- 超过100次的不计入统计，可按需调整
        END AS pay_range
    FROM (
        -- 第一层：按国家+用户ID统计每个用户2010年至今的下单次数
        SELECT 
            Country,
            userid,
            COUNT(*) AS user_order_cnt
        FROM [CRM].[dbo].[Orders]
        WHERE date >= '2010-01-01' -- 筛选2010年1月1日至今的数据，可按需调整起始日期
        GROUP BY Country, userid
    ) t1
) t2
PIVOT (
    -- 统计每个区间的用户数量
    COUNT(user_order_cnt)
    FOR pay_range IN ([1],[2],[3],[4-10],[10-100])
) AS country_pivot_result

逻辑说明 #

• 第一层子查询先统计每个国家下每个用户2010年至今的累计下单次数
• 第二层子查询通过CASE语句将下单次数映射为PIVOT需要的区间字符串标签，保证和IN子句里的标签完全匹配
• 最后PIVOT按国家聚合，统计每个区间的用户数量，输出符合要求的结果格式
  如果需求要求10次计入「10-100」区间，调整CASE语句的区间边界即可。

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