是否仅从一组行中选择要返回的一行？

Question

我正在使用AdventureWork2012数据库练习SQL，并且我已经创建了一个雇员信息表。这是我的查询和结果。

SELECT 
    [e].[BusinessEntityID]
    , [e].[JobTitle]
    , CONCAT (p.FirstName, ' ', p.LastName)             AS Full_Name
    , dep.Name                                          AS Department

    , DATENAME(dw, edh.StartDate) + ', ' +
      DATENAME(month, edh.StartDate) + ' ' +
      DATENAME(day, edh.StartDate) + ', ' +                 
      DATENAME(year, edh.StartDate)                     AS 'Start_Date'

    , CASE 
        WHEN edh.EndDate IS NULL
        THEN CONVERT(VARCHAR, GETDATE(), 101)       
        ELSE edh.EndDate
      END                                                                       AS End_Date

    , IIF(LEFT(pp.PhoneNumber, 3) = '1 (', ' ', LEFT(pp.PhoneNumber, 3))        AS Area_Code
    , eph.Rate

    , CASE CONVERT(CHAR(10), eph.PayFrequency)
        WHEN 2 THEN 'Biweekly'
        ELSE 'Weekly'
      END                                               AS PayFrequency

    , IIF(e.SalariedFlag = 1, 'Y', 'N')                 AS 'SalaryEmployee'

    , CASE 
        WHEN e.SalariedFlag = 1
        THEN ROUND((eph.Rate*40)*52, 2)
        ELSE NULL
      END                                               AS YearlySalary
--INTO EmpInfo

FROM [HumanResources].[Employee]    AS e
INNER JOIN  [HumanResources].[EmployeeDepartmentHistory] AS edh
    ON edh.BusinessEntityID = e.BusinessEntityID
INNER JOIN [Person].[Person] AS p 
    ON p.BusinessEntityId = e.BusinessEntityID
INNER JOIN [Person].[PersonPhone] AS pp
    ON pp.BusinessEntityID = e.BusinessEntityID
INNER JOIN [HumanResources].[Department] AS dep
    ON dep.DepartmentID = edh.DepartmentID
INNER JOIN [HumanResources].[EmployeePayHistory] AS eph
    ON eph.BusinessEntityID = e.BusinessEntityID


ORDER BY e.BusinessEntityID;

BusinessEntityID    JobTitle    Full_Name   Department  Start_Date  End_Date Area_Code  Rate    PayFrequency    SalaryEmployee  YearlySalary
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1   Chief Executive Officer         Ken Sánchez  Executive      Saturday, February 15,  2003 2019-11-08  697  125.50    Biweekly    Y   261040.00
2   Vice President of Engineering   Terri Duffy  Engineering    Sunday, March 3, 2002   2019-11-08  819 63.4615 Biweekly    Y   131999.92
3   Engineering Manager Roberto Tamburello  Engineering Wednesday, December 12, 2001    2019-11-08  212 43.2692 Biweekly    Y   89999.94
4   Senior Tool Designer    Rob Walters  Engineering    Saturday, January 5, 2002   2004-06-30  612 8.62    Biweekly    N   NULL
4   Senior Tool Designer    Rob Walters  Engineering    Saturday, January 5, 2002   2004-06-30  612 23.72   Biweekly    N   NULL
4   Senior Tool Designer    Rob Walters  Engineering    Saturday, January 5, 2002   2004-06-30   612    29.8462 Biweekly    N   NULL
4   Senior Tool Designer    Rob Walters  Tool Design     Thursday, July 1, 2004  2019-11-08  612    8.62    Biweekly    N   NULL
4   Senior Tool Designer    Rob Walters  Tool Design     Thursday, July 1, 2004  2019-11-08  612    23.72   Biweekly    N   NULL
4   Senior Tool Designer    Rob Walters  Tool Design    Thursday, July 1, 2004   2019-11-08  612    29.8462  Biweekly   N   NULL
5   Design Engineer Gail Erickson   Engineering     Wednesday, February 6, 2002  2019-11-08  849    32.6923  Biweekly   Y   67999.98

正如你所看到的，Rob Walters被列出了6次，因为他作为一名员工获得了3次加薪，并且是两个部门的一部分。有没有办法我只能退还Rob Walter所在的两个部门的最新/最高时薪？

Answer 1

像这样的东西应该很接近。我删除了日期的格式，因为它实际上属于前端。此外，您也不希望将字符串文字用作列别名。它令人困惑，在sql server 2016中已被弃用。https://docs.microsoft.com/en-us/sql/database-engine/deprecated-database-engine-features-in-sql-server-2016?view=sql-server-ver15

select *
from 
(
    SELECT 
        [e].[BusinessEntityID]
        , [e].[JobTitle]
        , CONCAT (p.FirstName, ' ', p.LastName) AS Full_Name
        , dep.Name AS Department
        , Start_Date = isnull(edh.StartDate, getdate())
        , End_Date = isnull(edh.EndDate, getdate())
        , IIF(LEFT(pp.PhoneNumber, 3) = '1 (', ' ', LEFT(pp.PhoneNumber, 3)) AS Area_Code
        , eph.Rate
        , CASE CONVERT(CHAR(10), eph.PayFrequency)
            WHEN 2 THEN 'Biweekly'
            ELSE 'Weekly'
          END AS PayFrequency
        , IIF(e.SalariedFlag = 1, 'Y', 'N')                 AS SalaryEmployee
        , CASE 
            WHEN e.SalariedFlag = 1
            THEN ROUND((eph.Rate*40)*52, 2)
            ELSE NULL
          END                                               AS YearlySalary
        , RowNum = ROW_NUMBER()over(partition by e.BusinessEntityID order by edh.StartDate desc) --or whatever column makes sense here
    --INTO EmpInfo
    FROM [HumanResources].[Employee]    AS e
    INNER JOIN  [HumanResources].[EmployeeDepartmentHistory] AS edh
        ON edh.BusinessEntityID = e.BusinessEntityID
    INNER JOIN [Person].[Person] AS p 
        ON p.BusinessEntityId = e.BusinessEntityID
    INNER JOIN [Person].[PersonPhone] AS pp
        ON pp.BusinessEntityID = e.BusinessEntityID
    INNER JOIN [HumanResources].[Department] AS dep
        ON dep.DepartmentID = edh.DepartmentID
    INNER JOIN [HumanResources].[EmployeePayHistory] AS eph
        ON eph.BusinessEntityID = e.BusinessEntityID
) x
where x.RowNum = 1
ORDER BY x.BusinessEntityID;