MSSQL查询如何保留销量为0的指定店铺数据行？

问题描述 #

我有一批以3字母缩写标识的店铺，各店有初始苹果库存及对应销量数据。原本使用以下MSSQL查询展示店铺编码、名称、已售苹果数及初始总库存：

select distinct si.[Shop Code], ai.[Shop Name], count(si.[Apples Sold]), ai.[Total Apples]
from [Apple Database 1] si
left join [Apple Database 2] ai
    on si.[Shop Code] = ai.[Grocery Store Code]
where si.[Apple Status Code] = '13'
and si.[Shop Code] like ('abc', 'def', 'ghi', 'jkl', 'mno', 'pqr')
and si.[Status] = 'Complete'
group by si.[Shop Code], ai.[Shop Name], ai.[Total Apples]

其中Apple Status Code和Status用于标识苹果已售出且流程完成。当前查询仅返回有销量的店铺，但需要包含指定店铺中销量为0的记录。

解决方案 #

核心问题是原查询以销量表为起点，且WHERE子句过滤掉了无符合条件销量的店铺。调整思路为以店铺基础信息表为主表，将销量过滤条件移至JOIN关联条件中，确保所有指定店铺都被保留。

修改后的查询语句（统计销售记录数）

SELECT 
    ai.[Grocery Store Code] AS [Shop Code],
    ai.[Shop Name],
    COUNT(si.[Apples Sold]) AS [Apples Sold Count],
    ai.[Total Apples]
FROM [Apple Database 2] ai
LEFT JOIN [Apple Database 1] si
    ON ai.[Grocery Store Code] = si.[Shop Code]
    AND si.[Apple Status Code] = '13'
    AND si.[Status] = 'Complete'
WHERE ai.[Grocery Store Code] IN ('abc', 'def', 'ghi', 'jkl', 'mno', 'pqr')
GROUP BY ai.[Grocery Store Code], ai.[Shop Name], ai.[Total Apples]

关键调整说明

• 切换主表：以存储店铺基础信息的[Apple Database 2]为主表，确保所有指定店铺都能被纳入查询范围
• 调整过滤条件位置：将销量状态相关的过滤条件移至LEFT JOIN的ON子句中，仅关联符合已售出且流程完成的销量记录，不会排除无销量的店铺
• 修正店铺编码筛选：原查询中like ('abc', ...)的用法错误，MSSQL多值匹配需使用IN关键字
• COUNT统计逻辑：LEFT JOIN后无销量的店铺对应si.[Apples Sold]为NULL，COUNT会忽略NULL值，自然返回0

若需统计销售总量（单条记录对应多个苹果）

如果[Apples Sold]字段代表单条记录的销售数量，而非单条记录对应1个苹果，可使用SUM统计总销量：

SELECT 
    ai.[Grocery Store Code] AS [Shop Code],
    ai.[Shop Name],
    ISNULL(SUM(si.[Apples Sold]), 0) AS [Total Apples Sold],
    ai.[Total Apples]
FROM [Apple Database 2] ai
LEFT JOIN [Apple Database 1] si
    ON ai.[Grocery Store Code] = si.[Shop Code]
    AND si.[Apple Status Code] = '13'
    AND si.[Status] = 'Complete'
WHERE ai.[Grocery Store Code] IN ('abc', 'def', 'ghi', 'jkl', 'mno', 'pqr')
GROUP BY ai.[Grocery Store Code], ai.[Shop Name], ai.[Total Apples]

使用ISNULL将SUM返回的NULL（无销量时）转为0，确保结果统一。

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