如何连接两个表，其中表名存储在另一个表中的值？

Question

我有一些表格（例如 [Table1]，[Table2]，[Table3] 等），每个表格都有一个 [ID] 作为主键以及一个 DATETIME 类型的 RecTime 字段。

此外，我有一个表格 [Files]，它在一个 varbinary(max) 列中保存文件，并引用其他表格的名称和 ID。

[Table2]，[Table3] 和其他表格具有不同的结构，但与 [Table1] 完全共享 [ID] 和 [RecTime] 列。

下面是一个简单的示例来可视化数据。

DECLARE @Table1 as table (
      [ID] [bigint]
    , [RecTime] [datetime]
)
DECLARE @Table2 as table (
      [ID] [bigint]
    , [RecTime] [datetime]
)
DECLARE @Table3 as table (
      [ID] [bigint]
    , [RecTime] [datetime]
)

DECLARE @Files as table (
      [ID] [bigint]
    , [tblName] nvarchar(255) NULL
    , [tblID] bigint NULL
    , [BinaryData]  varbinary(max)
    /* and some other columns */
)

INSERT INTO @Table1 (
      [ID]
    , [RecTime]
)
          SELECT '1', DATEADD(day, (ABS(CHECKSUM(NEWID())) % 65530), 0)
UNION ALL SELECT '2', DATEADD(day, (ABS(CHECKSUM(NEWID())) % 65530), 0)
UNION ALL SELECT '3', DATEADD(day, (ABS(CHECKSUM(NEWID())) % 65530), 0)
UNION ALL SELECT '4', DATEADD(day, (ABS(CHECKSUM(NEWID())) % 65530), 0)
UNION ALL SELECT '5', DATEADD(day, (ABS(CHECKSUM(NEWID())) % 65530), 0)

INSERT INTO @Table2 (
      [ID]
    , [RecTime]
)
          SELECT '11', DATEADD(day, (ABS(CHECKSUM(NEWID())) % 65530), 0)
UNION ALL SELECT '12', DATEADD(day, (ABS(CHECKSUM(NEWID())) % 65530), 0)
UNION ALL SELECT '13', DATEADD(day, (ABS(CHECKSUM(NEWID())) % 65530), 0)
UNION ALL SELECT '14', DATEADD(day, (ABS(CHECKSUM(NEWID())) % 65530), 0)
UNION ALL SELECT '15', DATEADD(day, (ABS(CHECKSUM(NEWID())) % 65530), 0)

INSERT INTO @Table3 (
      [ID]
    , [RecTime]
)
          SELECT '21', DATEADD(day, (ABS(CHECKSUM(NEWID())) % 65530), 0)
UNION ALL SELECT '22', DATEADD(day, (ABS(CHECKSUM(NEWID())) % 65530), 0)
UNION ALL SELECT '23', DATEADD(day, (ABS(CHECKSUM(NEWID())) % 65530), 0)
UNION ALL SELECT '24', DATEADD(day, (ABS(CHECKSUM(NEWID())) % 65530), 0)
UNION ALL SELECT '25', DATEADD(day, (ABS(CHECKSUM(NEWID())) % 65530), 0)

INSERT INTO @Files (
      [ID]
    , [tblName]
    , [tblID]
    , [BinaryData]
)
          SELECT '1', 'Table1', '1', 0x010203040506
UNION ALL SELECT '2', 'Table1', '2', 0x010203040506
UNION ALL SELECT '3', 'Table1', '2', 0x010203040506
UNION ALL SELECT '4', 'Table1', '3', 0x010203040506
UNION ALL SELECT '5', 'Table1', '4', 0x010203040506
UNION ALL SELECT '6', 'Table1', '5', 0x010203040506
UNION ALL SELECT '7', 'Table1', '5', 0x010203040506

UNION ALL SELECT '8', 'Table2', '11', 0x010203040506
UNION ALL SELECT '9', 'Table2', '11', 0x010203040506
UNION ALL SELECT '10', 'Table2', '12', 0x010203040506
UNION ALL SELECT '11', 'Table2', '13', 0x010203040506
UNION ALL SELECT '12', 'Table2', '14', 0x010203040506
UNION ALL SELECT '13', 'Table2', '12', 0x010203040506
UNION ALL SELECT '14', 'Table2', '15', 0x010203040506

UNION ALL SELECT '15', 'Table3', '21', 0x010203040506
UNION ALL SELECT '16', 'Table3', '22', 0x010203040506
UNION ALL SELECT '17', 'Table3', '24', 0x010203040506
UNION ALL SELECT '18', 'Table3', '23', 0x010203040506
UNION ALL SELECT '19', 'Table3', '25', 0x010203040506
UNION ALL SELECT '20', 'Table3', '25', 0x010203040506
UNION ALL SELECT '21', 'Table3', '21', 0x010203040506

SELECT * FROM @Table1
SELECT * FROM @Table2
SELECT * FROM @Table3

SELECT * FROM @Files

如何将 [Files] 表与其他表联接，其中 Name 和 ID 来自 '[Files]' 表中的值？

我需要从 [Files] 表获取 [BinaryData]，并从 [Files] 表中引用相应表中的 [RecTime]。

实际问题是 [Table1]、[Table2] 和 [Table3] 不是唯一 被引用到 [Files] 表中的表格。可能会创建新表格，其二进制数据必须存储在 [Files] 表中。

因此，我正在寻找一种“动态”地将它们连接起来的方法。

P.S. 我不是该系统的创建者，也无法对其进行任何结构更改，只是尝试解决这个问题。

任何帮助都将不胜感激。

- armen

---

选择 tblName，BinaryData 和 RecTime 从文件 f 加入 table1 t 在 f.tblID = t.id 的情况下 其中 tblName = 'Table1' 联合全部 选择 tblName，BinaryData 和 RecTime 从文件 f 加入 table2 t 在 f.tblID = t.id 的情况下 其中 tblName = 'Table2' 联合全部 选择 tblName，BinaryData 和 RecTime 从文件 f 加入 table3 t 在 f.tblID = t.id 的情况下 其中 tblName = 'Table3' - Chintan Udeshi

你尝试过动态查询并在最后执行它了吗？ - Lali

我认为你的问题是模式设计中的一个。显然，实体之间存在一种关系，但并没有得到（适当）表示。解决方案是否必须使用所描述的模式/数据，或者使用实现相同意图的备用数据库设计的解决方案可以接受？ - Bohemian

你的示例基于3个表，实际上可能有多少张表（例如超过100个）？ - pacreely

嗨，请检查这个链接 https://dev59.com/81_Va4cB1Zd3GeqPRj8r 由@Harun Prasad回答 - Varun Malhotra

这个问题已经在这里解决了：https://dev59.com/81_Va4cB1Zd3GeqPRj8r - Varun Malhotra

Answer 1

一种方法是创建一个包含所有表数据的CTE（当然，使用动态SQL来创建它），然后从文件左连接该CTE进行选择。

这种方法使得动态SQL的编写和维护相当简单，并且生成的SQL语句也非常简单。

DECLARE @SQL varchar(max) = ''
SELECT @SQL = @SQL +' UNION ALL SELECT ID, 
                                       RecTime, 
                                       '''+ tblName +''' AS TableName 
                                FROM ' + tblName 
FROM (
    SELECT DISTINCT tblName FROM files    
) x
-- replace the first 'UNION ALL' with ';WITH allTables as ('
SELECT @SQL = STUFF(@SQL, 1, 11, ';WITH allTables as (') 
       +') 
       SELECT * 
       FROM Files 
       LEFT JOIN allTables ON(tblName = TableName AND tblId = allTables.Id)'

你从中获得的SQL语句是：

;WITH allTables as ( 
    SELECT ID, RecTime, 'Table1' AS TableName 
    FROM Table1 
    UNION ALL  
    SELECT ID, RecTime, 'Table2' AS TableName 
    FROM Table2 
    UNION ALL  
    SELECT ID, RecTime, 'Table3' AS TableName 
    FROM Table3
 ) 
 SELECT * 
 FROM Files 
 LEFT JOIN allTables ON(tblName = TableName AND tblId = allTables.Id)

执行它的方法如下：

EXEC(@SQL)

结果：

ID    tblName   tblID   BinaryData  ID    RecTime                 TableName
1     Table1    1       123456      1       31.03.2060 00:00:00   Table1
2     Table1    2       123456      2       03.12.1997 00:00:00   Table1
3     Table1    2       123456      2       03.12.1997 00:00:00   Table1
4     Table1    3       123456      3       02.07.2039 00:00:00   Table1
5     Table1    4       123456      4       17.06.1973 00:00:00   Table1
6     Table1    5       123456      5       06.12.2076 00:00:00   Table1
7     Table1    5       123456      5       06.12.2076 00:00:00   Table1
8     Table2    1       123456      NULL    NULL                    NULL
9     Table2    3       123456      NULL    NULL                    NULL
10    Table2    3       123456      NULL    NULL                    NULL
11    Table2    4       123456      NULL    NULL                    NULL
12    Table2    5       123456      NULL    NULL                    NULL
13    Table2    5       123456      NULL    NULL                    NULL
14    Table2    5       123456      NULL    NULL                    NULL
15    Table3    1       123456      NULL    NULL                    NULL
16    Table3    1       123456      NULL    NULL                    NULL
17    Table3    1       123456      NULL    NULL                    NULL
18    Table3    3       123456      NULL    NULL                    NULL
19    Table3    3       123456      NULL    NULL                    NULL
20    Table3    3       123456      NULL    NULL                    NULL
21    Table3    4       123456      NULL    NULL                    NULL

Live demo on rextester

- Zohar Peled

Answer 2

一种解决方案是使用一个游标，针对@Files表中的每一行执行一些动态SQL：

-- Copy table variables into temporary tables so they can be referenced from dynamic SQL
SELECT * INTO #Table1 FROM @Table1;
SELECT * INTO #Table2 FROM @Table2;
SELECT * INTO #Table3 FROM @Table3;

-- Create a temporary table for storing the results
CREATE TABLE #results (
      [ID] [bigint]
    , [tblName] nvarchar(255) NULL
    , [tblID] bigint NULL
    , [BinaryData]  varbinary(max)
    , [RecTime] [datetime]
);

-- Declare placeholders and cursor
DECLARE @ID bigint;
DECLARE @tblName nvarchar(255);
DECLARE @tblID bigint;
DECLARE @BinaryData varbinary(max);
DECLARE @RecTime datetime;
DECLARE @sql nvarchar(max);
DECLARE @params nvarchar(max);

DECLARE files_cursor CURSOR FOR  
SELECT ID, tblName, tblID, BinaryData
FROM @Files

-- Loop over all rows in the @Files table
OPEN files_cursor   
FETCH NEXT FROM files_cursor INTO @ID, @tblName, @tblID, @BinaryData

WHILE @@FETCH_STATUS = 0   
BEGIN   
   -- Find the referenced table row and extract its RecTime.
   SET @RecTime = NULL;
   SET @sql = CONCAT(
       'SELECT @RecTime = RecTime FROM #', @tblName, ' WHERE ID = ', @tblID);
   SET @params = '@RecTime datetime out';

   EXEC SP_EXECUTESQL @sql, @params, @RecTime out;

   -- Add result
   INSERT INTO #results (ID, tblName, tblID, BinaryData, RecTime)
   VALUES (@ID, @tblName, @tblID, @BinaryData, @RecTime); 

   FETCH NEXT FROM files_cursor INTO @ID, @tblName, @tblID, @BinaryData;
END   

-- Finalise
CLOSE files_cursor;
DEALLOCATE files_cursor;

-- Display the results from temporary table
SELECT * FROM #results;

Online demo: http://rextester.com/DXCK86463

- Steve Chambers

---

嗨Steve，我有一个问题，为什么在游标的结果中Table2和Table3的RecTime字段是*NULL*？例如，在tblName为Table2和tblId为1时，RecTime字段的值应该是12.12.1934 00:00:00，我认为是这样的，也许我没有理解到什么。 - Phi

从表格数据来看，Table1的ID为1-5，Table2的ID为11-15，Table3的ID为21-25。但是在Files表中的tblIDs都是1-5，这就是为什么在Table2和Table3中找不到任何内容的原因。 - Steve Chambers

一些与性能相关的改进：

• 在最后删除临时表
• 如果要使用游标，使用快速前进只读游标
• 我会创建一个更长的 SQL 文本，其中包含用于结果集的 union all，并仅使用游标来创建一个 SQL 语句。然后在最后运行该语句。这样只需要一次调用，而不是针对每一行都调用一次。

不管怎样，干得好。 - Morzel

Answer 3

这是实现上述操作的最简单的方法。无需进行循环等操作。您需要动态代码，因为表格可以随时添加。 注意：在您的Files表的示例数据中，tblId似乎存在错误数据？

因此，我正在更改您的数据以匹配相应的表ID。 架构：

CREATE TABLE Table1   (
      [ID] [bigint]
    , [RecTime] [datetime]
)
CREATE TABLE Table2 (
      [ID] [bigint]
    , [RecTime] [datetime]
)
CREATE TABLE Table3 (
      [ID] [bigint]
    , [RecTime] [datetime]
)

CREATE TABLE Files (
      [ID] [bigint]
    , [tblName] nvarchar(255) NULL
    , [tblID] bigint NULL
    , [BinaryData]  varbinary(max)
    /* and some other columns */
)

INSERT INTO Table1 (
      [ID]
    , [RecTime]
)
          SELECT '1', DATEADD(day, (ABS(CHECKSUM(NEWID())) % 65530), 0)
UNION ALL SELECT '2', DATEADD(day, (ABS(CHECKSUM(NEWID())) % 65530), 0)
UNION ALL SELECT '3', DATEADD(day, (ABS(CHECKSUM(NEWID())) % 65530), 0)
UNION ALL SELECT '4', DATEADD(day, (ABS(CHECKSUM(NEWID())) % 65530), 0)
UNION ALL SELECT '5', DATEADD(day, (ABS(CHECKSUM(NEWID())) % 65530), 0)

INSERT INTO Table2 (
      [ID]
    , [RecTime]
)
          SELECT '11', DATEADD(day, (ABS(CHECKSUM(NEWID())) % 65530), 0)
UNION ALL SELECT '12', DATEADD(day, (ABS(CHECKSUM(NEWID())) % 65530), 0)
UNION ALL SELECT '13', DATEADD(day, (ABS(CHECKSUM(NEWID())) % 65530), 0)
UNION ALL SELECT '14', DATEADD(day, (ABS(CHECKSUM(NEWID())) % 65530), 0)
UNION ALL SELECT '15', DATEADD(day, (ABS(CHECKSUM(NEWID())) % 65530), 0)

INSERT INTO Table3 (
      [ID]
    , [RecTime]
)
          SELECT '21', DATEADD(day, (ABS(CHECKSUM(NEWID())) % 65530), 0)
UNION ALL SELECT '22', DATEADD(day, (ABS(CHECKSUM(NEWID())) % 65530), 0)
UNION ALL SELECT '23', DATEADD(day, (ABS(CHECKSUM(NEWID())) % 65530), 0)
UNION ALL SELECT '24', DATEADD(day, (ABS(CHECKSUM(NEWID())) % 65530), 0)
UNION ALL SELECT '25', DATEADD(day, (ABS(CHECKSUM(NEWID())) % 65530), 0)

INSERT INTO Files (
      [ID]
    , [tblName]
    , [tblID]
    , [BinaryData]
)
          SELECT '1', 'Table1', '1', 0x010203040506
UNION ALL SELECT '2', 'Table1', '2', 0x010203040506
UNION ALL SELECT '3', 'Table1', '2', 0x010203040506
UNION ALL SELECT '4', 'Table1', '3', 0x010203040506
UNION ALL SELECT '5', 'Table1', '4', 0x010203040506
UNION ALL SELECT '6', 'Table1', '5', 0x010203040506
UNION ALL SELECT '7', 'Table1', '5', 0x010203040506

UNION ALL SELECT '8', 'Table2', '11', 0x010203040506
UNION ALL SELECT '9', 'Table2', '11', 0x010203040506
UNION ALL SELECT '10', 'Table2', '12', 0x010203040506
UNION ALL SELECT '11', 'Table2', '13', 0x010203040506
UNION ALL SELECT '12', 'Table2', '14', 0x010203040506
UNION ALL SELECT '13', 'Table2', '12', 0x010203040506
UNION ALL SELECT '14', 'Table2', '15', 0x010203040506

UNION ALL SELECT '15', 'Table3', '21', 0x010203040506
UNION ALL SELECT '16', 'Table3', '22', 0x010203040506
UNION ALL SELECT '17', 'Table3', '24', 0x010203040506
UNION ALL SELECT '18', 'Table3', '23', 0x010203040506
UNION ALL SELECT '19', 'Table3', '25', 0x010203040506
UNION ALL SELECT '20', 'Table3', '25', 0x010203040506
UNION ALL SELECT '21', 'Table3', '21', 0x010203040506

现在是您的动态查询部分：

DECLARE @QRY VARCHAR(MAX)='', @Tables VARCHAR(MAX)='';

--Capturing List of Table names for selecting RecTime
SELECT @Tables = @Tables+ tblName+'.RecTime,' FROM (
SELECT DISTINCT tblName FROM Files
)A

--To remove last comma
SELECT @Tables = SUBSTRING(@Tables,1, LEN(@Tables)-1)

--Preparing Dynamic Qry
SELECT @QRY = '
SELECT Files.ID,Files.BinaryData
,COALESCE('+@Tables+') AS RecTime
FROM Files '

SELECT @QRY =@QRY+ JOINS FROM (
SELECT  DISTINCT '
LEFT JOIN '+ tblName + ' ON Files.tblID = '+tblName+'.ID AND Files.tblName= '''+tblName+''''
as JOINS
FROM Files
)A

print @QRY

EXEC( @QRY)

如果想查看 @Qry 包含的内容

/*
Print Output:

SELECT Files.ID,Files.BinaryData
,COALESCE(Table1.RecTime,Table2.RecTime,Table3.RecTime) AS RecTime
FROM Files 
LEFT JOIN Table1 ON Files.tblID = Table1.ID AND Files.tblName= 'Table1'
LEFT JOIN Table2 ON Files.tblID = Table2.ID AND Files.tblName= 'Table2'
LEFT JOIN Table3 ON Files.tblID = Table3.ID AND Files.tblName= 'Table3'

*/

- Shakeer Mirza

---

太棒了！谢谢你指出我的错误数据。我会纠正它以备将来使用。 - armen

Answer 4

尝试以下操作。

Select res.* , F.* From Files F
Left join
(
Select 'table1' as tablename, a.* From table1 a
Union
Select 'table2' as tablename, b.* From table2 b
Union
Select 'table3' as tablename, c.* From table3 c
)Res
On res.tablename = F.tblname

- Kalyan

---

正如我之前提到的，[Table1]、[Table2]和[Table3]并不是唯一的表格。新的表格可能会被创建，其中二进制数据必须存储在[Files]表中。 - armen

将此行内查询设置为视图，然后在 Files 表中添加新表时更改视图。编写动态查询可能会影响性能。 - Kalyan

我发现这很有用。谢谢。 - Sig

Answer 5

这种设计只是一种在ER中建模层次结构的方法。你基本上有一个基于表名（例如Table1，Table2等）的物理分区表。因此，连接这些表的最简单方法是创建一个分区视图，然后加入它。

对于你的例子，你只需要执行以下操作：

CREATE VIEW vmAll AS
SELECT 'Table1' AS 'tblName', [ID], [RecTime] FROM Table1
UNION ALL
SELECT 'Table2' AS 'tblName', [ID], [RecTime] FROM Table2
UNION ALL
SELECT 'Table3' AS 'tblName', [ID], [RecTime] FROM Table3;
GO

现在只需像往常一样将其与Files表连接（记得也要指定分区字段）：

例如：

SELECT 
      F.[ID] 
    , F.[tblName]
    , F.[tblID]
    , F.[BinaryData] 
    , A.RecTime
    FROM [Files] F
LEFT OUTER JOIN vmAll A ON
    F.[ID] = A.[ID] AND
    F.tblName = A.tblName

给出预期结果：

注意一件重要的事情：由于它是一个分区视图，SQL Server能够执行分区消除，从而显著加快联接速度（这里正确的术语应该是表消除）。

例如，先前的执行计划如下：

如果我们在分区列上添加一个过滤谓词：

SELECT 
      F.[ID] 
    , F.[tblName]
    , F.[tblID]
    , F.[BinaryData] 
    , A.RecTime
    FROM [Files] F
LEFT OUTER JOIN vmAll A ON
    F.[ID] = A.[ID] AND
    F.tblName = A.tblName

WHERE A.tblName = 'Table1'

我们将获得这个执行计划（注意两个表根本没有被扫描）：

当然，为了使用分区视图，你首先需要能够创建它。你可以通过编程方式查找特定字段并创建查询来实现：

;WITH CTE AS
(
    SELECT C.object_id FROM sys.columns C
    INNER JOIN sys.objects O ON C.object_id = O.object_id
    WHERE 
        (C.[name] = 'ID' OR C.[name] = 'RecTime')
        AND O.[type] = 'U'
    GROUP BY C.object_id
    HAVING COUNT(*) = 2
)
SELECT OBJECT_NAME(object_id), object_id FROM CTE;

- Francesco Cogno

Answer 6

请查看以下链接，这可能会解决您的问题。

MySQL连接表，其中表名是另一个表的字段, MySQL连接表，其中表名是另一个表的字段

- Jaimin Dave

Answer 7

如果你只有几个表，那么你可以这样做，它可能会稍微快一些，因为它避免了动态SQL。

如果你无法确定将有多少表或者表的数量太多，那么可以在这里查看其他解决方案（我喜欢Steve Chamber的解决方案）。

SELECT F.*, RecTime = 
      CASE tblName  
         WHEN 'Table1' THEN COALESCE(T1.RecTime, NULL)
         WHEN 'Table2' THEN COALESCE(T2.RecTime, NULL)
         WHEN 'Table3' THEN COALESCE(T3.RecTime, NULL)
         ELSE NULL
      END 
FROM @Files F
LEFT JOIN @Table1 T1 ON F.tblID = T1.ID
LEFT JOIN @Table2 T2 ON F.tblID = T2.ID
LEFT JOIN @Table3 T3 ON F.tblID = T3.ID

演示：http://rextester.com/FWWD90002

- shankar_pratap