Как присоединиться к таблицам, имена которых хранятся как значения в другой таблице?

Ответ 1

Это самый простой способ, чтобы сделать это. Нет необходимости зацикливания или любой вещи. Вам нужен динамический код, поскольку таблицы можно добавлять в любое время.

Примечание: В ваших данных образца для таблицы Files, кажется, есть неправильные данные в tblId?

Итак, я меняю ваши данные на соответствие идентификаторам соответствующим таблицам.

Схема:

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('[email protected]+') AS RecTime
FROM Files '

SELECT @QRY [email protected]+ 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'

*/

Ответ 2

Один из подходов состоит в создании cte, который будет содержать все данные таблиц (конечно, используя динамический sql для его создания), а затем выберите из файлов, оставшихся в join, что cte.

Таким образом, динамический sql довольно прост в написании и обслуживании, а также сделанное им выражение sql it очень просто:

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)'

С помощью этой статистики вы получите следующее:

;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

Живая демонстрация на реестре

Ответ 3

Одним из решений является использование курсора , который выполняет некоторый динамический SQL для каждой строки в таблице @Files:

-- 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;

Онлайн-демо: http://rextester.com/DXCK86463

Ответ 4

Этот проект является всего лишь способом моделирования иерархии в 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;

Ответ 5

Попробуйте следующее.

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

Ответ 6

Если у вас только несколько таблиц, вы можете просто сделать это, это может быть немного быстрее, потому что оно позволяет избежать динамического SQL.

Посмотрите на другие решения (мне нравится решение для камеры Steve) здесь, если вы не можете сказать, сколько таблиц будет или будет слишком много.

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

Ответ 7

Посмотрите ниже ссылки. это может решить вашу проблему.

MySQL соединяет таблицы, где имя таблицы является полем другой таблицы, MySQL соединяет таблицы, где имя таблицы является полем другой таблицы