通過 5 個數據庫實例對 6 個表進行多次 JOIN

我目前正在重新設計我的公司使用者管理並創建一個表，其中列出了所有 5 個數據庫實例的所有使用者。下一步是，我需要編寫一個查詢，向我顯示他在所有實例中擁有的使用者的所有角色。

我已經使用UNION ALL了，但是輸出是非結構化的，你無法分辨哪個角色在哪個實例上。所以我只為 3 個表嘗試了以下操作：

SELECT W.GRANTED_ROLE "GRANTED_ROLE_DB1", V.GRANTED_ROLE "GRANTED_ROLE_DB2"
FROM SCHEMA.USR_ALL_USERS U
LEFT OUTER JOIN SYS.DBA_ROLE_PRIVS W
  ON (U.USERNAME = W.GRANTEE AND U.DB_INSTANCE = 'DB1')
LEFT OUTER JOIN SYS.DBA_ROLE_PRIVS@DB2_LINK V
  ON (U.USERNAME = V.GRANTEE AND U.DB_INSTANCE = 'DB2')
WHERE U.USERNAME = 'USER'
ORDER BY U.USERNAME ASC;

它確實有效，但輸出並不令人滿意：

GRANTED_ROLE_DB1            GRANTED_ROLE_DB2
--------------------------- --------------------------
ROLE_1
ROLE_2
ROLE_3
ROLE_4
                           ROLE_1
                           ROLE_2
                           ROLE_4
                           ROLE_5
                           ROLE_6

有沒有辦法做出這樣的輸出：

GRANTED_ROLE_DB1            GRANTED_ROLE_DB2
--------------------------- --------------------------
ROLE_1                      ROLE_1
ROLE_2                      ROLE_2
ROLE_3
ROLE_4                      ROLE_4
                           ROLE_5
                           ROLE_6

我試過ON ((U.USERNAME = V.GRANTEE OR W.GRANTED_ROLE = V.GRANTED_ROLE) AND U.DB_INSTANCE = 'DB2')了，但輸出更糟。

你們有什麼建議或有用的想法嗎？

SCHEMA.USR_ALL_USERS表真的需要參與這個查詢嗎？似乎沒有：您正在過濾結果的同一列，USERNAME也用於連接其他兩個表。因此，您可以省略SCHEMA.USR_ALL_USERS並將過濾直接應用於SYS.DBA_ROLE_PRIVS和SYS.DBA_ROLE_PRIVS@DB2_LINK。

現在，有了這兩個表的兩個子集，您可以採用@Chris Saxon 的方法並使用 FULL JOIN：

SELECT
 DB1.GRANTED_ROLE AS "GRANTED_ROLE_DB1",
 DB2.GRANTED_ROLE AS "GRANTED_ROLE_DB2"
FROM (
 SELECT GRANTED_ROLE
 FROM SYS.DBA_ROLE_PRIVS
 WHERE GRANTEE = 'USER'
) DB1
FULL OUTER JOIN (
 SELECT GRANTED_ROLE
 FROM SYS.DBA_ROLE_PRIVS@DB2_LINK
 WHERE GRANTEE = 'USER'
) DB2
ON DB1.GRANTED_ROLE = DB2.GRANTED_ROLE
ORDER BY
 COALESCE(DB1.GRANTED_ROLE, DB2.GRANTED_ROLE)
;

或者你可以聯合這兩組：

SELECT
 'GRANTED_ROLE_DB1' AS GRANTED_ROLE_DB,
 GRANTED_ROLE
FROM SYS.DBA_ROLE_PRIVS
WHERE GRANTEE = 'USER'
UNION ALL
SELECT
 'GRANTED_ROLE_DB2' AS GRANTED_ROLE_DB,
 GRANTED_ROLE
FROM SYS.DBA_ROLE_PRIVS@DB2_LINK
WHERE GRANTEE = 'USER'

讓你得到這樣的結果：

GRANTED_ROLE_DB   GRANTED_ROLE
----------------  ------------
GRANTED_ROLE_DB1  ROLE_1
GRANTED_ROLE_DB1  ROLE_2
GRANTED_ROLE_DB1  ROLE_3
GRANTED_ROLE_DB1  ROLE_4
GRANTED_ROLE_DB2  ROLE_1
GRANTED_ROLE_DB2  ROLE_2
GRANTED_ROLE_DB2  ROLE_4
GRANTED_ROLE_DB2  ROLE_5
GRANTED_ROLE_DB2  ROLE_6

然後你會 PIVOT，例如：

**SELECT
CASE WHEN "'GRANTED_ROLE_DB1'" > 0 THEN GRANTED_ROLE END AS GRANTED_ROLE_DB1,
CASE WHEN "'GRANTED_ROLE_DB2'" > 0 THEN GRANTED_ROLE END AS GRANTED_ROLE_DB2
FROM (**
 SELECT
   'GRANTED_ROLE_DB1' AS GRANTED_ROLE_DB,
   GRANTED_ROLE
 FROM SYS.DBA_ROLE_PRIVS
 WHERE GRANTEE = 'USER'
 UNION ALL
 SELECT
   'GRANTED_ROLE_DB2' AS GRANTED_ROLE_DB,
   GRANTED_ROLE
 FROM SYS.DBA_ROLE_PRIVS@DB2_LINK
 WHERE GRANTEE = 'USER'
**) s
PIVOT (
COUNT(*) FOR GRANTED_ROLE_DB IN ('GRANTED_ROLE_DB1', 'GRANTED_ROLE_DB2')
) p
ORDER BY
GRANTED_ROLE
;**

您可以在 SQL Fiddle看到這兩種方法“在行動中” 。

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