sql - 如何在不重复行、不同粒度的情况下合并两个表

Question

有两个具有不同粒度（类型和子类型）的不同表，我想将这两个表结合起来以进行报告。交叉连接将增加行数，我避免使用 UNION ALL，因为表有 40 多列。

表

CREATE TABLE A
(
    SchoolID INT
    ,SchoolType INT
    ,SchoolSubtype INT
    ,SchoolScore INT
    ,LastUpdateDateTime DATETIME2
)


CREATE TABLE B
(
    SchoolID INT
    ,TotalStudents INT
    ,RecordedDate DATETIME2
)

INSERT INTO A (SchoolID, SchoolType, SchoolSubtype, SchoolScore, LastUpdateDateTime) 
VALUES (142, 342, 12, 98, '2019/12/01'), (142, 231, 11, 54, '2019/12/11'), (142, 231, 45, 89, '2019/12/01'), (145, 543, -1, 90, '2019/06/01')

INSERT INTO dbo.B (SchoolID, TotalStudents, RecordedDate)
VALUES (142, 82000,'2020/01/11'), (901, 78000,'2020/01/11')

SELECT *
FROM 
(
SELECT DISTINCT SchoolID, SchoolType, SchoolSubtype, 'A' AS [Source] FROM dbo.A
UNION ALL 
SELECT DISTINCT SchoolID, NULL AS SchoolType, NULL AS SchoolSubtype, 'B' AS [Source] FROM dbo.B
) AS z
    LEFT JOIN dbo.A
        ON z.SchoolID = a.SchoolID AND z.SchoolType = a.SchoolType AND z.SchoolSubtype = a.SchoolSubtype AND z.[Source] = 'A'
    LEFT JOIN dbo.B
        ON z.SchoolID = b.SchoolID AND z.[Source] = 'B'

想要的结果

标签： sqltsql

Answer 1

如果您想防止丢失列或任何其他打印错误，您可以生成您UNION ALL的INFORMATION_SCHEMA

declare @cmd varchar(max ) = 
    'select a.SchoolID'
    +
    (select ','+ c.COLUMN_NAME
    from INFORMATION_SCHEMA.COLUMNS c
    where c.TABLE_NAME = 'A'
        and c.COLUMN_NAME != 'SchoolID'
    for xml path('')
    )
    +
    (select ', null '+ c.COLUMN_NAME
    from INFORMATION_SCHEMA.COLUMNS c
    where c.TABLE_NAME = 'B'
        and c.COLUMN_NAME != 'SchoolID'
    for xml path('')
    )
    + 
    ' from A
    union all
    select b.SchoolID'
    +
    (select ', null '+ c.COLUMN_NAME
    from INFORMATION_SCHEMA.COLUMNS c
    where c.TABLE_NAME = 'A'
        and c.COLUMN_NAME != 'SchoolID'
    for xml path('')
    )
    +
    (select ','+ c.COLUMN_NAME
    from INFORMATION_SCHEMA.COLUMNS c
    where c.TABLE_NAME = 'B'
        and c.COLUMN_NAME != 'SchoolID'
    for xml path('')
    )
    + 
    ' from B
    order by SchoolID';

exec (@cmd);