如何高效查询最新FormSubmission距今6个月的FormTemplate数据

我有两张表：

1. FormTemplate，包含字段：Id、CreatedDate、Creator、Schema
2. FormSubmissions，包含字段：Id、FormTemplateId（外键）、CreatedDate、Creator

表数据示例 #

FormTemplate表 #

FormSubmission表 #

我需要查询所有满足最新关联FormSubmission记录距今已达6个月的FormTemplate行，上述示例中仅需返回FormTemplate的第3行（Id=3）。

方法1：子查询分组获取最新提交时间 #

先通过分组找到每个FormTemplate对应的最新提交时间，筛选符合时间条件的记录后，关联回FormTemplate表获取完整数据：

SELECT ft.*
FROM FormTemplate ft
JOIN (
    SELECT FormTemplateId, MAX(CreatedDate) AS LatestSubmissionDate
    FROM FormSubmissions
    GROUP BY FormTemplateId
) fs ON ft.Id = fs.FormTemplateId
WHERE fs.LatestSubmissionDate < DATEADD(MONTH, -6, GETDATE())

方法2：窗口函数筛选最新提交记录（适用于MySQL 8.0+、PostgreSQL、SQL Server等） #

用窗口函数标记每个FormTemplate的最新提交记录，再筛选符合条件的条目，逻辑更直观：

WITH RankedSubmissions AS (
    SELECT 
        FormTemplateId,
        CreatedDate,
        ROW_NUMBER() OVER (PARTITION BY FormTemplateId ORDER BY CreatedDate DESC) AS rn
    FROM FormSubmissions
)
SELECT ft.*
FROM FormTemplate ft
JOIN RankedSubmissions rs ON ft.Id = rs.FormTemplateId
WHERE rs.rn = 1 AND rs.CreatedDate < DATEADD(MONTH, -6, GETDATE())

性能优化建议 #

为避免全表扫描，提升查询效率，建议创建以下索引：

-- SQL Server 示例（覆盖索引）
CREATE INDEX IX_FormSubmissions_FormTemplateId_CreatedDate 
ON FormSubmissions (FormTemplateId) INCLUDE (CreatedDate);

-- MySQL 示例
CREATE INDEX IX_FormSubmissions_FormTemplateId_CreatedDate 
ON FormSubmissions (FormTemplateId, CreatedDate);

补充说明 #

如果需要包含从未有过提交记录的FormTemplate，可将JOIN改为LEFT JOIN并补充判断条件，但根据你的示例需求，这类记录不在返回范围内，当前查询已满足要求。

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