基于现有字段新增SQL列异常：LDate、TCount、LApprover结果不符

需求规则 #

基于SQL表中现有Date、Count、id字段，新增LDate、TCount、LApprover三列，规则如下：

• TCount：按id分组的Count字段总和

  SELECT SUM(Count) AS TCount 
  FROM db 
  GROUP BY id; 
  

• LDate：按id分组的Date字段最大值

  SELECT MAX(Date) AS LDate 
  FROM db 
  GROUP BY id;
  

• LApprover：取LDate对应行的Approver，当LDate=Date或LDate为空时取该行Approver

  SELECT
      MAX(CASE 
              WHEN [LDate] = [Date] OR [LDate] IS NULL 
                  THEN [Approver] 
                  ELSE NULL 
          END) AS [LApprover] 
  FROM db 
  GROUP BY id; 
  

当前表数据 #

预期结果 #

已尝试的SQL及问题 #

原SQL语句：

WITH CombinedCTE AS 
(
    SELECT 
        q1.id, q2.Count, 
        q1.[TCount], q2.Date, q1.LDate, q2.Approver 
    FROM 
        (SELECT 
             id, COUNT(Count) AS [TCount], MAX([Date]) AS LDate 
         FROM 
             db 
         GROUP BY 
             id) q1    
    JOIN 
        (SELECT id, Count, [Date], Approver 
         FROM db) q2  ON q1.id = q2.id 
    WHERE 
        q2.id = 'E3' 
)
SELECT 
    id, Approver, Count, TCount, Date, LDate,
    MAX(CASE WHEN [LDate] IS NULL OR [LDate] = [Date] THEN [Approver] ELSE NULL END) AS [LApprover] 
FROM 
    (SELECT * FROM CombinedCTE) SubQuery
GROUP BY 
    id, Approver, Count, TCount, Date, LDate 

实际返回结果（部分LApprover为NULL）：

问题原因：原SQL的GROUP BY包含了Approver、Date等行级字段，导致每一行单独成为一个分组，MAX函数仅对当前行生效，非匹配行的CASE返回NULL，最终LApprover就是NULL。

修正后的SQL语句 #

WITH IdAggregates AS (
    SELECT 
        id,
        SUM(Count) AS TCount,
        MAX(Date) AS LDate,
        MAX(CASE WHEN Date = MAX(Date) THEN Approver END) AS LApprover
    FROM db
    GROUP BY id
)
SELECT 
    ia.LDate,
    db.Count,
    ia.TCount,
    db.Approver,
    ia.LApprover,
    db.Date,
    db.id
FROM db
JOIN IdAggregates ia ON db.id = ia.id
WHERE db.id = 'E3'
ORDER BY db.Date DESC;

说明 #

1. 先通过IdAggregates CTE计算每个id的三个聚合值：TCount（总和）、LDate（最大日期）、LApprover（最大日期对应的审批人）。这里直接在分组聚合时计算LApprover，利用MAX(Date)匹配对应行的Approver。
2. 将原表与IdAggregates按id关联，这样每一行都能拿到对应id的三个聚合字段，无需再对行级字段分组，确保所有行的LApprover都能正确显示。

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