sql - 如何加入而不是工会

Question

我有一个查询，其中两个表之间的联合按预期工作，但现在我想覆盖加入，因为我有一个表，其中一个字段可以在这两个表之间加入。现在，当我编写 join 语句时，我很惊讶它得到一个具有最大值的表并应用第二个表

让我们看一下代码：

declare  @Users table (UserId int, UserName nvarchar(50))
insert into @Users(UserId,UserName) values 
(1,'Test users 1'),
(2,'Blah user')

declare  @Computers table (ComputerId int, UserId int, ComputerName nvarchar(50))
insert into @Computers (ComputerId, UserId,ComputerName)
values (3, 1, 'Test comp 3'),
(5, 2, 'blah 1'),
(6, 2, 'blah 1')

declare  @ComputersDeleted table (ComputerId int, UserId int, ComputerName nvarchar(50))
insert into @ComputersDeleted (ComputerId, UserId,ComputerName)
values (1, 1, 'Test comp 1'),
(2, 1, 'Test comp 2'),
(4, 2, 'blah')

-- excpected result
select * from @Computers where UserId = 1
union
select * from @ComputersDeleted where UserId = 1

select * from @Users u
left join @Computers c on c.UserId = u.UserId
left join @ComputersDeleted cd on cd.UserId = u.UserId
where u.UserId = 1

在这种情况下如何写一个联合？我想得到这样的数据

table 1, table 2 , table 3
data1    NULL      data3
data1    NULL      data3
data1    data2     NULL

而不是它现在可以工作，比如应用

table 1, table 2 , table 3
data1    data2      data3
data1    data2      data3

标签： sqlsql-servertsql

Answer 1

与 CTE

declare  @Users table (UserId int, UserName nvarchar(50))
insert into @Users(UserId,UserName) values 
(1,'Test users 1'),
(2,'Blah user')

declare  @Computers table (ComputerId int, UserId int, ComputerName nvarchar(50))
insert into @Computers (ComputerId, UserId,ComputerName)
values (3, 1, 'Test comp 3'),
(5, 2, 'blah 1'),
(6, 2, 'blah 1')

declare  @ComputersDeleted table (ComputerId int, UserId int, ComputerName nvarchar(50))
insert into @ComputersDeleted (ComputerId, UserId,ComputerName)
values (1, 1, 'Test comp 1'),
(2, 1, 'Test comp 2'),
(4, 2, 'blah')


--WITH CTE
;WITH table_union(ComputerId, UserId,ComputerName)
AS
(
select ComputerId, UserId,ComputerName   from @Computers
union ALL
select  ComputerId, UserId,ComputerName from @ComputersDeleted
)
select *
from @Users u
LEFT Outer join table_union ON table_union.UserId = u.UserId
where u.UserId = 1

您还可以创建一个合并表的视图