基于两列关联表并取另一列最大值的SQL查询需求

现有关联HistoricCall表与Responses表的SQL语句如下：

SELECT *
FROM HistoricCall h
INNER JOIN Responses r
ON h.projectid = r.projectId 
AND h.caseId = r.caseId

其中HistoricCall表包含callNumber字段，用于记录每次呼叫的尝试次数。

现需编写查询语句，实现以下查询逻辑：

SELECT 
    h.callDate,
    r.cellmap,
    COUNT(*)

但仅保留**每个projectId+caseId组合对应最大callNumber**的h.callDate数据。

表结构及示例数据 #

HistoricCall表 #

Responses表 #

期望结果 #

核心思路是先筛选出每个projectId+caseId组合下最大callNumber对应的记录，再关联Responses表做分组统计，以下是两种常用实现方案：

方案一：子查询筛选最大呼叫次数 #

SELECT 
    r.projectId,
    CONVERT(VARCHAR(10), h.callDate, 23) AS callDate,
    r.cellmap,
    COUNT(*) AS Count
FROM HistoricCall h
INNER JOIN (
    -- 先获取每个分组的最大callNumber
    SELECT projectId, caseId, MAX(callNumber) AS maxCallNumber
    FROM HistoricCall
    GROUP BY projectId, caseId
) max_h 
    ON h.projectId = max_h.projectId 
    AND h.caseId = max_h.caseId 
    AND h.callNumber = max_h.maxCallNumber
INNER JOIN Responses r 
    ON h.projectId = r.projectId 
    AND h.caseId = r.caseId
GROUP BY r.projectId, CONVERT(VARCHAR(10), h.callDate, 23), r.cellmap
ORDER BY callDate, cellmap;

方案二：窗口函数标记最新呼叫记录 #

WITH ranked_calls AS (
    SELECT 
        *,
        -- 按分组排序，最大callNumber排第1位
        ROW_NUMBER() OVER (PARTITION BY projectId, caseId ORDER BY callNumber DESC) AS rn
    FROM HistoricCall
)
SELECT 
    r.projectId,
    CONVERT(VARCHAR(10), rc.callDate, 23) AS callDate,
    r.cellmap,
    COUNT(*) AS Count
FROM ranked_calls rc
INNER JOIN Responses r 
    ON rc.projectId = r.projectId 
    AND rc.caseId = r.caseId
WHERE rc.rn = 1 -- 仅保留最大callNumber的记录
GROUP BY r.projectId, CONVERT(VARCHAR(10), rc.callDate, 23), r.cellmap
ORDER BY callDate, cellmap;

补充说明 #

• 若使用MySQL数据库，将CONVERT(VARCHAR(10), callDate, 23)替换为DATE(callDate)即可实现日期格式化。
• 分组统计时按projectId、格式化后的日期、cellmap聚合，最终结果按日期和cellmap排序，与期望结果格式对齐。

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