SUM数据选择-2个具有相同id的人

Question

我的查询显示两个人的名字分别是"Anna“、"Szymkowiak”和这个相同的"id“，但这是同一个人。我该如何求和呢？例如:id "80"，"Anna"，"Szymkowiak"，" 23 "，2 -> 23是20个月加3的和，2是Aneks 1加1的和

SELECT DISTINCT SBA.HRSystemId AS Id, SBA.FirstName,  SBA.Surname, 
    CASE WHEN SBA.EndDate < GETDATE() 
    THEN DATEDIFF(MONTH,SBA.StartDate,SBA.EndDate)
    ELSE DATEDIFF(MONTH,SBA.StartDate, GETDATE()) 
    END AS Miesiace_Przepracowane,
    MAX(SBC.ContractNumber) AS Aneks
    FROM SB_Applications SBA JOIN SB_Contracts SBC ON SBA.ApplicationId = SBC.ApplicationId 
    WHERE SBA.IsActiveYN = 1 AND SBA.HRSystemId != 0 AND SBC.ApplicationStatusId = 4
    GROUP BY SBA.FirstName, SBA.Surname,SBA.StartDate,SBA.EndDate,SBA.StartDate,SBA.HRSystemId,SBC.ContractNumber;

我得到了这个：

id |FirstName|Surname|Miesiace_Przepracowane|Aneks
80, Anna, Szymkowiak, 20, 1
80, Anna, Szymkowiak, 3, 1
91, Jack, Sparrow, 2, 1
92, Tom, Jones, 1, 1
100, Rocky, Balboa, 3, 0

这个例子不起作用

​

感谢同事的帮助，这对我来说是一次很好的训练:)我给你的帮助点数。

Answer 1

当您已经有GROUP BY时，您几乎不想使用DISTINCT；它是无关的。

最自然、最快速的方法是使用派生表扩展查询：

SELECT HRSystemId, FirstName, SurName,
       Sum(Miesiace_Przepracowane) Miesiace_Przepracowane,
       MAX(Aneks) Aneks
FROM
(
    SELECT SBA.HRSystemId AS Id, SBA.FirstName,  SBA.Surname, 
        CASE WHEN SBA.EndDate < GETDATE() 
        THEN DATEDIFF(MONTH,SBA.StartDate,SBA.EndDate)
        ELSE DATEDIFF(MONTH,SBA.StartDate, GETDATE()) 
        END AS Miesiace_Przepracowane,
        MAX(SBC.ContractNumber) AS Aneks
        FROM SB_Applications SBA JOIN SB_Contracts SBC ON SBA.ApplicationId = SBC.ApplicationId 
        WHERE SBA.IsActiveYN = 1 AND SBA.HRSystemId != 0 AND SBC.ApplicationStatusId = 4
        GROUP BY SBA.FirstName, SBA.Surname,SBA.StartDate,SBA.EndDate,SBA.StartDate,SBA.HRSystemId,SBC.ContractNumber
) X
GROUP BY HRSystemId, FirstName, SurName;

Answer 2

Select ID,FirstName,Surname,SUM(Miesiace_Przepracowane) as Miesiace_Przepracowane,SUM(Aneks) as Aneks from
(
SELECT DISTINCT SBA.HRSystemId AS Id, SBA.FirstName,  SBA.Surname, 
    CASE WHEN SBA.EndDate < GETDATE() 
    THEN DATEDIFF(MONTH,SBA.StartDate,SBA.EndDate)
    ELSE DATEDIFF(MONTH,SBA.StartDate, GETDATE()) 
    END AS Miesiace_Przepracowane,
    MAX(SBC.ContractNumber) AS Aneks
    FROM SB_Applications SBA JOIN SB_Contracts SBC ON SBA.ApplicationId = SBC.ApplicationId 
    WHERE SBA.IsActiveYN = 1 AND SBA.HRSystemId != 0 AND SBC.ApplicationStatusId = 4
    GROUP BY SBA.FirstName, SBA.Surname,SBA.StartDate,SBA.EndDate,SBA.StartDate,SBA.HRSystemId,SBC.ContractNumber;
) a
Group by  ID,FirstName,Surname