具有多個規範化欄位的複雜查詢

我們有一個相當簡單的表結構，但每個表有很多欄位（40+）。這些數據最初是在純文字、使用者可讀的表中生成的，但隨後在安裝用於生產之前將其轉換為性能更高、更易於查詢的表。

我們所做的是，在可能且合理的情況下，我們將某些欄位轉換為列舉值，並在 MasterEnum 表中跟踪列舉。40 個左右的列舉欄位中通常有 20-25 個。

範例表結構：

純文字版本：

| 零件號 | 製造商 | 一些數據 | 更多數據 | 一些文本數據...
----------------------------------------------------------------------------------
| 1x9kdah | 小發明公司 | 這是數據 | 其他數據 | ThisStaysText ...
| 8xcjkzh | 小工具公司 | 更多數據 | 其他數據2 | ThisTooStaysText ...

目標表樣本結構：

| 零件號 | 製造商 | 一些數據 | 更多數據 | 一些文本數據...
-------------------------------------------------------------------------------------
| 1x9kdah | 1 | 1 | 1 | ThisStaysText ...
| 8xcjkzh | 2 | 2 | 2 | ThisTooStaysText ...

主列舉表結構

| 欄位名 | 輸入文本 | 價值程式碼 |
---------------------------------------------
| 製造商 | 小發明公司 | 1 |
| 製造商 | 小工具公司 | 2 |
| 一些數據 | 這是數據 | 1 |
| 一些數據 | 更多數據 | 2 |
| 更多數據 | 其他數據 | 1 |
| 更多數據 | 其他數據2 | 2 |

我們有一種方法可以進行這種翻譯，而且效果很好；但是它有點慢，因為所有處理都是通過 Spring/Hibernate 在 Java 中完成的。我的問題是：

有沒有辦法編寫一個可以完成上述所有翻譯的查詢？（請注意，我們有一種以程式方式跟踪欄位定義的好方法，因此動態生成複雜的 SQL 查詢不是問題）。如果無法在單個查詢中執行此操作，我將如何構造查詢以迭代各個欄位並確保在翻譯發生時插入新表的數據仍然與正確的行相關聯？

請注意，假設目標表在程序開始時始終為空是安全的。

正如其他人指出的那樣，這是一個非常糟糕的主意。不過，如果您堅持，SQL 並不是非常複雜：

CREATE TABLE RawData
(
PartNumber  VARCHAR(30) NOT NULL PRIMARY KEY,
Manufacturer    VARCHAR(30) NOT NULL,
Data1   VARCHAR(30),
Data2   VARCHAR(30),
Data3   VARCHAR(30)
)

CREATE TABLE Translations
(
FieldName   VARCHAR(30) NOT NULL,
Value   VARCHAR(30) NOT NULL,
PRIMARY KEY (FieldName, Value),
ID  INT NOT NULL
UNIQUE (FieldName, ID)
)

CREATE TABLE CleanData
(
PartNumber  VARCHAR(30) NOT NULL PRIMARY KEY,
Manufacturer    VARCHAR(30) NOT NULL,
Data1   VARCHAR(30),
Data2   VARCHAR(30),
Data3   VARCHAR(30)
)

INSERT INTO CleanData (PartNumber, Manufacturer, Data1, Data2, Data3)
   SELECT
       RD.PartNumber,
       TMfr.ID AS Manufacturer,
       TDt1.ID AS Data1,
       TDt2.ID AS Data2,
       TDt3.ID AS Data3
   FROM
       RawData AS RD
       LEFT JOIN Translations AS TMfr ON RD.Manufacturer = TMfr.Value AND TMfr.FieldName = 'Manufacturer'
       LEFT JOIN Translations AS TDt1 ON RD.Data1        = TDt1.Value AND TDt1.FieldName = 'Data1'
       LEFT JOIN Translations AS TDt2 ON RD.Data2        = TDt2.Value AND TDt2.FieldName = 'Data2'
       LEFT JOIN Translations AS TDt3 ON RD.Data3        = TDt3.Value AND TDt3.FieldName = 'Data3'

擴展到完整的欄位集。願科德憐憫你的靈魂。

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