mysql - 使用“加入”按大多数商品排序

Question

我有一个具有以下结构的“items”表:

id, person_id, active

以及带有 id 和 name 列的“people”表。

我想按最活跃的项目排序并将 people.name 列加入到 sql 中。

我尝试做类似的事情，但它不起作用:

SELECT people.id, COUNT(*) as items_count
FROM items, people
WHERE items.active = true AND people.id = items.person_id
GROUP BY items.person_id
ORDER BY items_count DESC

Answer 1

使用连接这将匹配条件。按 items_count 排序将为您提供最活跃的用户

Select people.id, COUNT(items.active) as items_count
FROM items
LEFT JOIN people on  people.id = items.person_id
WHERE items.active = true
GROUP BY people.id
ORDER BY items_count DESC