时间戳

首页 > 解决方案 > 如何从 SQL 查询中删除重复行

sql - 如何从 SQL 查询中删除重复行

问题描述

这是我的查询：

SELECT TOP (100) PERCENT
       dbo.PMAPS.PMAP_Employee_NED_ID,
       dbo.Employees.First_Name,
       dbo.Employees.Last_Name,
       dbo.Employees.DeptID,
       dbo.Employees.Barg_Unit,
       dbo.Employees.Pay_Plan,  
       dbo.Employees.Grade, dbo.Employees.Last_Name + ', ' + dbo.Employees.First_Name AS Full_Name,
       dbo.FDA_Centers.Center_Acronym,
       MAX(dbo.PMAPS.PMAP_Establishment_Rating_Official_Signed) AS PMAP_Establishment_Rating_Official_Signed,
       dbo.PMAPS.PMAP_MidYear_Rating_Official_Signed,
       dbo.Employees.center_ID, 
       dbo.Employees.office_id,
       dbo.PMAPS.PMAP_MidYear_Estimated_completion_Date,
       dbo.PMAPS.PMAP_MidYear_Estimated_completion_Reason,
       dbo.PMAPS.PMAP_Below_Level_Three, 
       dbo.PMAPS.PMAP_Contact_LER
FROM dbo.PMAPS
     INNER JOIN dbo.Employees ON dbo.PMAPS.PMAP_Employee_NED_ID = dbo.Employees.CapHR_ID
     INNER JOIN dbo.FDA_Centers ON dbo.Employees.center_ID = dbo.FDA_Centers.Center_ID
GROUP BY dbo.PMAPS.PMAP_Employee_NED_ID,
         dbo.PMAPS.PMAP_Year
         dbo.PMAPS.PMAP_IsActive
         dbo.PMAPS.PMAP_MidYear_Estimated_completion_Date
         dbo.PMAPS.PMAP_Below_Level_Three
         dbo.Employees.center_ID
         dbo.Employees.isCommissionedCorps
         dbo.Employees.isExecutive
         dbo.Employees.Pay_Plan
         dbo.PMAPS.PMAP_Establishment_Rating_Official_Signed
         dbo.FDA_Centers.Center_Acronym
         dbo.Employees.First_Name
         dbo.Employees.Last_Name
         dbo.Employees.DeptID
         dbo.Employees.Barg_Unit
         dbo.Employees.Pay_Plan
         dbo.Employees.Grade
         dbo.Employees.center_ID
         dbo.Employees.office_id
         dbo.PMAPS.PMAP_MidYear_Rating_Official_Signed
         dbo.PMAPS.PMAP_MidYear_Estimated_completion_Reason
         dbo.PMAPS.PMAP_Contact_LER
HAVING  (dbo.PMAPS.PMAP_IsActive = 1)
        AND (dbo.Employees.isCommissionedCorps = 0)
        AND (dbo.Employees.isExecutive = 0)
        AND (NOT (dbo.Employees.Pay_Plan LIKE 'E%')
         AND NOT (dbo.Employees.Pay_Plan LIKE 'Z%')
         AND NOT (dbo.Employees.Pay_Plan LIKE 'S%')
          OR dbo.Employees.Pay_Plan IS NULL)
        AND (dbo.PMAPS.PMAP_MidYear_Estimated_completion_Date IS NULL)
        AND (dbo.PMAPS.PMAP_Establishment_Rating_Official_Signed IS NOT NULL)
        AND (dbo.Employees.center_ID = 14)
        AND (dbo.PMAPS.PMAP_Year = 2020)
        AND (dbo.PMAPS.PMAP_MidYear_Rating_Official_Signed IS NULL)
ORDER BY dbo.PMAPS.PMAP_Employee_NED_ID;

结果：

在此处输入图像描述

然后突出显示的数据行是我需要显示的行。我试过 MAX() （在上面的查询中），不同的，似乎没有任何效果。请指教。提前致谢。

标签： sqlsql-server

解决方案

我认为你只需要修复你的GROUP BY：

SELECT ID, First_Name, Last_Name, MAX(Date1) AS Date1, Date2
FROM dbo.vw_getActiveEmployees ae LEFT JOIN
     dbo.vw_active_Employees_with_PMAPs ewp
     ON emp.CapHR_ID = ae.CapHR_ID LEFT JOIN
     dbo.vw_active_Employees_with_MidYear_PMAPs emp
     ON emp.CapHR_ID = ewp.CapHR_ID 
GROUP BY ID,First_Name, Last_Name, Date2;

请注意，表别名使查询更易于阅读。我也更喜欢LEFT JOIN，RIGHT JOIN因为它说将所有内容保留在第一个表中（不是尚未阅读的最后一个表）。

推荐阅读