如何筛选MySQL中ScheduleId与EmployeeId计数均大于1的分组结果

参考相关文章后编写了如下MySQL查询语句：

select 
  ScheduleId,count(*) as s, 
  EmployeeId,count(*) as e 
  from Schedules
  group by ScheduleId, EmployeeId

该语句返回结果如下：

ScheduleId   s       EmployeeId   e
1            1       24242        1
2            1       83928        1
3            1       93829        1
4            2       84993        2
5            1       43434        1

希望仅显示ScheduleId对应的总计数和EmployeeId对应的总计数均大于1的记录，即只保留如下结果：

4            2       84993        2

尝试添加类似where p.count(*) > 1的条件未能成功，需要解决方法。

你当前的查询里，count(*) as s和count(*) as e是同一个值——当前ScheduleId+EmployeeId分组下的记录数，并不是单独的ScheduleId总出现次数或EmployeeId总出现次数。要实现需求，得先算出每个ScheduleId、每个EmployeeId的总出现次数，再关联回原分组结果筛选。

下面提供两种可行方法：

方法1：子查询预计算计数 #

SELECT 
  s.ScheduleId, 
  sc.schedule_count AS s, 
  s.EmployeeId, 
  ec.employee_count AS e
FROM (
  SELECT ScheduleId, EmployeeId, COUNT(*) AS group_count
  FROM Schedules
  GROUP BY ScheduleId, EmployeeId
) s
JOIN (
  SELECT ScheduleId, COUNT(*) AS schedule_count
  FROM Schedules
  GROUP BY ScheduleId
) sc ON s.ScheduleId = sc.ScheduleId
JOIN (
  SELECT EmployeeId, COUNT(*) AS employee_count
  FROM Schedules
  GROUP BY EmployeeId
) ec ON s.EmployeeId = ec.EmployeeId
WHERE sc.schedule_count > 1 AND ec.employee_count > 1;

方法2：窗口函数（MySQL 8.0+适用） #

窗口函数能直接在分组时计算全局的ScheduleId和EmployeeId计数，写法更简洁：

SELECT DISTINCT
  ScheduleId,
  COUNT(*) OVER (PARTITION BY ScheduleId) AS s,
  EmployeeId,
  COUNT(*) OVER (PARTITION BY EmployeeId) AS e
FROM Schedules
GROUP BY ScheduleId, EmployeeId
HAVING COUNT(*) OVER (PARTITION BY ScheduleId) > 1 
   AND COUNT(*) OVER (PARTITION BY EmployeeId) > 1;

说明 #

• 方法1通过三个子查询分别获取分组记录、ScheduleId总计数、EmployeeId总计数，再通过关联筛选出两个计数都大于1的记录，兼容所有MySQL版本。
• 方法2利用窗口函数OVER (PARTITION BY ...)直接计算每个ScheduleId和EmployeeId的全局总次数，通过HAVING条件筛选，无需额外关联，效率更高，适合MySQL 8.0及以上版本。

内容的提问来源于stack exchange，提问作者John