MySQL 计数逗号分隔

Question

我有 3 个这样的表:

表事件

+------+----------+----------------------+
| ID   | Title    | Employees            |
+------+----------+----------------------+
| 1    | Event1   | john,james           |
+------+----------+----------------------+
| 2    | Event2   | sarah,jessica        |
+------+----------+----------------------+

表 checkin

+------+----------+----------+---------------------+
| ID   | Time     | EventID  | By                  |
+------+----------+----------+---------------------+
| 1    | 08:30    | 1        | john                |
+------+----------+----------+---------------------+
| 2    | 08:30    | 1        | james               |
+------+----------+----------+---------------------+
| 3    | 09:30    | 1        | john                |
+------+----------+----------+---------------------+
| 4    | 10:30    | 2        | sarah               |
+------+----------+----------+---------------------+
| 5    | 10:35    | 2        | sarah               |
+------+----------+----------+---------------------+

表格问题

+------+----------------+----------+---------------------+
| ID   | Comment        | EventID  | By                  |
+------+----------------+----------+---------------------+
| 1    | Broken door    | 1        | john                |
+------+----------------+----------+---------------------+
| 2    | Slippery floor | 1        | john                |
+------+----------------+----------+---------------------+
| 3    | Leaking tap    | 1        | john                |
+------+----------------+----------+---------------------+
| 4    | Broken window  | 2        | jessica             |
+------+----------------+----------+---------------------+
| 5    | Broken glass   | 2        | jessica             |
+------+----------------+----------+---------------------+

我想打印这样的东西:

+------+----------+---------------+-------------------+-------------------+
| ID   | Title    | Employees     | Count_Check_In    | Count_Problems    |
+------+----------+---------------+-------------------+-------------------+
| 1    | Event1   | john,james    | john:2,james:1    | john:3,james:0    |
+------+----------+---------------+-------------------+-------------------+
| 2    | Event2   | sarah,jessica | sarah:2,jessica:0 | sarah:0,jessica:2 |
+------+----------+---------------+-------------------+-------------------+

我知道如果数据库设计得当，这个问题会很微不足道，但我们目前没有重写应用程序的奢侈。

Answer 1

您首先需要使用 union 从签到表和问题表中获取每个事件 ID 的所有员工。

然后 left join 将每个签到表和问题表的计数与之前的结果相加，也得到 0 计数。

最后使用 group_concat 为每个事件 ID 在一行中获取结果。

select te.id,te.title,te.employees
,group_concat(concat(t.`By`,':',coalesce(tccnt.cnt,0))) count_check_in
,group_concat(concat(t.`By`,':',coalesce(tpcnt.cnt,0))) count_problems
from table_events te 
left join (select eventid,`By` from table_check_in
           union
           select eventid,`By`from table_problems) t on te.id = t.eventid
left join (select eventid,`By`,count(*) cnt from table_check_in group by eventid,`By`) tccnt on tccnt.eventid = t.eventid and tccnt.`By`=t.`By`
left join (select eventid,`By`,count(*) cnt from table_problems group by eventid,`By`) tpcnt on tpcnt.eventid = t.eventid and tpcnt.`By`=t.`By`
group by te.id,te.title,te.employees

Sample Demo (感谢@valex 设置架构)