时间戳

首页 > 解决方案 > 如何连接具有不同列的两个表

sql-server - 如何连接具有不同列的两个表

问题描述

有两个表（table1 有 2 行，table2 有 3 行）。我需要加入这两个表并获得输出（5 行），如预期结果所示。

我尝试过加入、联合，但行数越来越多。

使用此代码创建表。

DECLARE @Table1 AS TABLE
(
    versionid int, 
    substrate varchar(5), 
    yyyyww int, 
    [type] varchar (10), 
    itm_id varchar(15), 
    qty int, 
    assy_yield float,
    assy_start int 
)
INSERT INTO @Table1 (versionid,substrate,yyyyww, 
    [type],itm_id,qty,assy_yield,assy_start ) VALUES
(6704, 'E123',201919,'ASSEMBLY','2000-029-848',15537,0.997,15584),
(6704, 'E123',201919,'ASSEMBLY','2000-029-849',11578,0.997,11613)

DECLARE @Table2 AS TABLE
    (
    versionid int, 
    substrate varchar(5), 
    yyyyww int, 
    [type] varchar (10), 
    itm_id varchar(15), 
    qty int, 
    assy_yield float,
    assy_start int,
    test_yield float 
)
INSERT INTO @Table2 (versionid,substrate,yyyyww, 
    [type],itm_id,qty,assy_yield,assy_start, test_yield) VALUES
(6704, 'E123',201919,'TEST','2000-029-866',176,0.997,182,0.9707),
(6704, 'E123',201919,'TEST','2000-029-868',14,0.997,15,0.9707),
(6704, 'E123',201919,'TEST','2000-029-870',9,0.997,10,0.9707)

预期结果：

versionid   substrate   yyyyww  type        type1   itm_id          itm_id1         qty     qty1    assy_yield  assy_start  assy_start1     test_yield
--------------------------------------------------------------------------------------------------------------------------------------------------------
6704        E123        201919  ASSEMBLY    n.a.    2000-029-848    n.a.            15537   n.a.    0.997       15584       n.a.            n.a.
6704        E123        201919  ASSEMBLY    n.a.    2000-029-849    n.a.            11578   n.a.    0.997       11613       n.a.            n.a.
6704        E123        201919  n.a.        TEST    n.a.            2000-029-866    n.a.    176     0.997       n.a.        182             0.9707
6704        E123        201919  n.a.        TEST    n.a.            2000-029-868    n.a.    14      0.997       n.a.        15              0.9707
6704        E123        201919  n.a.        TEST    n.a.            2000-029-870    n.a.    9       0.997       n.a.        10              0.9707

标签： sql-servertsql

解决方案

我想这就是你要找的：

SELECT  versionid, substrate, yyyyww, 
        [type], null as type1, 
        itm_id, null as itm_id1, 
        qty, null as qty1,
        assy_yield, 
        assy_start, null as assy_start1, 
        null as test_yield
FROM @table1

UNION ALL

SELECT  versionid, substrate, yyyyww, 
    null as [type], [type] as type1, 
    null as itm_id, itm_id as itm_id1, 
    null as qty, qty as qty1,
    assy_yield, 
    null as assy_start, assy_start as assy_start1, 
    test_yield
FROM @table2

结果：

versionid   substrate   yyyyww  type        type1   itm_id          itm_id1         qty     qty1    assy_yield  assy_start  assy_start1 test_yield
6704        E123        201919  ASSEMBLY    NULL    2000-029-848    NULL            15537   NULL    0,997       15584       NULL        NULL
6704        E123        201919  ASSEMBLY    NULL    2000-029-849    NULL            11578   NULL    0,997       11613       NULL        NULL
6704        E123        201919  NULL        TEST    NULL            2000-029-866    NULL    176     0,997       NULL        182         0,9707
6704        E123        201919  NULL        TEST    NULL            2000-029-868    NULL    14      0,997       NULL        15          0,9707
6704        E123        201919  NULL        TEST    NULL            2000-029-870    NULL    9       0,997       NULL        10          0,9707

推荐阅读