Sql-Server-2008

比較兩個表之間的兩對列並從第三個表返回列

  • November 5, 2020

我有三張桌子：

表格1

table1可以有 50K 到 800k 的唯一行。我的意思是每行中所有值的組合都是唯一的，儘管有時某些列會匹配。除了列之外，整行都可能匹配NAME，但這是非常非常不可能的。NAME 列將始終是唯一的。

該NAME列是 type varchar(20)。每條記錄的其餘列都是 type varchar(6)，其中每兩列是一個集合對，表有多達 21 個集合對（即總共 43 列）。這是一個table1顯示 4 條記錄的一組對的範例（為了方便起見，我在這裡使用了單個字元，但不要忘記它們是 type varchar(6)）：

table1

NAME        pair1_1     pair1_2     ...up to pair21_1 - pair21_2
00001A      A           B       
00002A      A           A       
00003A      B           C       
00004A      A           B       
…up to 800k rows

表2

table2設置方式完全相同，只是它包含完全相同類型的完全不同的記錄（這裡可以有 1 到 200 行）

table2

NAME        pair1_1    pair1_2    ...up to pair21_1 - pair21_2
1234B       A          B
5678B       A          A
9101B       C          C
1213B       A          B        
…up to 200 rows

表3

table3與 in 中的單行相關聯，table2並且可以表徵為該單行 intable2與任何可能的行之間的比較的每個可能結果table1。最好通過與它相關的NAMEin來呼叫table2它（讓我們只使用第一個並呼叫它，table1234B因為如果 table2 中有 200 行，則有 200 個不同的關聯table3s）。

第 3 個表將包含 4 行。它有一NAME列是 avarchar(20)和 21 組 7 列（每列與 和 的不同對相關聯table1）table2。附加列res1_1是res1_2…res21_7type decimal(30,7)。

這是它的樣子：

table1234B

NAME        res1_1  res1_2  res1_3  res1_4  res1_5  res1_6  res1_7  ….res21_7
1234B       12.30   1.000   0.2500  1.000   2.000   2.10    25.00   ….

我想使用來自table1和的列對中的共享數據的組合table2（即它們如何匹配）作為從中選擇數據的條件table1234B（我將在下面展示其中的一些）。只會比較重合的對。pair1_1and pair1_2fromtable1將與 topair1_1和pair1_2from進行比較table2，pair2s 將僅與pair2s 進行比較，pair3s 僅與pair3s 進行比較等。因此，不會進行交叉對比較（例如pair1，永遠不會與 進行比較pair2）

在下面的範例中，pair1_1每個表中的欄位匹配 (A)，pair1_2每個表中的欄位匹配 (B)，但每個表的列之間的欄位不匹配。

NAME        Pair1_1     Pair1_2
00001A      A           B       (from table1)
1234B       A           B       (from table2)

所以我想 SELECT 說pair1_4fromtable1234B並將其與記錄 00001A 和 1234B 之間的比較相關聯。

如果表格是這樣的，我們可以看到所有 4 個欄位都匹配。

NAME        pair1_1     pair1_2
00001A      A           A       (from table1)
1234B       A           A       (from table2)

在這種情況下，也許我想pair1_1選擇table1234B

在這裡我們可以看到pair1_1intable1匹配兩個欄位 fromtable2但pair1_2fromtable1不匹配任何內容。

NAME        pair1_1     pair1_2
00001A      A           B       (from table1)
1234B       A           A       (from table2)

所以我想選擇say pair1_4fromtable1234B

以上只是列對中的數據可以在表之間共享的 14 種可能方式中的 3 種（它們也有多種方式在表之間不共享數據），但每對只有 7 種可能的列可供選擇table1234B。

我想從和table1234B的每組 2 中的列之間的所有可能共享中選擇符合標準的所有值。完成後看起來像這樣：table1``table2

1234BResult

NAME        RESULTPair1     …up to Resultpair21
00001A      12.30       (res1_1 from table3)        
00002B      1.000       (res1_2 from table3)                
00003C      25.00       (res1_7 from table3)        
00004A      1.000       (res1_4 from table3)
…up to 800K rows

這是我開始編輯的查詢。

SELECT t1.NAME as NAME, t3.pair1_4 as RESULTPair1
FROM table1 t1
LEFT OUTER JOIN table2 t2
ON (t1.pair1_1 = t2.pair1_1 AND t1.pair1_1 <> t1.pair1_2 AND t1.pair1_2 = t2.pair1_2 AND t2.pair1_1 <> t2.pair1_2)
LEFT OUTER JOIN table1234B t3
ON t2.NAME = t3.NAME

…以及該查詢的結果

NAME        RESULTPair1 
00001A      1.000       (res1_4 from table3)
00002B      NULL
00003A      NULL
00004A      1.000       (res1_4 from table3)

這更接近我正在尋找的內容，因為它使NAMEstable2具有不同的共享對來自 的其他值開放table1234B。我打算合併後的剩餘邏輯將返回其他適當的結果。

麻煩的是，這只會給我任何情況的結果，即 AB，AB。我需要將其擴展到 21 對和可能table3的 200 對，包括所有不同的可能結果（table3如果適用）。我可以處理所有共享邏輯（即pair1_1 = pairt2_2或pair1_1 <> pair2_2），它將其擴展到其餘情況以及可能更多table2我不知所措的記錄。我需要獲得table1所有 21 對中所有 800k 記錄的結果。

如果你和我待了這麼久並且理解所有的雞抓，我的問題是：

  1. 我將如何編輯上面的查詢以使用 aJOIN而不是 a WHERE？
  2. 是否有一種方法可以擴展它以有效地包含在欄位之間共享的其他可能場景的邏輯，table1以便table2我可以在新表中查看或儲存​​從table1單行到單行的所有 50-800k 結果table2？
  3. 我怎麼把它擴展到額外的 20 對？

編輯 在被問及與 table3 的連接後，我意識到該列需要編輯才能正常工作。我仍然無法弄清楚如何使查詢正常工作。我也編輯了查詢。我仍然在將其擴展到多個列時遇到了一些麻煩。建議使用 INTERSECT + EXCEPT 或 NOT EXISTS。我無法讓 INTERSECT 返回任何內容，而帶有編輯以包含左連接的查詢確實如此。

我有一個有效的查詢。它連接了 21 個派生表（比較的 21 對中的每一對都有一個）。它們是用 20 FULL OUTER JOINs 而不是LEFT JOINs 連接的，每個都有 14 個子查詢的集合和UNION ALLs 組合，所以我將給出一個帶有一些評論的一般形式。它在 5 秒內完成了 67,000 次比較。

SELECT COALESCE (t4.NAME, t5.NAME,...t25.NAME) AS NAME,
               t4.RESULTPair1, t5.RESULTPair2,...t25.RESULTPair21           

FROM ((SELECT t1.NAME AS NAME, t3.RESULTPair1_1 AS RESULTPair1
    FROM table1 t1
    INNER JOIN table2 t2
    ON (t1.pair1_1 = t1.pair1_2 AND    ---logic between table1 and table2 for AAAA
        t1.pair1_1 = t2.pair1_1 AND
        t1.pair1_1 = t2.pair1_2)
    INNER JOIN table1234B t3
    ON t2.NAME = t3.NAME) 

    UNION ALL

    (SELECT t1.NAME AS NAME, t3.RESULTPair1_2 AS RESULTPair1
    FROM table1 t1
    INNER JOIN table2 t2
    ON (---LOGIC FOR AAAB---)  ---I wont bore you with the rest of the matching logic unless someone is really interested
    INNER JOIN table1234B t3
    ON t2.NAME = t3.NAME) 

    ------Repeat the above query for each set of logic AAAB, AABA, ABAA, BAAA etc. with 13
    ------total UNION ALL (each set of logics will take care of every possible situation of
    ------sharing between the tables and each t1.NAME is different so UNION ALL will be more
    ------efficient, COALESCE for the NAME field returns a full column for NAME)

   UNION ALL

   (SELECT t1.NAME AS NAME, t3.RESULTPair1_7 AS RESULTPair1
   FROM OFFENDERS t1
   INNER JOIN table2 t2
   ON  (---LOGIC FOR AABB---)
   INNER JOIN table1234B t3
   ON t2.NAME = t3.NAME)) t4 --aabb

    ——-now move through the remaining result pairs up to 21 (ie derived table25)
FULL OUTER JOIN
    ——-subqueries for result set2
ON t4.NAME = t5.NAME
    ——-subqueries for resultsets 3-20
ON t23.NAME = t24.NAME
FULL OUTER JOIN

   ((SELECT t1.NAME AS NAME, t3.RESULTPair21_1 AS RESULTPair21
   FROM table1 t1
   INNER JOIN table2 t2
   ON  (---LOGIC FOR AAAA---)
   INNER JOIN table1234B t3
   ON t2.NAME = t3.NAME)  ---aaaa

   UNION ALL

   -------remaining sets of logic for RESULTPair21

   SELECT t1.NAME AS NAME, t3.RESULTPair21_7 AS RESULTPair21
   FROM table1 t1
   INNER JOIN table2 t2
   ON  (---LOGIC FOR AABB---)
   INNER JOIN table3 t3
   ON t2.NAME = t3.NAME)) t25 --aabb

ON t24.NAME = t25.NAME

這是一項正在進行的工作，所以如果我可以做些什麼來改進它，請告訴我！就像我在上面的評論中所說的那樣，我想我要設置另一個表來將 varchar(6) 欄位索引為整數，然後使用整數進行比較。所以現在 table1 和 table2 看起來像這樣：

table1
NAME        pair1_1     pair1_2     ...up to pair21_1 - pair21_2
00001A      1           2       
00002A      1           1       
00003A      2           3       
00004A      1           2       
…up to 800k rows 

table2
NAME        pair1_1    pair1_2    ...up to pair21_1 - pair21_2
1234B       1          2
5678B       1          1
9101B       3          3
1213B       1          2        
…up to 200 rows

…以及使比較更快的附加索引，以防我想檢索實際的數據集：

intIndex
pairs    code
A        1
B        2
C        3
D        4
...
up to all possible varchar(6) character strings

我添加了索引表，現在可以比較整數而不是 varchar(6) 列。67,000 table1 行查詢在 5 秒內為 varchar(6) 列生成 21 列，而對於整數列則為 4 秒。140 萬次計算。有人有任何其他建議可以幫助我嗎？

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

相關問答

Sql-Server

當 ANSI_WARNINGS 關閉時，即使使用 nvarchar，也無法將其插入到我的特殊字元表中

  • August 18, 2022
檢視問答
Sql-Server

為什麼 SERIALIZABLE 級別不會為 SQL Server 中的其他會話鎖定表

  • August 12, 2022
檢視問答
Sql-Server

使用者“NT AUTHORITYANONYMOUS LOGIN”登錄失敗

  • July 13, 2022
檢視問答
Sql-Server-2008

SQL Server 安裝失敗 - 缺少記憶體的 MSI 文件

  • April 6, 2022
檢視問答
Sql-Server-2008

即使索引存在於表中，查詢計劃仍顯示缺少索引

  • March 23, 2022
檢視問答
Sql-Server-2008

參數嗅探 vs 變數 vs 重新編譯 vs 優化未知

  • March 23, 2022
檢視問答