MySQL COUNT 记录在 GROUP BY 之前

Question

我正在对两个表进行简单查询，并且正在尝试计算具有相同 ID 的记录数(如果我不将它们分组)。这些是表格:

表作业

id -- 类型 -- user_id -- field_id

1 -- 绘画 -- 53 -- 12

2 -- 阅读 -- 53 -- 12

3 -- 写作 -- 53 -- 12

表格事件

id -- is_test -- user_id -- field_id

7 -- 是 -- 53 -- 12

8 -- 否 -- 53 -- 12

通过此查询，我从表 jobs (绘画)中获取了所需的单个记录，因为我在列 上使用了 GROUP BY 子句职位.id。如果我没有使用 GROUP BY，我将获得 2 条记录，因为相同的 user_id 和 field_id 有 2 个匹配事件。

SELECT `jobs`.*,`activities`.`is_test` 
FROM `jobs` 
LEFT JOIN `activities` ON `activities`.`user_id`=`jobs`.`user_id` 
WHERE `jobs`.`field_id`=12 AND `activities`.`field_id`=12 AND `jobs`.`type`='painting' AND `jobs`.`user_id`=53 
GROUP BY `jobs`.`id` 
ORDER BY `jobs`.`id` DESC;

我想要做的是获取另一列，其中包含表activities 中匹配记录的实际数量。基本上我想获得 2 作为 activities 中具有相同 user_id 和 field_id 的记录数。

我尝试像这样选择新列:

SELECT `jobs`.*,`activities`.`is_test`, COUNT(DISTINCT `jobs`.`id`) AS `tot_act` 

但是对于新列tot_act，这显然返回 1 而不是 2。使其对 activities.is_test 字段进行计数也不起作用。

我试图避免子选择。

请不要关注activities.is_test这一栏，我不关心它是否设置为Yes或No，我只想统计是否有一条匹配记录为Yes如果我不将它们分组，则总共 2 条记录。 2 是我尝试检索作为新列的值。

任何想法将不胜感激!

Answer 1

您的第一个查询没有任何意义。 Group by 仅与输出列(select 子句)中的聚合函数结合才有意义。

activity.field_id 上的 where 过滤器正在(昂贵地)将左连接转换为内连接。

我还怀疑您的架构未规范化。

to fetch another column containing the actual number of matching records in the table activities

SELECT `jobs`.id, jobs.type, jobs.user_id, 
  Jobs.field_id,`activities`.`is_test`, 
  SUM(IF(activities.id IS NULL, 0,1)) 
FROM `jobs` 
 LEFT JOIN `activities` 
 ON `activities`.`user_id`=`jobs`.`user_id` 
 AND activities.field_id=12
WHERE `jobs`.`field_id`=12 
 AND `jobs`.`type`='painting' 
 AND `jobs`.`user_id`=53 
GROUP BY `jobs`.id, jobs.type, jobs.user_id, 
  Jobs.field_id,`activities`.`is_test`
ORDER BY `jobs`.`id` DESC;