我有一个SQL数据库,其中包含三个相应的表:customers、orders和items。客户有一个与订单中的id相对应的id。订单有一个与项目中的订单号相对应的订单号。Items是包含客户在特定公司花费的金额的表格。在这种情况下,我想把在一家名为"HRO"的公司的总支出相加,包括那些没有在HRO消费的客户(他们的名字旁边有一个零)。除了包含零的部分外,我的工作是正确的。提前感谢!
select customer.customer_num, customer.fname, sum(items.total_price) amountSpent
from customer
left join orders
on customer.customer_num = orders.customer_num
left join items
on orders.order_num = items.order_num
where items.manu_code like 'HRO'
group by customer.customer_num, customer.fname
您的where子句items.manu_code like 'HRO'
排除了没有任何订单的客户,因为这些客户的行对于items.manu_code
将具有null
。
不要使用where子句,而是尝试将该筛选器移动到join:中
select
customer.customer_num, customer.fname,
isnull(sum(items.total_price),0) amountSpent
from customer
left join orders
on customer.customer_num = orders.customer_num
left join items
on orders.order_num = items.order_num
and items.manu_code like 'HRO'
group by customer.customer_num, customer.fname
我已经将sum
封装在isnull
中,这样没有订单的客户显示为零支出,而不是null
支出。