MySQL跨2017-2020同结构分表，统计满足stars=4的ID跨表出现次数的方法

核心逻辑说明 #

之前的写法无法生效主要存在两个共性问题：

• 纯数字命名的表名不能用单引号包裹，需使用反引号 ` 转义，否则MySQL会将单引号内容识别为字符串而非数据表对象，直接触发语法错误
• 直接连表查询的逻辑会过滤掉仅在1张、2张表满足条件的id，无法覆盖全部统计范围，更适合采用先将各表符合过滤条件的记录合并、再分组计数的实现思路

可直接运行的参考SQL #

SELECT
  id,
  COUNT(*) AS matched_table_count
FROM (
  -- 提取2017表中满足stars=4的id
  SELECT id FROM `2017` WHERE stars = 4
  UNION ALL
  -- 提取2018表中满足stars=4的id
  SELECT id FROM `2018` WHERE stars = 4
  UNION ALL
  -- 提取2019表中满足stars=4的id
  SELECT id FROM `2019` WHERE stars = 4
  UNION ALL
  -- 提取2020表中满足stars=4的id
  SELECT id FROM `2020` WHERE stars = 4
) AS matched_id_pool
GROUP BY id
-- 可选：按匹配表数量降序排列结果
ORDER BY matched_table_count DESC;

细节注意 #

• 上述写法覆盖全部匹配场景：只要id在任意一张表中满足stars=4就会出现在结果中，对应matched_table_count就是该id符合条件的表数量，计数范围为1-4，完全匹配需求
• 若单张表内同一个id可能存在多条stars=4的重复记录，需要在每个子查询中增加DISTINCT，修改为SELECT DISTINCT id FROM 对应表 WHERE stars =4，避免单表内的重复记录导致计数虚高
• 合并多表记录时必须使用UNION ALL，不能用UNION：后者会对合并后的全量结果做去重，会打乱逐表计数的逻辑

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