SQL Compare tables

Question

我有两个具有相同模式的表，表中有两列，我想比较结果并获得不存在的唯一行，如下所示

表名:flow

tbody> <<tr>Rob

Employee	Dateinoffice
约翰	15/09/2021
Joane	15/09/2021
>	15/09/2021

如果你想要一个不同的视角，试试这个

select * from console 
where Employee+'-'+convert(varchar, Dateinoffice, 23) 
not in (select Employee+'-'+convert(varchar, Dateinoffice, 23) from flow)

DECLARE @table1 TABLE  (
Employee nvarchar(100),
Dateinoffice datetime
)
DECLARE @table2 TABLE  (
Employee nvarchar(100),
Dateinoffice datetime
)
INSERT INTO @table1 VALUES ('John', '2021-09-15'),('Joane', '2021-09-15'),('Rob', '2021-09-15')
INSERT INTO @table2 VALUES ('John', '2021-09-15'),('Joane', '2021-09-15'),('Rob', '2021-09-15'),('Rob', '2021-09-16')

——你的查询应该像这样

select * from @table2
except
select * from @table1

——如果只希望匹配的记录

select * from @table2
intersect
select * from @table1

---

记住:在"除了";语句中，查询的顺序很重要。

详见:https://learn.microsoft.com/en-us/sql/t-sql/language-elements/set-operators-except-and-intersect-transact-sql?view=sql-server-ver15

您可以使用full join来获取两个表中缺失的行:

select coalesce(f.employee, c.employee) as employee,
coalesce(f.dateinoffice, c.employee) as employee
from flow f full join
console c
on f.employee = c.employee and f.dateinoffice = c.dateinoffice
where f.employee is null or c.employee is null;

如果您特别希望console中的行不在flow中，则可以使用left join或not exists:

select c.employee, c.dateinoffice
from console c left join
flow f 
on f.employee = c.employee and f.dateinoffice = c.dateinoffice
where f.employee is null;