This question already has answers here:
How to do a FULL OUTER JOIN in MySQL?
(14个答案)
去年关门了。
这里有两个表,每个表都有监控字段和用户id字段。我想对每一个监督的表和打印如下分组计数。两个表都包含不同的数据。
还应仅对指定的用户id计数。
表1列
+----+-------------+---------+
| id | supervision | user_id |
+----+-------------+---------+
|  1 | type1       |       2 |
|  2 | type1       |       2 |
|  3 | type2       |      1  |
|  4 | type1       |       2 |
|  5 | type2       |       2 |
+----+-------------+---------+

表2列
+----+-------------+---------+
| id | supervision | user_id |
+----+-------------+---------+
|  1 | type3       |       2 |
|  2 | type1       |       2 |
|  3 | type3       |       1 |
|  4 | type1       |       2 |
|  5 | type2       |       2 |
+----+-------------+---------+

对于user_id=2它应该给出如下输出:
+-------+--------+--------+
| Type  | table1 | table2 |
+-------+--------+--------+
| type1 |      3 |      2 |
| type2 |      1 |      1 |
| type3 |      0 |      1 |
| ....  |        |        |
+-------+--------+--------+

目前,我尝试的这个查询为table1提供了正确的结果,而不是table2。
select t1.supervision,
       count(t1.supervision) AS table1,
       count(t2.supervision) AS table2 from table1 t1
LEFT JOIN
table2 t2 ON t1.id=t2.id
where t1.userid=2 AND t2.userid=2
group by t1.supervision

最佳答案

SELECT t1.supervision, count(t1.id) AS table1, count(t2.id) AS table2
FROM users u
LEFT JOIN table1 t1 on (t1.user_id = u.id)
LEFT JOIN table2 t2 on (t2.user_id = u.id)
WHERE u.id = 2
GROUP BY t1.supervision

假设用户表存在。你的问题是Type 3没有在T1中退出,所以左边的连接将不包含来自那里的值,而你加入了ID而不是用户ID。

关于mysql - 如何对每个表进行分组计数并按列打印? ,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/53285038/

10-08 22:43
查看更多