如何调整SQL查询以获取完整14个月数据及当月截至今日数据？

问题分析 #

原查询用now() - INTERVAL '14 MONTHS'作为日期起始，会包含不完整的起始月份（比如当前是9月，14个月前是7月，会拿到7月的部分数据），不符合“14个完整月份+当前月截至今日”的需求。

修改后的SQL #

SELECT t.cora_acct_code, t.accountnumber, c.accounttype, t.accountingdate, t.postingamount, t.refer_30, t.control_30, t.control2_30, t.detaildescription 
FROM ed.gljedetail_v as t 
  LEFT JOIN ed.glcoa_v as c 
        on t.cora_acct_code = c.cora_acct_code 
       AND t.accountnumber = c.accountnumber 
WHERE t.cora_acct_code = 'BMW-A' 
  AND c.accounttype = 'E'
  AND (
      -- 14个完整历史月份：从14个月前的月初到上月末
      (t.accountingdate >= date_trunc('month', now()) - INTERVAL '14 MONTHS'
       AND t.accountingdate <= date_trunc('month', now()) - INTERVAL '1 DAY')
      OR
      -- 当前月份截至今日的数据
      (t.accountingdate >= date_trunc('month', now())
       AND t.accountingdate <= now())
  )
ORDER BY t.accountingdate

关键逻辑说明 #

• date_trunc('month', now())：获取当前月份的第一天（比如当前是2024-09-15，返回2024-09-01）
• date_trunc('month', now()) - INTERVAL '14 MONTHS'：计算14个完整月份的起始日期（2024-09-01减14个月得到2023-07-01）
• date_trunc('month', now()) - INTERVAL '1 DAY'：获取上月最后一天（2024-09-01减1天得到2024-08-31）
• 用OR连接两个日期范围，确保同时拿到14个完整历史月和当前月截至今日的数据

注：该语法基于PostgreSQL，若使用其他数据库（如MySQL），日期函数会略有差异（比如MySQL用DATE_FORMAT(NOW(), '%Y-%m-01')替代date_trunc('month', now())）。

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