MS SQL Server:选择每月最高收入员工详细信息

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Name NVARCHAR(100),
    Salary DECIMAL(10, 2),
    HireDate DATE,
    DepartmentID INT
);

WITH MonthlySalary AS (
    SELECT 
        EmployeeID,
        Name,
        Salary,
        DATEFROMPARTS(YEAR(HireDate), MONTH(HireDate), 1) AS MonthYear
    FROM Employees
),
MaxMonthlySalary AS (
    SELECT 
        MonthYear,
        MAX(Salary) AS MaxSalary
    FROM MonthlySalary
    GROUP BY MonthYear
)
SELECT 
    ms.EmployeeID,
    ms.Name,
    ms.Salary,
    ms.MonthYear
FROM MonthlySalary ms
JOIN MaxMonthlySalary mms ON ms.MonthYear = mms.MonthYear AND ms.Salary = mms.MaxSalary
ORDER BY ms.MonthYear;

WITH RankedMonthlySalary AS (
    SELECT 
        ms.EmployeeID,
        ms.Name,
        ms.Salary,
        ms.MonthYear,
        ROW_NUMBER() OVER (PARTITION BY ms.MonthYear ORDER BY ms.Salary DESC) AS Rank
    FROM MonthlySalary ms
)
SELECT 
    EmployeeID,
    Name,
    Salary,
    MonthYear
FROM RankedMonthlySalary
WHERE Rank = 1
ORDER BY MonthYear;