主页 / dba / 问题 / 40657
Accepted
Kahn
Asked: 2013-04-23 23:21:23 +0800 CST

获取连接中使用的列的名称？

  • 772

这可能是一个有点奇怪的问题：

我们项目的数据模型有多个表和多个列，其中任何一个都可以用来连接一个设置表。这里的问题是如何获取用于连接到 OR 连接中的设置表的列的名称？

例如，假设查询如下所示：

SELECT FIRST.ID, SEC.ID, TRD.ID, FRTH.ID, SETS.ID, SETS.VALUE
FROM ROOTTABLE FIRST
LEFT JOIN SECONDTABLE SEC ON SEC.SECDONTABLE_ID = FIRST.SECONDTABLE_ID
LEFT JOIN THIRDTABLE TRD ON TRD.THIRDTABLE_ID = FIRST.THIRDTABLE_ID
LEFT JOIN FOURTHTABLE FRTH ON FRTH.FOURTHTABLE_ID = TRD.FOURTHTABLE_ID
-- etc...
LEFT JOIN SETTINGTABLE SETS 
   ON (SETS.ID = FIRST.ONESETTING_ID OR SETS.ID = FIRST.SECONDSETTING_ID
      OR SETS.ID = FIRST.THIRDSETTING_ID OR SETS.ID = TRD.RANDSETTING_ID
      OR SETS.ID = FRTH.ONESETTING_ID
-- etc...
   )

现在，这是真正发生的事情的一个非常经过分析的例子。我们可能有几十个表，每个表都有几个连接列到 SETTINGTABLE。

显然，仅对每个 table.column 进行单独的连接就会显示所使用的列。或者，您可以在选择中使用 CASE 子句。但任何一个都很棘手，需要大量的手动输入。即使只是在选择中使用所有连接的 ID 列也可以让您比较 ID，但由于结果需要非技术人员可读，所以这不会有太大帮助。

那么有没有一种方法可以使用一些 sys 对象或类似的对象来简单地返回实际用于连接到 SETTINGTABLE 的列名？设计就是这样，不会改变。所以我只是想找到最好的方法来处理我们现有的东西。这将显着加快我们对某些脚本、查询和数据的诊断速度。

谢谢！

sql-server join

1 个回答

  1. Best Answer

    没有sys对象或 DMV 会为您提供此类信息。您必须自己构建它，为此您可以查看下面的示例。

    让我们准备一些表格和虚拟数据。

    CREATE TABLE ROOTTABLE (FIRST_ID INT, SECOND_ID INT, THIRD_ID INT, ONESETTING_ID INT);
CREATE TABLE SECONDTABLE (SECOND_ID INT, SECONDSETTING_ID INT);
CREATE TABLE THIRDTABLE (THIRD_ID INT, FOURTH_ID INT, RANDSETTING_ID INT, THIRDSETTING_ID INT);
CREATE TABLE FOURTHTABLE (FOURTH_ID INT, ONESETTING_ID INT);
CREATE TABLE SETTINGTABLE (ID INT, VALUE Varchar(10));

INSERT ROOTTABLE VALUES (1,2,3,15),(1,1,1,12),(11,5,6,12);
INSERT SECONDTABLE VALUES (1,11),(2,15),(5,13);
INSERT THIRDTABLE VALUES (1,5,6,15),(6,7,8,15),(3,7,8,15);
INSERT FOURTHTABLE VALUES (5,15),(6,16),(7,17),(1,18);
INSERT SETTINGTABLE VALUES
  (11,'one'),
  (12,'two'),
  (13,'three'),
  (14,'four'),
  (15,'five'),
  (16,'six'),
  (17,'seven');

    下面是带有 MATCHES 列的查询，显示从 (SETS.ID = ?) 满足 JOIN 条件的位置。

    SELECT FIRST.FIRST_ID, SEC.SECOND_ID, TRD.THIRD_ID, FRTH.FOURTH_ID, SETS.ID, SETS.VALUE,
       STUFF((SELECT ','+SOURCE_TABLE
              FROM (VALUES
                    ('ROOTTABLE',   FIRST.ONESETTING_ID),  -- 1
                    ('SECONDTABLE', SEC.SECONDSETTING_ID), -- 2
                    ('THIRDTABLE',  TRD.THIRDSETTING_ID),  -- 3
                    ('THIRDTABLE',  TRD.RANDSETTING_ID),   -- 4
                    ('FOURTHTABLE', FRTH.ONESETTING_ID))   -- 5
                   X (SOURCE_TABLE, SOURCE_ID)
              WHERE X.SOURCE_ID = SETS.ID
              FOR XML PATH('')),1,1,'') MATCHES
FROM ROOTTABLE FIRST
LEFT JOIN SECONDTABLE SEC ON SEC.SECOND_ID = FIRST.SECOND_ID
LEFT JOIN THIRDTABLE TRD ON TRD.THIRD_ID = FIRST.THIRD_ID
LEFT JOIN FOURTHTABLE FRTH ON FRTH.FOURTH_ID = TRD.FOURTH_ID
LEFT JOIN SETTINGTABLE SETS 
     ON (SETS.ID = FIRST.ONESETTING_ID    -- 1
      OR SETS.ID = SEC.SECONDSETTING_ID   -- 2
      OR SETS.ID = TRD.THIRDSETTING_ID    -- 3
      OR SETS.ID = TRD.RANDSETTING_ID     -- 4
      OR SETS.ID = FRTH.ONESETTING_ID);   -- 5

    VALUES请注意，我已经在行构造函数和子句中列出了可以匹配 (1-5) 的源，ON以便您可以遵循该模式。

    结果：

    | FIRST_ID | SECOND_ID | THIRD_ID | FOURTH_ID | ID | VALUE |                          MATCHES |
-----------------------------------------------------------------------------------------------
|        1 |         2 |        3 |         7 | 15 |  five | ROOTTABLE,SECONDTABLE,THIRDTABLE |
|        1 |         2 |        3 |         7 | 17 | seven |                      FOURTHTABLE |
|        1 |         1 |        1 |         5 | 11 |   one |                      SECONDTABLE |
|        1 |         1 |        1 |         5 | 12 |   two |                        ROOTTABLE |
|        1 |         1 |        1 |         5 | 15 |  five |           THIRDTABLE,FOURTHTABLE |
|       11 |         5 |        6 |         7 | 12 |   two |                        ROOTTABLE |
|       11 |         5 |        6 |         7 | 13 | three |                      SECONDTABLE |
|       11 |         5 |        6 |         7 | 15 |  five |                       THIRDTABLE |
|       11 |         5 |        6 |         7 | 17 | seven |                      FOURTHTABLE |

    一些注意事项：

    1. JOIN 中的多个ORs 意味着SETS.ID可以与任何来源匹配 - 查询显示它们全部以逗号分隔。
    2. 具有前导列的第 3-5 行(1, 1, 1, 5)与 SETS.ID 匹配三次。

    SQL小提琴

    为清楚起见，此其他SQL Fiddle*SETTING_ID显示了用于匹配 SETS.ID的附加列。

    • 2

相关问题