sql - 基于“月”SQL 对行进行分组

Question

我有一个工作查询，它​​产生如下所示的表格。

查询：

Select * from (
SELECT months, count(user_id) as count
        from 
        (
        select u.user_id, u.region_id, u.latest_login, year(u.latest_login), 
        
        period_diff(date_format(now(), '%Y%m'),date_format(u.latest_login, '%Y%m')) as months
        
        from users u
        where u.date_ended = 0000-00-00 
            AND country_id = 1 
            AND intRoleId = 3 
        )
        t1
        group by months
)
t2

桌子：

我想根据“月”数对行进行分组，并合并计数。因此，对于名为“不到 3 个月”的组，结果应显示为 140。

以下是我尝试过的查询，但结果不正确。我想也许我不应该使用 SUM，但不确定要使用什么。关于如何获得我想要的结果的任何建议？

Select months, count,
sum(months<=2) as months_less_than_3_,
sum(months>=3) as months_3_6,
sum(months>=6) as months_6_12,
sum(months>=12) as months_12_24,
sum(months>=24) as months_25_plus,
sum(months>=20000) as Never


from (
SELECT months, count(user_id) as count
        from 
        (
        select u.user_id, u.region_id, u.latest_login, year(u.latest_login), 
        
        period_diff(date_format(now(), '%Y%m'),date_format(u.latest_login, '%Y%m')) as months
        
        from users u
        where u.date_ended = 0000-00-00 
            AND country_id = 1 
            AND intRoleId = 3 
        )
        t1
        group by months
)
t2

标签： sqlmariadbpivot-table

Answer 1

    SELECT MonthGroup, SUM(count) as CountGroup 
    FROM 
    (Select 
    CASE 
    WHEN months<=2 THEN 'months_less_than_3'
    WHEN months<=6 THEN  'months_3_6'
    WHEN months<=12 THEN 'months_6_12'
    WHEN months<=24 THEN 'months_12_24'
    WHEN months>=20000 THEN 'Never'
    WHEN months>=25 THEN'months_25_plus'
    END AS MonthGroup, count    
    from (
        SELECT months, count(user_id) as count
        from 
        (
        select u.user_id, u.region_id, u.latest_login, year(u.latest_login), 
        
        period_diff(date_format(now(), '%Y%m'),date_format(u.latest_login, '%Y%m')) as months
        
        from users u
        where u.date_ended = 0000-00-00 
            AND country_id = 1 
            AND intRoleId = 3 
        ) t1
        group by months
        ) t2
    ) t3        
    GROUP BY MonthGroup