SQL多表关联GROUP BY-HAVING悬疑类电影统计查询无结果修复
错误根因定位
- 核心逻辑完全错位:原SQL中HAVING子句的判断逻辑,是筛选出「本身是全库所有电影中上映数量最多的年份」的Mystery电影分组,完全没有按照需求计算Mystery电影自身的年度计数最大值。测试库中Mystery电影单年最高计数为2(对应2001年),但全库电影总数最高的年份往往远大于2部,既可能不在Mystery电影的统计结果里,也可能不符合需求逻辑,最终查询要么无返回,要么返回完全错误的结果。
- 设计冗余:三层嵌套子查询的结构没有复用统一的筛选逻辑,既放大了范围错位的bug,也大幅增加了排查难度。
- 细节瑕疵:类型匹配使用
genres.gen_title LIKE '%mystery%'的模糊匹配规则,存在误匹配其他带mystery字段的非目标类型的风险。
修正方案
按照“先统一筛选Mystery类电影集合、再按上映年份聚合计数、最后筛选出计数最高的年份”的逻辑重构,可直接返回预期结果(2001年,对应数量2):
WITH mystery_year_count AS ( SELECT movie.mov_year, COUNT(DISTINCT movie.mov_id) AS total_movies FROM movie$ movie JOIN movie_genres$ m_genre ON m_genre.mov_id = movie.mov_id JOIN genres$ genre ON genre.gen_id = m_genre.gen_id AND genre.gen_title = 'Mystery' -- 精准匹配目标类型,避免模糊匹配误差 GROUP BY movie.mov_year ) SELECT mov_year, total_movies FROM mystery_year_count WHERE total_movies = (SELECT MAX(total_movies) FROM mystery_year_count)
- 修正后逻辑的优势:
- 所有统计环节都在Mystery类型电影的统一集合内计算,不存在跨范围匹配的问题
- 用CTE复用同一份多表关联的筛选逻辑,不需要重复写关联代码,可读性更强
- 计数时增加
DISTINCT限定,避免单部电影因关联多个genre条目产生重复计数 - 类型匹配采用精准等值判断,统计精度更高
内容的提问来源于stack exchange,提问作者Deva
相关产品推荐
相关产品推荐

