Mysql COUNT 相关表的结果行

Question

我有

users
------------------------
id | name | other_stuff.....

.

engagement
------------------------
user_id | type_code |

type_code 是 varchar，但可以是 A、B、C 或 NULL

[为清楚起见进行编辑:用户可以对每种类型代码进行多次参与。所以我想数一下他们每个人有多少个。 ]

我想返回所有用户行，但包含 A、B 和 C 类型的参与计数。例如

users_result
------------------------
user_id | user_name | other_stuff..... | count_A | count_B | count_C |

我进行了大量搜索，但发现其他解决方案存在以下问题:

• “other_stuff...”实际上是来自十几个其他连接的分组/连接结果，所以它已经有点像怪物了。因此，我需要能够将附加字段添加到预先存在的“SELECT ...... FROM users...”查询中。

• 三个额外的必需数据位都来自同一个参与表，每个都有自己的条件。我还没有找到任何东西可以让我在同一个相关表上使用这三个条件。

谢谢

[编辑]

我试图简化问题，这样人们就不必查看大量不必要的内容，但似乎我可能没有提供足够的信息。这是原始查询的“大部分”。由于有负载，我删除了很多选定的字段，但我保留了大部分联接，以便您基本上可以看到实际发生的情况。

SELECT 
  user.id, 
  user.first_name, 
  user.second_name, 
  GROUP_CONCAT(DISTINCT illness.id ORDER BY illness.id SEPARATOR ',' ) AS reason_for_treatment, 
  IF(ww_id=1000003, 1,'') as user_refused_program, 
  Group_CONCAT(DISTINCT physical_activity.name SEPARATOR ', ') AS programme_options, 
  COUNT(CASE WHEN engagement_item.type_code LIKE 'wm6%' THEN 1 ELSE NULL END) as count_A, 
  COUNT(CASE WHEN engagement_item.type_code LIKE 'wm12%' THEN 1 ELSE NULL END) as count_B, 
  COUNT(CASE WHEN engagement_item.type_code LIKE 'wm6%' THEN 1 ELSE NULL END) as count_C 
FROM `user` 
LEFT JOIN session AS session_induction ON (user.id = session_induction.user_id AND session_induction.session_type_id = 3) 
LEFT JOIN stats AS stats_induction ON session_induction.id = stats_induction.session_id 
LEFT JOIN session AS session_interim ON (user.id = session_interim.user_id AND session_interim.session_type_id = 4) 
LEFT JOIN stats AS stats_interim ON session_interim.id = stats_interim.session_id 
LEFT JOIN session AS session_final ON (user.id = session_final.user_id AND session_final.session_type_id = 5) 
LEFT JOIN stats AS stats_final ON session_final.id = stats_final.session_id 
LEFT JOIN user_has_illness ON user.ID = user_has_illness.user_id 
LEFT JOIN illness ON user_has_illness.illness_id = illness.id 
LEFT JOIN user_has_physical_activity ON user.ID = user_has_physical_activity.user_id 
LEFT JOIN physical_activity ON user_has_physical_activity.physical_activity_id = physical_activity.id 
LEFT JOIN engagement_item ON user.ID = engagement_item.user_ID 
WHERE (user.INDUCTION_DATE>='2010-06-09' AND user.INDUCTION_DATE<='2011-06-09' AND user.archive!='1' ) 
GROUP BY user.id, engagement_item.user_id

值得一提的是，它运行良好 - 返回所有用户以及所需的所有详细信息。除了 count_A B 和 C 列。

[编辑在下面添加了稍微简化的查询]

删除了不相关的连接和选择。

SELECT 
  user.id, 
  user.first_name, 
  COUNT(CASE WHEN engagement_item.type_code LIKE 'wm6%' THEN 1 ELSE NULL END) as count_A, 
  COUNT(CASE WHEN engagement_item.type_code LIKE 'wm12%' THEN 1 ELSE NULL END) as count_B, 
  COUNT(CASE WHEN engagement_item.type_code LIKE 'wm6%' THEN 1 ELSE NULL END) as count_C 
FROM `user` 
LEFT JOIN engagement_item ON user.ID = engagement_item.user_ID 
GROUP BY user.id, engagement_item.user_id

Answer 1

SELECT e.user_id, u.name,
       COUNT(CASE type_code WHEN 'A' THEN 1 ELSE NULL END) as count_A,
       COUNT(CASE type_code WHEN 'B' THEN 1 ELSE NULL END) as count_B,
       COUNT(CASE type_code WHEN 'C' THEN 1 ELSE NULL END) as count_C
FROM engagement e join users u on (e.user_id = u.id)
GROUP BY e.user_id, u.name

我会使用 COUNT 而不是 SUM，因为这就是它的用途，在非 NULL 时对事物进行计数。

SELECT 
  user.id, 
  user.first_name, 
  user.second_name, 
  GROUP_CONCAT(DISTINCT illness.id ORDER BY illness.id SEPARATOR ',' ) AS reason_for_treatment, 
  IF(ww_id=1000003, 1,'') as user_refused_program, 
  Group_CONCAT(DISTINCT physical_activity.name SEPARATOR ', ') AS programme_options,
  ei.count_A, ei.count_B, ei.count_C
FROM `user` 
LEFT JOIN ( SELECT user_id
    , COUNT(CASE WHEN engagement_item.type_code LIKE 'wm6%' THEN 1 ELSE NULL END) as count_A
    , COUNT(CASE WHEN engagement_item.type_code LIKE 'wm12%' THEN 1 ELSE NULL END) as count_B 
    , COUNT(CASE WHEN engagement_item.type_code LIKE 'wm6%' THEN 1 ELSE NULL END) as count_C 
    FROM engagement_item
    GROUP BY userid ) ei
LEFT JOIN session AS session_induction ON (user.id = session_induction.user_id AND session_induction.session_type_id = 3) 
LEFT JOIN stats AS stats_induction ON session_induction.id = stats_induction.session_id 
LEFT JOIN session AS session_interim ON (user.id = session_interim.user_id AND session_interim.session_type_id = 4) 
LEFT JOIN stats AS stats_interim ON session_interim.id = stats_interim.session_id 
LEFT JOIN session AS session_final ON (user.id = session_final.user_id AND session_final.session_type_id = 5) 
LEFT JOIN stats AS stats_final ON session_final.id = stats_final.session_id 
LEFT JOIN user_has_illness ON user.ID = user_has_illness.user_id 
LEFT JOIN illness ON user_has_illness.illness_id = illness.id 
LEFT JOIN user_has_physical_activity ON user.ID = user_has_physical_activity.user_id 
LEFT JOIN physical_activity ON user_has_physical_activity.physical_activity_id = physical_activity.id 
LEFT JOIN engagement_item ON user.ID = engagement_item.user_ID 
WHERE (user.INDUCTION_DATE>='2010-06-09' AND user.INDUCTION_DATE<='2011-06-09' AND user.archive!='1' ) 
GROUP BY user.id, engagement_item.user_id, ei.count_A, ei.count_B, ei.count_C