如何查询单条件存在但另一条件缺失的数据及SQL语句优化

一、通用查询思路：存在某条件，但另一条件为空/无对应值 #

当你需要找满足A条件，但不存在B条件（或者B条件为空）的数据时，核心是先锁定满足A的主体，再排除那些同时符合B条件的情况，常用方法有这几种：

• 子查询（NOT EXISTS）：最直观的方式，先找到满足A的记录，再确保该主体（比如商品编号）没有符合B条件的记录
• 分组筛选（GROUP BY + HAVING）：对主体分组，统计满足A和B的数量，只保留有A但无B的组
• 左连接（LEFT JOIN）：将满足A的表和满足B的表左连接，筛选出B表中无匹配的记录

二、你的具体SQL问题解决 #

先说说你原来的SQL为什么不对：

SELECT [Country_IBS] , [Item_Number] , [Price_List] , [Sales_Price] , [Currency] 
FROM [DATA_IBS].[dbo].[fact_List_Prices_ALL_ALL_COUNTRIES] 
where Price_List in ('01') and Price_List not in ('ma')

这个语句的逻辑是同一行数据的Price_List既是'01'又不是'ma'——但同一行的Price_List只能是一个值，所以它其实等价于Price_List = '01'，自然会把那些同时有'ma'价格列表的商品也包含进来，因为只要有一行是'01'就会被选出来，不管这个商品有没有'ma'的行。

下面给你几种正确的写法：

方法1：用NOT EXISTS子查询（推荐，性能较好） #

逻辑是：先找到所有Price_List为'01'的商品记录，同时确保这个商品不存在Price_List为'ma'的记录：

SELECT [Country_IBS] , [Item_Number] , [Price_List] , [Sales_Price] , [Currency] 
FROM [DATA_IBS].[dbo].[fact_List_Prices_ALL_ALL_COUNTRIES] main
WHERE main.Price_List = '01'
AND NOT EXISTS (
    SELECT 1
    FROM [DATA_IBS].[dbo].[fact_List_Prices_ALL_ALL_COUNTRIES] sub
    WHERE sub.Item_Number = main.Item_Number
    AND sub.Price_List = 'ma'
)

方法2：用GROUP BY + HAVING筛选 #

如果只需要商品编号和对应的'01'价格信息，可以先分组，确保每个商品只有'01'的价格列表，没有'ma'：

SELECT [Country_IBS] , [Item_Number] , [Price_List] , [Sales_Price] , [Currency] 
FROM [DATA_IBS].[dbo].[fact_List_Prices_ALL_ALL_COUNTRIES]
WHERE Item_Number IN (
    SELECT Item_Number
    FROM [DATA_IBS].[dbo].[fact_List_Prices_ALL_ALL_COUNTRIES]
    WHERE Price_List IN ('01', 'ma')
    GROUP BY Item_Number
    HAVING COUNT(DISTINCT Price_List) = 1 
       AND MAX(Price_List) = '01' -- 确保唯一的那个是'01'
)
AND Price_List = '01'

方法3：左连接筛选无匹配的记录 #

把满足'01'的表和满足'ma'的表左连接，然后筛选出'ma'表中没有匹配的行：

SELECT main.[Country_IBS] , main.[Item_Number] , main.[Price_List] , main.[Sales_Price] , main.[Currency] 
FROM [DATA_IBS].[dbo].[fact_List_Prices_ALL_ALL_COUNTRIES] main
LEFT JOIN [DATA_IBS].[dbo].[fact_List_Prices_ALL_ALL_COUNTRIES] sub
    ON main.Item_Number = sub.Item_Number
    AND sub.Price_List = 'ma'
WHERE main.Price_List = '01'
AND sub.Item_Number IS NULL -- 表示没有对应的'ma'记录

这几种方法都能帮你筛选出只有'01'价格列表、没有'ma'价格列表的商品记录，你可以根据自己数据库的性能情况选择合适的写法~

内容的提问来源于stack exchange，提问作者phew