按类型子排序的MySQL查询计数

Question

我想知道是否有人能帮助我解决这个问题

我有以下表格

user_ids {
    uUID
    name
}

types {
    tUID
    name
}

activity {
    aUID
    user_id
    type_id
    date
}

所以用户将包含

uUID    name
1       Bob
2       Mark
3       Peter

类型应该是

tUID    name
1       Baseball
2       Football
3       Cricket

活动将会是...

aUID    user_id    type_id    date
1         1           2
2         1           2
3         1           3
4         2           1
5         2           3
6         2           1
7         3           3
8         3           3
9         3           3

现在我想要的是以下表格返回结果。

Name   Type      Count of type
Bob    Baseball  0
Bob    Football  2
Bob    Cricket   1
Mark   Baseball  2
Mark   Football  0
Mark   Cricket   1
Peter  Baseball  0
Peter  Football  0
Peter  Cricket   3

我可以使用每个用户ID的单个查询来完成此操作，但是我想知道是否有一种方法可以使用单个查询来完成此操作。谢谢，马丁。

- Martin

Answer 1

你只需要左连接类型和活动表，然后按用户和类型进行分组：

select u.name as Name, t.name as Type, COUNT(a.user_id) as `Count of type`
from type t
left join activity a on t.tuid=a.type_id
left join users u on a.user_id = u.uUID
group by u.name, t.name

- Shadow

Answer 2

试试这样做

零风险

SELECT X.Name ,X.Type,
SUM(CASE WHEN A.user_id IS NULL THEN 0 ELSE 1 END ) as `Count of type`
FROM
(
    SELECT T.Name AS Type,U.name AS NAME,U.uUID,T.tuid FROM types T,user_ids U
)X  LEFT JOIN activity A ON A.type_id = X.tuid AND A.user_id =X.uUID
GROUP BY X.Name ,X.Type
ORDER BY X.Name ,X.Type

没有零

SELECT U.name AS Name, 
       T.name AS Type, 
       SUM(CASE WHEN A.user_id IS NULL THEN 0 ELSE 1 END ) as `Count of type`
FROM types T
LEFT JOIN activity a on T.tuid=A.type_id
JOIN user_ids U on A.user_id = U.uUID
GROUP BY U.name, T.name

- Vignesh Kumar A

---

如果用户没有进行任何活动或只进行了1个EG，那么"With zero"语句似乎不会返回0，而是返回空值。 - Martin

不会返回用户是否已经执行了某些活动。 - Vignesh Kumar A

Answer 3

一种解决方案是使用users和types的CROSS JOIN，然后与activity进行LEFT JOIN。

select u.name as Name, t.name as Type, COUNT(a.aUID) as `Count of type`
from users u CROSS JOIN type t
    LEFT JOIN activity a ON a.user_id = u.uUID AND a.type_id = t.tUID

- Dylan Su