對於欄位列表中的每個欄位名稱，獲取唯一值

問題：

我有一個視圖，它是表中的欄位列表：

FIELD_LIST_VW
+-------------+------------+
| TABLE_NAME  | FIELD_NAME |
+-------------+------------+
| ENG.TABLE_1 | FIELD_1    |
| ENG.TABLE_1 | FIELD_2    |
| ENG.TABLE_2 | FIELD_A    |
+-------------+------------+

基礎表如下所示：

TABLE_1
+---------+---------+
| FIELD_1 | FIELD_2 |
+---------+---------+
| A       | X       |
| A       | Y       |
| B       | Y       |
| B       | Y       |
| B       |         |
| C       |         |
+---------+---------+

TABLE_2
+---------+
| FIELD_A |
+---------+
|       1 |
|       1 |
|       1 |
|       2 |
|       3 |
+---------+

TABLE_3, TABLE_4, etc...

我想從展開欄位列表FIELD_LIST_VW，以便每個欄位的每個唯一值都有一行：

+-------------+------------+--------------+
| TABLE_NAME  | FIELD_NAME | UNIQUE_VALUE |
+-------------+------------+--------------+
| ENG.TABLE_1 | FIELD_1    | A            |
| ENG.TABLE_1 | FIELD_1    | B            |
| ENG.TABLE_1 | FIELD_1    | C            |
+-------------+------------+--------------+
| ENG.TABLE_1 | FIELD_2    | X            |
| ENG.TABLE_1 | FIELD_2    | Y            |
+-------------+------------+--------------+
| ENG.TABLE_2 | FIELD_A    | 1            |
| ENG.TABLE_2 | FIELD_A    | 2            |
| ENG.TABLE_2 | FIELD_A    | 3            |
+-------------+------------+--------------+

---

我試過的：

我認為一個好的起點是cross join子選擇：

SELECT
   a.table_name
   ,a.field_name
   ,b.unique_value
FROM
   eng.field_list_vw a
CROSS JOIN (
   SELECT 
       'FIELD_1' AS field_name    --<-- Hardcoded
        ,field_1 AS unique_value  --<-- Hardcoded
   FROM 
       eng.table_1                --<-- Hardcoded
   GROUP BY 
       field_1                    --<-- Hardcoded
   ) b
WHERE a.field_name = b.field_name

但當然，cross join子選擇充滿了硬編碼的表和欄位名稱。該查詢連接TABLE_1, FIELD_1，但不連接 中的任何其他欄位FIELD_LIST_VW。這不是我想要的：

+-------------+------------+--------------+
| TABLE_NAME  | FIELD_NAME | UNIQUE_VALUE |
+-------------+------------+--------------+
| ENG.TABLE_1 | FIELD_1    | A            |
| ENG.TABLE_1 | FIELD_1    | B            |
| ENG.TABLE_1 | FIELD_1    | C            |
+-------------+------------+--------------+
(rows are missing)

有沒有辦法動態交叉連接到每個欄位的唯一值，而不是硬編碼FIELD_LIST_VW？

我沒有CREATE TYPE特權。

解決此問題的一種方法是使用動態 sql。實際上，您的問題是範例案例之一：

例如，包含在編譯時未知的標識符（例如表名）的 SELECT 語句或在編譯時子句的數量未知的 WHERE 子句。

在編譯時，您不知道查詢的表名、列名或子句的數量。據我所知，你不能把它寫成一個簡單的 SQL 查詢。您需要一些中間對象為您創建查詢文本。

首先，讓我們編寫一個完整的查詢來滿足您對三個範例行的需求。我提出以下建議：

SELECT 'ENG.TABLE_1', 'FIELD_1', FIELD_1 FROM ENG.TABLE_1
UNION
SELECT 'ENG.TABLE_1', 'FIELD_2', FIELD_2 FROM ENG.TABLE_1
UNION
SELECT 'ENG.TABLE_2', 'FIELD_A', FIELD_A FROM ENG.TABLE_2

UNION運算符只會從查詢返回不同的結果集，因此應該返回正確的結果。使用針對視圖的查詢來生成上述查詢的一種方法是利用LISTAGG運算符。

對於指定的度量，LISTAGG 對 ORDER BY 子句中指定的每個組中的數據進行排序，然後連接度量列的值。

作為單集聚合函式，LISTAGG 對所有行進行操作並返回單個輸出行。

對於度量表達式，我建議建構一個字元串，根據需要在列名周圍添加引號。因為這是一個字元串，所以有點亂：

'SELECT ''' || CAST(FIELD_LIST_VW.TABLE_NAME AS VARCHAR2(30)) 
   || ''', ''' || CAST(FIELD_LIST_VW.FIELD_NAME AS VARCHAR2(30)) 
   || ''', ' || CAST(FIELD_LIST_VW.FIELD_NAME AS VARCHAR2(30)) 
   || ' FROM ' || CAST(FIELD_LIST_VW.TABLE_NAME AS VARCHAR2(30))

對於分隔符，我提出以下建議：

' UNION '

以下是將所有查詢放在一起並使用視圖時的樣子：

SELECT LISTAGG(
   'SELECT ''' || CAST(FIELD_LIST_VW.TABLE_NAME AS VARCHAR2(30)) 
   || ''', ''' || CAST(FIELD_LIST_VW.FIELD_NAME AS VARCHAR2(30)) 
   || ''', ' || CAST(FIELD_LIST_VW.FIELD_NAME AS VARCHAR2(30)) 
   || ' FROM ' || CAST(FIELD_LIST_VW.TABLE_NAME AS VARCHAR2(30))
   , ' UNION ')
WITHIN GROUP (ORDER BY NULL) into varStringToExecute
FROM FIELD_LIST_VW;

上面的查詢所做的是將視圖中每個表和列所需的選擇查詢生成為字元串，UNION在所有查詢之間添加，並將整個字元串連接在一起。如果您需要查詢以特定順序建構，您可以ORDER BY使用LISTAGG.

您可以使用動態 SQL 執行查詢：

EXECUTE IMMEDIATE varStringToExecute;

你沒有說你需要用這些數據做什麼。如果您只需要將其返回給客戶端，您可能需要使用游標。這是關於如何實現這一目標的一組想法。

LISTAGG請注意is的返回值VARCHAR2，這意味著您的查詢文本在預設設置下限制為 4000 個字元。MAX_STRING_SIZE 可以通過設置參數將該限制增加到 32767 ，但我不知道這是否適合您的數據庫。如果你不能這樣做，有多種解決方法可以創建超過 4k 個字元的字元串。

如果此答案中的某些程式碼無法編譯，我們深表歉意。我目前無法訪問 Oracle。

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