MySQL 按子查询总和排序

Question

我试图从表中选择一组行并按一组子查询进行排序:

SELECT *, 
(SELECT Count(id) > 0 FROM places_users WHERE places_id = places.id) AS verified_bool,
(SELECT Count(id) > 0 FROM places_services WHERE places_id = places.id) AS services_bool
FROM places
ORDER BY sum(verified_bool + services_bool) DESC 
LIMIT 0, 10

但它只返回 1 个结果。子查询检查连接到原始行的其他表。我只需要验证其他表是否至少有 1 个结果，因此我使用 bool 值，然后尝试对子查询 0 或 1 求和，并将原始结果从总和最大的结果排列到总和最小的结果。

Answer 1

问题在于 order by 子句中的总和:

SELECT *, 
(SELECT Count(id) > 0 FROM places_users WHERE places_id = places.id) AS verified_bool,
(SELECT Count(id) > 0 FROM places_services WHERE places_id = places.id) AS services_bool
FROM places
ORDER BY (verified_bool + services_bool) DESC 
LIMIT 0, 10