select t1.month_id, count(distinct offer_name), '今年往月新增有订购' as A from zone_crmchk.dwd_sum_zhuliuxxsc t1 left join zone_crmchk.dwd_sum_zhuliuxxsc t2 on t2.month < t1.month group by t1.month

这是一个 SQL 查询语句，用于统计每个月新增有订购的 offer 数量。其中，t1 和 t2 都是来自于 zone_crmchk 数据库中的 dwd_sum_zhuliuxxsc 表，并通过左连接来进行关联。具体来说，t1 表中的记录代表着当前月份的数据，而 t2 表中的记录则代表着之前月份的数据。通过在 t2 表上添加条件 t2.month < t1.month，可以保证只统计之前的月份。最后，根据 t1.month_id 进行分组，并使用 count(distinct offer_name) 统计不重复的 offer 数量，最终得到的结果会显示在第三列，显示的内容为“今年往月新增有订购”。

相关问题

select t1.month_id, count(distinct case when t1.offer_type_name='主套餐' then offer_name end) aa, count(distinct case when t1.offer_type_name='促销' then offer_name end) bb, count(distinct case when t1.offer_type_name='可选包' then offer_name end) cc, count(distinct case when t2.offer_type_name='主套餐' then offer_name end) aaa, count(distinct case when t2.offer_type_name='促销' then offer_name end) bbb, count(distinct case when t2.offer_type_name='可选包' then offer_name end) ccc, from zone_crmchk.dwd_sum_zhuliuxxsc t1 left join zone_crmchk.dwd_sum_zhuliuxxsc t2 on t2.month < t1.month group by t1.month, count(distinct case when offer_type_name='主套餐' then offer_name end) aa, count(distinct case when offer_type_name='促销' then offer_name end) bb, count(distinct case when offer_type_name='可选包' then offer_name end) cc

根据您提供的 SQL 查询语句，我发现其中存在一个逗号(,)出现在了 FROM 子句之后，这是一个语法错误。您需要将逗号(,)删除，同时将查询语句修改为如下形式：

SELECT
    t1.month_id, 
    COUNT(DISTINCT CASE WHEN t1.offer_type_name='主套餐' THEN offer_name END) aa, 
    COUNT(DISTINCT CASE WHEN t1.offer_type_name='促销' THEN offer_name END) bb, 
    COUNT(DISTINCT CASE WHEN t1.offer_type_name='可选包' THEN offer_name END) cc, 
    COUNT(DISTINCT CASE WHEN t2.offer_type_name='主套餐' THEN offer_name END) aaa, 
    COUNT(DISTINCT CASE WHEN t2.offer_type_name='促销' THEN offer_name END) bbb, 
    COUNT(DISTINCT CASE WHEN t2.offer_type_name='可选包' THEN offer_name END) ccc
FROM 
    zone_crmchk.dwd_sum_zhuliuxxsc t1 
    LEFT JOIN zone_crmchk.dwd_sum_zhuliuxxsc t2 ON t2.month < t1.month 
GROUP BY 
    t1.month_id, 
    COUNT(DISTINCT CASE WHEN offer_type_name='主套餐' THEN offer_name END), 
    COUNT(DISTINCT CASE WHEN offer_type_name='促销' THEN offer_name END), 
    COUNT(DISTINCT CASE WHEN offer_type_name='可选包' THEN offer_name END);

这样修改后，查询语句应该就能正确执行了。

select a.month_id, sum(b.aa) aa, sum(b.bb) bb, sum(b.cc) cc, '今年往月新增订购' as A from ( select month_id, sum(aa) aa,sum(bb) bb,sum(cc) cc from ( select month_id , count(distinct case when offer_type_name='主套餐' then offer_name end) aa, count(distinct case when offer_type_name='促销' then offer_name end) bb, count(distinct case when offer_type_name='可选包' then offer_name end) cc, from dwd_sum_zhuliuxxsc where substring(crt_version,1,4)='2023' group by month_id ) ptuser group by month_id ) a join ( select month_id,sum(aa) aa,sum(bb) bb,sum(cc) cc from ( select month_id , count(distinct case when offer_type_name='主套餐' then offer_name end) aa, count(distinct case when offer_type_name='促销' then offer_name end) bb, count(distinct case when offer_type_name='可选包' then offer_name end) cc, from dwd_sum_zhuliuxxsc where substring(crt_version,1,4)='2023' group by month_id ) ptuser group by month_id ) b on a.month_id >= b.month_id group by a.month_id order by a.month_id;如何改成计算今年往月(不包含本月)的统计

您可以将子查询中的 `where substring(crt_version,1,4)='2023'` 改为 `where year(crt_version) = year(current_date()) and month_id < month(current_date())`，这样就只会统计今年往月的数据（不包含本月）。修改后的 SQL 语句如下：

select a.month_id, 
       sum(b.aa) aa, 
       sum(b.bb) bb, 
       sum(b.cc) cc, 
       '今年往月新增订购' as A 
from (
    select month_id, sum(aa) aa,sum(bb) bb,sum(cc) cc
    from (
        select month_id,
               count(distinct case when offer_type_name='主套餐' then offer_name end) aa,
               count(distinct case when offer_type_name='促销' then offer_name end) bb,
               count(distinct case when offer_type_name='可选包' then offer_name end) cc
        from dwd_sum_zhuliuxxsc
        where year(crt_version) = year(current_date()) and month_id < month(current_date())
        group by month_id
    ) ptuser
    group by month_id
) a
join (
    select month_id,sum(aa) aa,sum(bb) bb,sum(cc) cc
    from (
        select  month_id,
                count(distinct case when offer_type_name='主套餐' then offer_name end) aa,
                count(distinct case when offer_type_name='促销' then offer_name end) bb,
                count(distinct case when offer_type_name='可选包' then offer_name end) cc
        from dwd_sum_zhuliuxxsc
        where year(crt_version) = year(current_date()) and month_id < month(current_date())
        group by month_id
    ) ptuser
    group by month_id
) b
on a.month_id >= b.month_id
group by a.month_id
order by a.month_id;

注意，这里的 `current_date()` 函数是 MySQL 中获取当前日期的函数，如果您使用的是其他数据库，获取当前日期的函数可能有所不同。