sql - 根据日期分组和重命名

Question

我有一个工作查询，它​​生成一个包含两列的表。

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;

它产生这张表：

我想根据月数进行分组。我检查了文档，下面的查询似乎是我需要的，但我得到一个语法错误。我尝试更改逻辑格式和命名约定，但不确定这里出了什么问题。

SELECT months, count(user_id) as count,
        sum(months=<2) as 'Less than 3 months',
        sum(months>2 and months=<5) as '3-6 months',
        sum(months>5 and months=<11) as '6-12 months',
        sum(months>11 and months=<24) as '12-24 months',
        sum(months>24 and months<20000) as 'More than 24 months',
        sum(months>20000) as 'Never'
        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
        
        group by months
         
        )
        t1;

非常感谢您的帮助。

标签： sqlmariadb

Answer 1

要转义列名，请使用双引号而不是单引号。但是，我会推荐不需要转义的名称。此外，将布尔值转换为数字

 select months, count(user_id) as count,
        sum( (months=<2)) as months_less_than_3_,
        sum( (months>2 and months=<5)) as months_3_6,
        sum( (months>5 and months=<11)) as months_6_12,
        sum( (months>11 and months=<24)) as months_12_24,
        sum( (months>24 and months<20000)) as months_25_plus,
        sum( (months>20000)) as Never