sql - 比较数据库中一列存在但另一列不存在的记录

Question

我正在尝试比较同一个表中的两个“列表”并获取 customerId 位置的记录存在但 storeid不存在 customerid .

列表(表定义)

name             listid   storeid   customerid

BaseList           1        10         100
BaseList           1        11         100
BaseList           1        11         102


NewList            2        11         100
NewList            2        12         102
NewList            2        12         103

查询:

SELECT 
    NewList.*
FROM 
    Lists NewList
LEFT JOIN 
    Lists BaseList ON BaseList.customerid = NewList.customerid 
WHERE 
    BaseList.listid = 1
    AND NewList.listid = 2
    AND NewList.storeid <> BaseList.storeid
    AND NOT EXISTS (SELECT 1 
                    FROM Lists c
                    WHERE BaseList.customerid = c.customerid
                      AND BaseList.storeid = c.storeid
                      AND c.listid = 2)

当前结果:

NewList 2 11 100 
NewList 2 12 102 

但我期望只得到结果

NewList 2 12 102 

存在具有 storeid 11 的 customerid 100。

Fiddle

Answer 1

如果表定义包含列Name(如您所说)，则下面的语句将返回您的结果。

我不明白你的 select 语句。

SELECT *
from @table
WHERE NAME = 'NewList'
  AND customerID IN (SELECT CustomerID FROM @table WHERE NAME = 'BaseList')
  AND storeID NOT IN (SELECT storeID FROM @table WHERE NAME = 'BaseList')