php - 如何用总和连接多个表

Question

我想按访问次数排序

类别有任务，任务有访问量。我有四个表:

1)visits
     -id

2)category 
    -id

3)tasks
    -id
    -category_id

4)task_visit
   -task_id
   -visit_id

我现在拥有的:

-按访问排序任务

SELECT count(task_id) as count, task_id
FROM task_visit
GROUP BY task_id
ORDER BY count DESC;

-加入任务类别

SELECT *
FROM categories
JOIN tasks
    ON categories.id = tasks.category_id
ORDER BY categories.id;

-尝试将类别与任务结合起来并计算访问次数(不起作用)，然后在接下来按类别对其进行求和

SELECT *
FROM categories
JOIN tasks
    ON categories.id = tasks.category_id
ORDER BY categories.id
JOIN
(
    SELECT count(task_id) as count, task_id
    FROM task_visit
    GROUP BY task_id
    ORDER BY count
) ON tasks.id = task_visit.task_id;

那么，如何按访问次数对类别进行排序？

Answer 1

您只需要连接 category、tasks 和 task_visit 表，以及按类别聚合:

SELECT
    c.id,
    COUNT(tv.visit_id) AS cnt
FROM category c
LEFT JOIN tasks t
    ON c.id = t.category_id
LEFT JOIN task_visit tv
    ON tv.task_id = t.id
GROUP BY
    c.id
ORDER BY cnt DESC;