如何在count()不返回任何值的情况下选择所有行?

lf5gs5x2  于 2021-06-21  发布在  Mysql
关注(0)|答案(1)|浏览(257)

我有两个简单的客户和订单栏。我想选择所有客户和适当的订单数量,即使计数为零(或空或其他)。以下操作不起作用,仅返回有订单的客户:

select customers.id, count(orders.orderid) as total_orders from `xcart_customers` customers 
left join `xcart_orders` orders 
    on customers.id=orders.userid 
where orders.status in ('C', 'K') 
group by customers.id

添加 having total_orders=0 没有帮助,因为这些行未被选中。也试过了 ifnull 和其他很多东西(左,右外接),但没有运气。

xvw2m8pv

xvw2m8pv1#

这是查询。

select customers.id, count(orders.orderid) as total_orders from `xcart_customers` customers 
left join `xcart_orders` orders 
    on customers.id=orders.userid and orders.status in ('C', 'K') 
group by customers.id

相关问题