具有多個條件和列的 SQL

我有 3 張桌子：

Employee
+----+--------------+--------------------+----------------+
| Id | EmployeeName | EmployeePermission | Position       |
+----+--------------+--------------------+----------------+
|  1 | Christopher  |                  3 | Leader         |
|  2 | Helen        |                  4 | Manager        |
|  3 | Ana          |                  5 | Branch Manager |
+----+--------------+--------------------+----------------+  

Employee Approval
+----+--------------------+--------+-----------+
| Id | EmployeePermission | TaskID | IsApprove |
+----+--------------------+--------+-----------+
|  1 |                  3 | 1a     |         1 |
|  2 |                  4 | 1a     |         0 |
|  3 |                  5 | 1a     |         0 |
|  4 |                  3 | 2b     |         0 |
|  5 |                  4 | 2b     |         0 |
+----+--------------------+--------+-----------+

Items
+----+----------+-------+--------+
| Id | ItemName | Spec  | TaskId |
+----+----------+-------+--------+
|  1 | Ruler    | 2 pcs | 1a     |
|  2 | Book     | 4 pcs | 2b     |
+----+----------+-------+--------+

我想顯示已經批准的員工姓名和下一個批准名稱，我想是這樣的

+----+--------+-------------+--------------+
| Id | TaskId |  Approval   | NextApproval |
+----+--------+-------------+--------------+
|  1 | 1a     | Christopher | Helen        |
|  2 | 2b     | -           | Christopher  |
+----+--------+-------------+--------------+

現在我使用子查詢來顯示名稱，但無法正確獲取條件。我嘗試使用 CASE WHEN，但它看起來很複雜，我迷路了。

有沒有辦法讓它更簡單？

使用派生表/CTE 獲取“已批准”/“未批准”的單個 ID，然後將其 LEFT JOIN 兩次Employees：

SELECT TaskID
  ,MAX(CASE WHEN IsApprove = 1 THEN EmployeePermission end) AS Approved_ID
  ,MIN(CASE WHEN IsApprove = 0 THEN EmployeePermission end) AS Not_Approved_ID
FROM "Employee Approval"
GROUP BY TaskID

此查詢獲取第一個Approve和第一個Not Approve：

WITH list AS (
   SELECT ea.TaskId, e.EmployeeName, ea.IsApprove
       , n1 = ROW_NUMBER() OVER(PARTITION BY ea.TaskId ORDER BY ea.IsApprove DESC, ea.Id)
       , n0 = ROW_NUMBER() OVER(PARTITION BY ea.TaskId ORDER BY ea.IsApprove, ea.Id)
   FROM @Employee_Approval ea
   INNER JOIN @Employee e ON e.EmployeePermission = ea.EmployeePermission
)
SELECT i.Id, i.TaskId, Approval = l1.EmployeeName, NextApproval = l2.EmployeeName
FROM @Items i
LEFT JOIN list l1 ON i.TaskId = l1.TaskId AND l1.n1 = 1 AND l1.IsApprove = 1
LEFT JOIN list l2 ON i.TaskId = l2.TaskId AND l2.n0 = 1 AND l2.IsApprove = 0

SQL小提琴

輸出：

Id  TaskId  EmployeeName    EmployeeName
1   1a      Christopher     Helen
2   2b      NULL            Christopher

引用自：https://dba.stackexchange.com/questions/128613