mysql - MYSQL 中跨多个表的行计数和组合

Question

我想计算跨多个表的行中变量出现的次数，然后将它们组合起来以获得如下输出:

Output Table

user id | A count | B count | C count | Total count   
------------------------------------------------
1       |     *   |    *    |    *    |    *
2       |     *   |    *    |    *    |    *
3       |     *   |    *    |    *    |    *       

来自具有以下结构的表:

Table A, B, C etc

   x    |    y   |  user id  |   
-------------------------------
   *    |    *   |     2
   *    |    *   |     4
   *    |    *   |     1
   *    |    *   |     1

This question处理单个表中的计数，但我不确定如何跨多个表应用该解决方案以获得上面显示的输出。理想情况下我会输出到 CSV。任何帮助将不胜感激。

示例数据(请参阅评论以回答标记为正确的问题)

user_id     a        b         c       a+b+c
1           18266    238640    2022    258928
70          67       200       19      286
596         2        3         6       11
759         56       606       67      729
760         2        5         5       12
1122        194      1030      220     1444
1194        26       2         2       30
1298        195      466       2       663

Answer 1

SELECT user_id, SUM(a), SUM(b), SUM(c), SUM(a) + SUM(b) + SUM(c)
FROM
(
   SELECT user_id, count(*) a, 0 b, 0 c FROM A GROUP BY user_id
    UNION ALL
   SELECT user_id, 0, count(*), 0 FROM B GROUP BY user_id
    UNION ALL
   SELECT user_id, 0, 0, count(*) FROM C GROUP BY user_id
) tmp
GROUP BY user_id