当没有where子句或不存在子句时，SQL Server查询聚合不正确

Question

编辑：我已经尝试了前两种解决方案，但在查看带有WHERE子句的单个客户时，仍然存在查询返回正确结果的问题，但对于没有WHERE子句的同一客户，查询返回错误结果的问题。这怎么会发生？到底是什么导致了这一切？

我正在构建一个查询，以便在一个大表上联接和聚合客户信息，因此我开始为单个客户构建带有where子句的查询，以确保在对客户群体实施逻辑之前该逻辑正常工作。

我加入的表格看起来像这样：

表A:

| customer | order_id |
----------------------
| abc      | 1       |
| abc      | 2       |
| xyz      | 3       |
| xyz      | 4       |
| xyz      | 5       |
| xyz      | 6       |
...

表B:

| order_id | return_date   |
----------------------------
| 1        |       Mon     |
| 3        |       Tues    |
| 5        |       Wed     |
...

我需要根据客户名称来汇总这些信息，并计算他们的信息在每个表中出现的次数。

所以查询看起来像这样：

SELECT 
a.customer as customer_name
,COUNT(DISTINCT(a.order_id)) as total_orders
,COUNT(DISTINCT(B.order_id)) as num_returns
FROM B
RIGHT JOIN (
SELECT 
customer
order_id
FROM A
) as a
ON B.order_id = a.order_id
WHERE customer = 'xyz'
GROUP BY a.customer

当where子句存在时，这非常有效(也适用于groupby后面的HAVING customer='xyz'(。但是，当我删除where子句将其应用于客户群体时，结果完全不正确。我如何才能解决这一问题，为民众服务？

Answer 1

此查询应该有效：

SELECT a.customer as customer_name,
COUNT(DISTINCT a.order_id) as total_orders,
COUNT(DISTINCT B.order_id) as num_returns
FROM A LEFT JOIN
B
ON B.order_id = a.order_id
WHERE a.customer = 'xyz'
GROUP BY a.customer;

如果xyz在A中没有行，则不返回任何行。

Answer 2

我建议在b和left join:上进行预聚合

select a.customer, count(*) total_orders, coalesce(sum(b.num_returns), 0) num_returns
from a
left join (
select order_id, count(*) num_returns
from b
group by order_id
) b on b.order_id = a.order_id
group by a.customer

无论是否使用where子句，结果都是一致的。请注意，这假设a中没有重复的(customer_id, order_id)，如示例数据所示。

横向连接也可以：

select a.customer, count(*) total_orders, sum(b.num_returns) num_returns
from a
cross apply (
select count(*) num_returns
from b
where b.order_id = a.order_id
) b
group by a.customer