Solution without Dummy variables! :)


  • 1
    S
    SELECT q0.Department AS Department, q0.Name AS Employee, q0.Salary AS Salary
      FROM
      (SELECT t0.Id AS Id, t0.Name AS Name, t0.Salary AS Salary, t0.DepartmentId AS DepartmentId, t1.Name AS Department
         FROM 
           Employee AS t0
         JOIN
           Department AS t1
         ON t0.DepartmentId = t1.Id
      ) AS q0
      JOIN
      (SELECT q1.Salary AS Salary, q1.DepartmentId AS DepartmentId
        FROM
          (SELECT DISTINCT t2.Salary AS Salary, t2.DepartmentId AS DepartmentId
            FROM 
             Employee AS t2) AS q1
        LEFT JOIN
          (SELECT DISTINCT t3.Salary AS Salary, t3.DepartmentId AS DepartmentId
            FROM 
             Employee AS t3) AS q2
        ON
        q1.Salary < q2.Salary AND q1.DepartmentId = q2.DepartmentId
        GROUP BY Salary, DepartmentId
        HAVING COUNT(q2.Salary) <= 2) AS q3
      ON 
        q0.Salary = q3.Salary AND q0.DepartmentId = q3.DepartmentId
      ORDER BY Department, Salary DESC

  • 0
    L

    niubility ! But it's not easy to understand


Log in to reply
 

Looks like your connection to LeetCode Discuss was lost, please wait while we try to reconnect.