如何基于两个月前的记录查询各ID的分数？SQL技术求助

我是SQL新手，现在需要基于两个月前的记录，分别获取每个ID对应的分数。所有APP Date都是月末日期，以当前日期2022年11月7日为例，要获取2022-09-30的记录。

示例数据 #

期望结果 #

我之前用MAX函数获取最新日期的记录，但不知道怎么改成获取两个月前的，当前的查询语句如下：

with maxdate as ( 
    select ID, max([APP_DATE]) as maxdate 
    from [Table]
    where ID is not null group by ID 
) 
SELECT [APP_DATE],
    a.ID,
    Score 1,
    Score 2,
    Score 3 
FROM [Table] a 
join maxdate md on a.ID = md.ID and maxdate = a.[APP_DATE]
ORDER BY ID

解决方案 #

核心逻辑很简单：先算出两个月前的月末日期，然后直接匹配这个日期对应的各ID记录就行——因为你的APP Date全是月末，不用额外处理非月末的情况。

方法1：直接匹配目标日期（按数据库类型选对应代码） #

不同数据库的日期函数不一样，下面是几种常用数据库的实现：

SQL Server

SELECT [APP_DATE], ID, [Score 1], [Score 2], [Score 3]
FROM [Table]
WHERE 
    ID IS NOT NULL
    AND [APP_DATE] = EOMONTH(DATEADD(MONTH, -2, GETDATE()))
ORDER BY ID

• GETDATE()拿当前日期，DATEADD(MONTH, -2, ...)往前推2个月，EOMONTH()直接返回该月最后一天（刚好是你的APP Date格式）

MySQL

SELECT `APP_DATE`, ID, `Score 1`, `Score 2`, `Score 3`
FROM `Table`
WHERE 
    ID IS NOT NULL
    AND `APP_DATE` = LAST_DAY(DATE_SUB(CURDATE(), INTERVAL 2 MONTH))
ORDER BY ID

• CURDATE()取当前日期，DATE_SUB(..., INTERVAL 2 MONTH)减2个月，LAST_DAY()返回月末日期

PostgreSQL

SELECT "APP_DATE", ID, "Score 1", "Score 2", "Score 3"
FROM "Table"
WHERE 
    ID IS NOT NULL
    AND "APP_DATE" = (DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '2 months') + INTERVAL '1 month - 1 day'
ORDER BY ID

• 先把当前日期截断到当月第一天，减2个月后，再加一个月减1天，得到目标月末日期

方法2：如果要保留所有ID（哪怕没有两个月前的记录） #

如果有些ID没有对应两个月前的记录，想保留这些ID并显示空值，可以用左连接：

以SQL Server为例：

WITH target_date AS (
    SELECT EOMONTH(DATEADD(MONTH, -2, GETDATE())) AS target_app_date
)
SELECT 
    td.target_app_date AS [APP_DATE],
    ids.ID,
    t.[Score 1],
    t.[Score 2],
    t.[Score 3]
FROM (SELECT DISTINCT ID FROM [Table] WHERE ID IS NOT NULL) ids
CROSS JOIN target_date td
LEFT JOIN [Table] t 
    ON ids.ID = t.ID 
    AND t.[APP_DATE] = td.target_app_date
ORDER BY ids.ID

对你原查询的修改 #

原查询用MAX(APP_DATE)取最新日期，现在只需把这个MAX替换成计算出的目标月末日期就行，SQL Server版本如下：

WITH target_date AS (
    SELECT ID, EOMONTH(DATEADD(MONTH, -2, GETDATE())) AS target_date
    FROM [Table]
    WHERE ID IS NOT NULL
    GROUP BY ID
)
SELECT 
    t.[APP_DATE],
    t.ID,
    t.[Score 1],
    t.[Score 2],
    t.[Score 3]
FROM [Table] t
JOIN target_date td ON t.ID = td.ID AND t.[APP_DATE] = td.target_date
ORDER BY t.ID

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