如何创建合并两个表的视图？

Question

我有两个具有完全相同结构的表。两个表可以使用不同的主键（自动递增整数）存储相同的数据。因此，有第三个表列出了哪两个主键列出了相同的数据。但是，也存在其他行中不存在的行。因此，简单的联接不起作用，因为您将有两行具有相同的主键但数据不同。因此，是否有一种方法可以将主键重新分配给视图中未使用的值？

表1

 ID    name
 1     Adam
 2     Mark
 3     David
 4     Jeremy

表2

 ID    name
 1     Jessica
 2     Jeremy
 3     David
 4     Mark

表3

 T1ID  T2ID
 2     4
 3     3
 4     2

我正在寻找如下所示的结果表：

结果

 ID    name
 1     Adam
 2     Mark
 3     David
 4     Jeremy
 5     Jessica

问题的真正核心是我如何将临时假ID 5 分配给杰西卡，而不仅仅是一些随机数。我想要的 id 规则是，如果该行存在于第一个表中，则使用它自己的 id。否则，使用插入语句生成的下一个 id（该列处于自动增量状态）。

Answer 1

编辑问题的答案

select id, name from table1
union all
select X.offset + row_number() over (order by id), name
from (select MAX(id) offset from table1) X
cross join table2
where not exists (select * from table3 where t2id = table2.id)

MAX(id) 用于“预测”如果将第二个表中的数据合并到第一个表中将会出现的下一个身份。如果 Table3.T2ID 存在，则意味着它已包含在 table1 中。

使用下面的测试数据

create table table1 (id int identity, name varchar(10))
insert table1 select 'Adam' union all
select 'Mark' union all
select 'David' union all
select 'Jeremy'

create table table2 (id int identity, name varchar(10))
insert table2 select 'Jessica' union all
select 'Jeremy' union all
select 'David' union all
select 'Mark'

create table table3 (t1id int, t2id int)
insert table3 select 2,4 union all
select 3,3 union all
select 4,2

---

回答下面的原始问题

那么第三个表是您想要构建的表（视图而不是表）？

select newid=row_number() over (order by pk_id), *
from
(
    select a.*
    from tblfirst a
    UNION ALL
    select b.*
    from tblsecond b
) X

数据将包含每个记录的唯一 newid 值，无论是来自第一个表还是第二个表。将 pk_id 更改为您的主键列名称。

Answer 2

假设您有以下数据，据我了解，阅读您的问题：

Table: T1
ID  name
--------
1   a
2   b
3   c

Table: T2
ID  name
--------
2   b
3   c
4   d

Table: Rel
ID1  ID2
--------
2   2
3   3

T1 有一些数据不在 T2 中，反之亦然。
以下查询将给出所有数据联合

SELECT ROW_NUMBER() OVER (order by name) ID, Col
from 
(
SELECT ISNULL(T1.name,'') name
FROM T1 t1 LEFT JOIN Rel TR ON TR.ID1 = T1.ID
union
SELECT ISNULL(T2.name,'') name
FROM T2 t2 LEFT JOIN Rel TR ON TR.ID2 = T2.ID
) T

Answer 3

如果我理解正确，以下可能会起作用

1. 从第一个表中选择所有内容
2. 从第二个表中选择未链接到第三个表的所有内容
3. 合并结果

测试数据

DECLARE @Table1 TABLE (ID INTEGER IDENTITY(1, 1), Value VARCHAR(32))
DECLARE @Table2 TABLE (ID INTEGER IDENTITY(1, 1), Value VARCHAR(32))
DECLARE @Table3 TABLE (T1ID INTEGER, T2ID INTEGER)


INSERT INTO @Table1 VALUES ('Adam')
INSERT INTO @Table1 VALUES ('Mark')
INSERT INTO @Table1 VALUES ('David')
INSERT INTO @Table1 VALUES ('Jeremy')

INSERT INTO @Table2 VALUES ('Jessica')
INSERT INTO @Table2 VALUES ('Jeremy')
INSERT INTO @Table2 VALUES ('David')
INSERT INTO @Table2 VALUES ('Mark')

INSERT INTO @Table3 VALUES (2, 4)
INSERT INTO @Table3 VALUES (3, 3)
INSERT INTO @Table3 VALUES (4, 2)

SQL 语句

SELECT  ROW_NUMBER() OVER (ORDER BY ID), t1.Value
FROM    @Table1 t1
UNION ALL 
SELECT  ROW_NUMBER() OVER (ORDER BY ID) + offset, t2.Value
FROM    @Table2 t2
        LEFT OUTER JOIN @Table3 t3 ON t3.T2ID = t2.ID
        CROSS APPLY (
          SELECT  Offset = COUNT(*)
          FROM    @Table1
        ) offset
WHERE   t3.T2ID IS NULL