mysql - 如何使用子查询中的值按记录排序

Question

我的查询看起来像这样

SELECT 
    users.*, 
    (select count(*) 
      from user_actions 
      where status='pending' and user_id = users.id) number_pending
FROM users
order by name

我需要首先显示 number_pending 计数大于零的记录。您能帮我实现这一目标吗？

Answer 1

SELECT 
u.*, count(a.*) number_pending
FROM users u join user_actions a
on a.user_id = u.id
where a.status='pending' 
order by count(a.*) desc

这就是你想做的事吗？