主页 / dba / 问题 / 231866
Accepted
Dan
Asked: 2019-03-12 11:30:48 +0800 CST

比较两个表之间的两对列并从第三个表返回列

  • 772

我有三张桌子：

表格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 中的列之间的所有可能共享中选择符合标准的所有值。完成后看起来像这样：table1table2

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 返回任何内容，而带有编辑以包含左连接的查询确实如此。

sql-server-2008

1 个回答

  1. Best Answer

    我有一个有效的查询。它连接了 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 万次计算。有人有任何其他建议可以帮助我吗？

    • 0

相关问题