SQL查询仅返回报价与订单数相等的客户问题排查求助

我来帮你搞定这个问题——你现在遇到的核心问题是把LEFT JOIN意外变成了隐式的INNER JOIN，导致只有同时存在指定时段报价和订单的客户才会被返回，完全不符合你想要的全量客户+对应计数的需求。咱们一步步拆解问题，再给出修复后的SQL：

问题根源 #

你的SQL里犯了一个LEFT JOIN的经典错误：把针对子查询的过滤条件写在了WHERE子句里，而不是LEFT JOIN的ON子句里。

举个例子，你写了：

left join (...) as Quotes on Customer2.Name = Quotes.UD03_Key1
...
where Quotes.UD03_Date02 >= '5/15/2018' and Quotes.UD03_Date02 <= '5/15/2018'

对于那些没有匹配到报价记录的客户，Quotes.UD03_Date02会是NULL，而NULL和任何值做比较都会返回false，所以这些客户直接被WHERE子句过滤掉了。订单子查询的过滤条件也犯了同样的错误，最终就只剩同时有报价和订单的客户能被返回。

另外，虽然你用count(Quotes.UD03_Key3)来统计报价数是对的（NULL会被count忽略），但为了让没有记录的客户显示0而不是NULL，可以用COALESCE包装一下计数结果。

修复后的SQL语句 #

select 
    [Customer2].[Name] as [Customer2_Name],
    COALESCE(count(Quotes.UD03_Key3), 0) as [Calculated_CustomerQuotes],
    COALESCE(count(Customer_Bookings.OrderHed_OrderNum), 0) as [Calculated_CustomerBookings]
from Erp.Customer as Customer2
left join (
    select 
        [UD03].[Key3] as [UD03_Key3], 
        [UD03].[Key4] as [UD03_Key4], 
        [UD03].[Key1] as [UD03_Key1], 
        [UD03].[Date02] as [UD03_Date02] 
    from Ice.UD03 as UD03 
    inner join Ice.UD02 as UD02 on UD03.Company = UD02.Company And CAST(CAST(UD03.Number09 AS INT) AS VARCHAR(30)) = UD02.Key1 
    left outer join Erp.Customer as Customer on UD03.Company = Customer.Company And UD03.Key1 = Customer.Name 
    left outer join Erp.SalesTer as SalesTer on Customer.Company = SalesTer.Company And Customer.TerritoryID = SalesTer.TerritoryID 
    left outer join Erp.CustGrup as CustGrup on Customer.Company = CustGrup.Company And Customer.GroupCode = CustGrup.GroupCode 
    where (UD03.Key3 <> '0')
) as Quotes on 
    Customer2.Name = Quotes.UD03_Key1
    AND Quotes.UD03_Date02 >= '5/15/2018' 
    AND Quotes.UD03_Date02 <= '5/15/2018' -- 把日期过滤移到ON子句
left join (
    select 
        [Customer1].[Name] as [Customer1_Name], 
        [OrderHed].[OrderNum] as [OrderHed_OrderNum], 
        [OrderDtl].[OrderLine] as [OrderDtl_OrderLine], 
        [OrderHed].[OrderDate] as [OrderHed_OrderDate] 
    from Erp.OrderHed as OrderHed 
    inner join Erp.Customer as Customer1 on OrderHed.Company = Customer1.Company And OrderHed.BTCustNum = Customer1.CustNum 
    inner join Erp.OrderDtl as OrderDtl on OrderHed.Company = OrderDtl.Company And OrderHed.OrderNum = OrderDtl.OrderNum
) as Customer_Bookings on 
    Customer2.Name = Customer_Bookings.Customer1_Name
    AND Customer_Bookings.OrderHed_OrderDate >= '5/15/2018' 
    AND Customer_Bookings.OrderHed_OrderDate <= '5/15/2018' -- 同样把日期过滤移到ON子句
group by [Customer2].[Name]

关键修改点说明 #

1. 将子查询的过滤条件移至LEFT JOIN的ON子句：这样只会过滤子查询中匹配的记录，不会把主表中没有匹配的客户过滤掉，保证返回全量客户。
2. 用COALESCE处理NULL计数：对于没有报价或订单的客户，count结果会是NULL，用COALESCE(..., 0)可以把NULL转换成0，让结果更符合你的预期格式。
3. 保留原有的关联逻辑：子查询内部的关联逻辑没有改动，确保统计的报价和订单数据是准确的。

这样修改后，你就能得到包含全量客户、对应报价数和订单数的结果集，即使某个客户没有报价或订单，也会显示0而不是被过滤掉。

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