处理空日期时段：查询近2个月支付最小金额（无数据则为0）

核心思路 #

• 先生成过去2个月的所有月份（不受原表数据存在与否影响）
• 将生成的月份列表与支付表按月份关联
• 对每个月份计算最小支付金额，无数据的月份用0填充

分数据库实现 #

MySQL 版本 #

-- 生成过去2个月的月份列表
WITH months AS (
    SELECT DATE_FORMAT(CURRENT_DATE - INTERVAL 1 MONTH, '%Y-%m-01') AS month_date
    UNION ALL
    SELECT DATE_FORMAT(CURRENT_DATE - INTERVAL 2 MONTH, '%Y-%m-01') AS month_date
)
SELECT
    m.month_date,
    COALESCE(MIN(p.sum), 0) AS min_sum
FROM months m
LEFT JOIN payment_table p ON DATE_FORMAT(p.date, '%Y-%m-01') = m.month_date
GROUP BY m.month_date
ORDER BY m.month_date DESC;

PostgreSQL 版本 #

-- 生成过去2个月的月份列表
WITH months AS (
    SELECT date_trunc('month', CURRENT_DATE - INTERVAL '1 month')::date AS month_date
    UNION ALL
    SELECT date_trunc('month', CURRENT_DATE - INTERVAL '2 months')::date AS month_date
)
SELECT
    m.month_date,
    COALESCE(MIN(p.sum), 0) AS min_sum
FROM months m
LEFT JOIN payment_table p ON date_trunc('month', p.date)::date = m.month_date
GROUP BY m.month_date
ORDER BY m.month_date DESC;

SQL Server 版本 #

-- 生成过去2个月的月份列表
WITH months AS (
    SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0) AS month_date
    UNION ALL
    SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 2, 0) AS month_date
)
SELECT
    m.month_date,
    ISNULL(MIN(p.sum), 0) AS min_sum
FROM months m
LEFT JOIN payment_table p ON DATEADD(MONTH, DATEDIFF(MONTH, 0, p.date), 0) = m.month_date
GROUP BY m.month_date
ORDER BY m.month_date DESC;

关键说明 #

• WITH months AS (...)：构建指定月份范围的临时数据集，确保每个目标月份都被覆盖
• LEFT JOIN：保留所有生成的月份，匹配原表对应月份的支付数据
• COALESCE/ISNULL：将无数据月份的MIN(p.sum)空值替换为0

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