SQL如何查询每位客户每月最后一条记录（含无数据空白月份）

核心解决思路 #

• 首先生成全量的「客户+目标月份月末」基准数据，补全所有需要输出的月份，解决缺月问题
• 把基准数据和客户变更记录表关联，匹配每个月末当天生效的客户状态
• 过滤关联结果得到唯一匹配的记录即可

调整后SQL（适配Oracle语法，和原有写法兼容） #

WITH all_months AS (
    -- 生成需要覆盖的所有月份的最后一天，示例为2021年全年，可按需调整起止范围
    SELECT last_day(add_months(date'2021-01-01', level-1)) as month_end
    FROM dual
    CONNECT BY level <= 12
),
all_customers AS (
    -- 去重得到所有需要统计的客户
    SELECT DISTINCT ID, Name FROM TableA
),
customer_month_base AS (
    -- 笛卡尔关联得到每个客户每个月的基准行
    SELECT ac.ID, ac.Name, am.month_end
    FROM all_customers ac
    CROSS JOIN all_months am
)
-- 关联原表取每个月末生效的状态
SELECT 
    cmb.ID,
    cmb.Name,
    ta.Number,
    to_char(cmb.month_end, 'yyyymmdd') as Date
FROM customer_month_base cmb
LEFT JOIN TableA ta
    ON cmb.ID = ta.ID
    AND cmb.month_end >= ta.from_date
    AND cmb.month_end < nvl(ta.to_date, date'9999-12-31')
ORDER BY cmb.ID, cmb.month_end;

说明 #

• 可调整all_months中的起始日期和生成数量，覆盖需要的近3年时间范围即可
• 关联条件用nvl(ta.to_date, date'9999-12-31')是为了匹配当前仍生效的、to_date为null的记录
• 2021年9月的月末实际为20210930，若示例中的20210931为笔误，输出会自动修正为正确日期

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