Oracle 錯誤“值太多”，必須使用嵌套 SELECT 解決

在使用一些複雜的嵌套 SELECT 時，我在 Oracle 中遇到了“太多值”錯誤。

我的問題的簡化描述，關於描述公司層次結構的玩具問題。有兩個表：

• ACG_EMPLOYEES，列出所有員工

• ACG_BOSS_OF，它簡單地列出誰 ( b_id ) 是誰 ( e_id )的老闆

我可以

SELECT b_id as boss,
            LTRIM(MAX(SYS_CONNECT_BY_PATH(e_id,','))
            KEEP (DENSE_RANK LAST ORDER BY curr),',') as minion
FROM (SELECT b_id,
            e_id,
            ROW_NUMBER() OVER (PARTITION BY b_id ORDER BY e_id) AS curr,
            ROW_NUMBER() OVER (PARTITION BY b_id ORDER BY e_id) -1 AS prev
     FROM acg_boss_of)
GROUP BY b_id
CONNECT BY prev = PRIOR curr AND b_id = PRIOR b_id
START WITH curr = 1

獲取不是層次結構中的葉子的每個員工的“奴才”列表：

但是假設我還想從ACG_EMPLOYEES表中獲取其他欄位，在本例中是每個老闆的名稱：

我努力了

SELECT 
emp.name, 
(SELECT b_id as boss,
            LTRIM(MAX(SYS_CONNECT_BY_PATH(e_id,','))
            KEEP (DENSE_RANK LAST ORDER BY curr),',') as minion
FROM (SELECT b_id,
            e_id,
            ROW_NUMBER() OVER (PARTITION BY b_id ORDER BY e_id) AS curr,
            ROW_NUMBER() OVER (PARTITION BY b_id ORDER BY e_id) -1 AS prev
     FROM acg_boss_of)
GROUP BY b_id
CONNECT BY prev = PRIOR curr AND b_id = PRIOR b_id
START WITH curr = 1) minion
FROM acg_employees emp

但我得到了我提到的“太多值”錯誤。

我已經看到了一些解決此類問題的建議；但是，我不能使用它們中的任何一個，因為此語句將在產品生命週期管理應用程序中使用，無論多麼複雜，都必須將其定義為單個 SELECT（好吧，我也可以使用 JOIN，但沒有儲存過程或諸如此類）。

使用連接？

SELECT 
 minion.boss,
 emp.name, 
 minion.minion
FROM acg_employees emp 
JOIN    
   (SELECT b_id as boss,
                LTRIM(MAX(SYS_CONNECT_BY_PATH(e_id,','))
                KEEP (DENSE_RANK LAST ORDER BY curr),',') as minion
   FROM (SELECT b_id,
                e_id,
                ROW_NUMBER() OVER (PARTITION BY b_id ORDER BY e_id) AS curr,
                ROW_NUMBER() OVER (PARTITION BY b_id ORDER BY e_id) -1 AS prev
         FROM acg_boss_of)
   GROUP BY b_id
   CONNECT BY prev = PRIOR curr AND b_id = PRIOR b_id
   START WITH curr = 1) minion
ON (minion.boss=emp.e_id)

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