sql - resueltos - ¿Cómo unirse a tablas cuyos nombres se almacenan como valores en otra tabla?

select sql ejemplos

Question

Ι tengo algunas tablas (por ejemplo, [Table1] , [Table2] , [Table3] y así sucesivamente) con una [ID] como clave principal y RecTime como DATETIME en cada una.

También tiene una [Files] tabla que contiene archivos en una columna varbinary(max) , y se refiere a las otras tablas que tienen sus nombres e ID.

[Table2] , [Table3] y otros tienen una estructura diferente, pero comparten las columnas [ID] y [RecTime] exactamente como en [Table1]

A continuación se muestra una muestra rápida para visualizar datos.

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

¿Cómo puedo unir la tabla [Files] a otras tablas, cuyo Name e ID derivan de un valor en la tabla ''[Archivos]''?

Necesito [BinaryData] de la tabla [Files] y [RecTime] de la tabla correspondiente en la tabla [Files] .

El problema real es que [Table1] , [Table2] y [Table3] no son las únicas tablas a las que se hace referencia en la tabla [Files] . Se pueden crear nuevas tablas, para las cuales los datos binarios deben almacenarse en la tabla [Files] .

Así que estoy buscando una manera de "unirlos" dinámicamente.

PD: no soy el creador de este sistema, y ​​no puedo realizar ningún cambio estructural en él, solo estoy tratando de resolver este problema.

Cualquier ayuda sería apreciada.

Answer 1

Echa un vistazo a los enlaces de abajo. Esto podría resolver su problema.

MySQL une tablas donde el nombre de la tabla es un campo de otra tabla , MySQL une tablas donde el nombre de la tabla es un campo de otra tabla

Answer 2

Esta es la forma más sencilla de hacer lo anterior. No hay necesidad de hacer bucles ni nada. Necesita código dinámico ya que las tablas se pueden agregar en cualquier momento.

Nota: En su muestra de datos para la tabla Files parece tener datos incorrectos en tblId ?

Así que estoy cambiando sus datos para hacer coincidir las ID con las tablas respectivas.

Esquema:

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

Ahora su parte de consulta dinámica:

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)

Si quieres ver lo que contiene @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''

*/

Answer 3

Este diseño es solo una forma de modelar una jerarquía en ER. Básicamente, tiene una tabla particionada físicamente basada en nombres de tabla (es decir, Table1 , Table2 y así sucesivamente). Como tal, la forma más fácil de unir estas tablas es crear una vista particionada y luego unirla.

En el caso de tu ejemplo solo tienes que hacer:

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

Ahora simplemente únase a la tabla de Files como de costumbre (recuerde especificar el campo de partición también):

Por ejemplo esto:

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

Da el resultado esperado:

Observe una cosa importante: dado que es una vista particionada, SQL Server puede realizar la eliminación de particiones, lo que acelera considerablemente la unión (el término correcto aquí debería ser la eliminación de tablas ).

Por ejemplo el plan de ejecución anterior fue:

Si agregamos un predicado de filtro en la columna de partición:

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

Obtendremos este plan de ejecución (observe que dos tablas no se escanean en absoluto):

Por supuesto, para poder usar la vista particionada, primero debe ser capaz de crearla. Puede hacerlo buscando los campos específicos mediante una consulta como esta:

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

Answer 4

Intenta lo siguiente.

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

Answer 5

Si solo tiene pocas tablas, puede hacer esto. Podría ser un poco más rápido porque evita el SQL dinámico.

Mire otras soluciones (me gusta la solución de la cámara de Steve) aquí si no puede saber cuántas tablas habrá o si habrá demasiadas.

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

Demostración: http://rextester.com/FWWD90002

Answer 6

Un enfoque consiste en crear un cte que contendrá todos los datos de las tablas (por supuesto, utilizando sql dinámico para crearlo), y luego seleccionar desde los archivos que se unen a ese cte.

De esta manera, el sql dinámico es bastante simple de escribir y mantener, y también la declaración sql que produce es muy simple:

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

El estado de SQL que se obtiene de esto es:

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

Para ejecutarlo:

EXEC(@SQL)

Resultados:

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

Demo en vivo en rextester

Answer 7

Una solución es usar un cursor que ejecute algún SQL dinámico para cada fila en la tabla @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;

Demostración en línea: http://rextester.com/DXCK86463