使用SQL数据库中的现有列派生或修改几个列

我想使用SQL数据库表中存在的Date、Count、id列添加三个列LDate、TCount和LApprover.

该过程涉及特定的SQL查询，以提取每个新列的相关信息.

最初的步骤是通过执行以下SQL查询来计算总计数，表示为TCount

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

我想使用以下SQL查询来确定标记为LDate的最大日期:

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

得出LApprover列的逻辑涉及根据与LDate和Date列相关的条件 Select 最大批准者.SQL查询如下:

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

当前表格:

预期结果:

我已try 此查询，但未获得预期结果:

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 

我得到的结果是这样的: