sql-server - SQL Server : How to select missing rows in table from another table?

Question

我有两个如下表:

表1:

StoreId SKU
------------
1       abc
2       abc
3       abc
1       xyz
4       xyz

表2:

 StoreId
 --------
    1
    2
    3
    4
    5

我想从表 2 中的 table1 中选择丢失的 storeid。但条件是在上面的示例中，SKU abc storeid 4 和 5 丢失，而 sku xyz 2,3,5 丢失。所以我想要下表作为输出

SKU,ID
------
abc 4
abc 5
xyz 2
xyz 3
xyz 5

使用下面的查询，我只能提取整个缺失的商店 5。

 SELECT 
     SKU, t2.StoreId 
 FROM 
     @table1 t1 
 FULL OUTER JOIN
     @table2 t2 ON t1.StoreId = t2.StoreId 
 WHERE
     t1.StoreId IS NULL

下面是测试创建和插入查询。

Declare @table1 As table
(
 StoreId varchar(4),
 SKU varchar(5)
)

Declare @table2 As table
(
 StoreId int
)
BEGIN
Insert Into @table1(SKU,StoreId) values('abc',1)
Insert Into @table1(SKU,StoreId) values('abc',2)
Insert Into @table1(SKU,StoreId) values('abc',3)
Insert Into @table1(SKU,StoreId) values('xyz',1)
Insert Into @table1(SKU,StoreId) values('xyz',4)
Insert Into @table2(StoreId) values(1)
Insert Into @table2(StoreId) values(2)
Insert Into @table2(StoreId) values(3)
Insert Into @table2(StoreId) values(4)
Insert Into @table2(StoreId) values(5)
END

谢谢

Answer 1

您需要获取所有sku和表的列表，然后仅显示未出现在table1中的行:

select SKU, StoreID
from @table2 t2
cross join (select distinct sku from @table1) t1
where not exists (select 1 from @table1 table1 
                  where table1.SKU = t1.SKU 
                    and table1.StoreId = t2.StoreId)