如何按月份分组统计已签署合同数量？SQL语句问题求助

核心问题梳理 #

原代码存在两个关键错误：

• SELECT子句未输出格式化后的月份字段，无法生成需求中的Months列
• GROUP BY语法错误，不能直接将日期与格式字符串作为分组条件，需先对日期字段做格式化处理

分数据库类型的修正代码 #

根据你使用的数据库类型选择对应代码：

MySQL/MariaDB

SELECT 
    DATE_FORMAT(A.DTIME_SIGNATURE, '%Y-%m') AS Months,
    COUNT(A.CONTRACT_NUMBER) AS Count
FROM DM_SALES.V_SALES_DM_DATA A
INNER JOIN DM_SALES.V_SALES_DM_CONTRACT_BUNDLE VSR 
    ON A.CONTRACT_NUMBER = VSR.CONTRACT_NUMBER 
WHERE 
    VSR.NAME_PRODUCER LIKE '%OPPO%'
    AND A.CONTRACT_STATE <> 'Cancelled' 
    AND a.cnt_signed = 1 
    AND A.LOAN_TYPE = 'Consumer Loan'
    AND A.DTIME_SIGNATURE >= '2022-01-01'
GROUP BY DATE_FORMAT(A.DTIME_SIGNATURE, '%Y-%m')
ORDER BY Months;

Oracle

SELECT 
    TO_CHAR(A.DTIME_SIGNATURE, 'YYYY-MM') AS Months,
    COUNT(A.CONTRACT_NUMBER) AS Count
FROM DM_SALES.V_SALES_DM_DATA A
INNER JOIN DM_SALES.V_SALES_DM_CONTRACT_BUNDLE VSR 
    ON A.CONTRACT_NUMBER = VSR.CONTRACT_NUMBER 
WHERE 
    VSR.NAME_PRODUCER LIKE '%OPPO%'
    AND A.CONTRACT_STATE <> 'Cancelled' 
    AND a.cnt_signed = 1 
    AND A.LOAN_TYPE = 'Consumer Loan'
    AND A.DTIME_SIGNATURE >= TO_DATE('2022-01-01', 'YYYY-MM-DD')
GROUP BY TO_CHAR(A.DTIME_SIGNATURE, 'YYYY-MM')
ORDER BY Months;

SQL Server

SELECT 
    FORMAT(A.DTIME_SIGNATURE, 'yyyy-MM') AS Months,
    COUNT(A.CONTRACT_NUMBER) AS Count
FROM DM_SALES.V_SALES_DM_DATA A
INNER JOIN DM_SALES.V_SALES_DM_CONTRACT_BUNDLE VSR 
    ON A.CONTRACT_NUMBER = VSR.CONTRACT_NUMBER 
WHERE 
    VSR.NAME_PRODUCER LIKE '%OPPO%'
    AND A.CONTRACT_STATE <> 'Cancelled' 
    AND a.cnt_signed = 1 
    AND A.LOAN_TYPE = 'Consumer Loan'
    AND A.DTIME_SIGNATURE >= '2022-01-01'
GROUP BY FORMAT(A.DTIME_SIGNATURE, 'yyyy-MM')
ORDER BY Months;

补充说明 #

• 原代码用LEFT JOIN但在WHERE子句过滤VSR.NAME_PRODUCER，会自动转为INNER JOIN（不匹配的记录会被过滤），直接改为INNER JOIN逻辑更清晰
• 添加ORDER BY Months确保结果按月份顺序排列
• 日期条件A.DTIME_SIGNATURE >= '01-01-2022'改为标准YYYY-MM-DD格式，避免数据库解析歧义

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