不修改表结构的SQL实现:查找A/B/C类最近有记录月份的Top5
现有数据库查询
(SELECT * FROM `annon` as a LEFT JOIN `users` as u ON a.id = u.id WHERE a.year = '" . $currentYear . "' AND a.category = 'A_" . $currentMonth . "' GROUP BY a.category, u.name ORDER BY a.points DESC LIMIT 5) UNION (SELECT * FROM `annon` as a LEFT JOIN `users` as u ON a.id = u.id WHERE a.year = '" . $currentYear . "' AND a.category = 'B_" . $currentMonth . "' GROUP BY a.category, u.name ORDER BY a.points DESC LIMIT 5) UNION (SELECT * FROM `annon` as a LEFT JOIN `users` as u ON a.id = u.id WHERE a.year = '" . $currentYear . "' AND a.category = 'C_" . $currentMonth . "' GROUP BY a.category, u.name ORDER BY a.points DESC LIMIT 5)
分类存储规则
当前查询的目标是获取A、B、C三个分类下的Top5积分记录,分类字段的存储形式为「分类标识_月份编号」,例如:
A_1 // 1月的A类记录,月份取值范围1-12 B_1 // 1月的B类记录 C_1 // 1月的C类记录
实现约束:不允许修改annon表的现有结构
已实现的查询逻辑
目前在WHERE子句中通过CASE WHEN EXISTS语法,实现了当月无数据时自动查询上一月的逻辑,核心代码片段为:
WHERE a.year = '" . $currentYear . "' AND (CASE WHEN EXISTS(SELECT id FROM `annon` WHERE year = '" . $currentYear . "' AND category = 'A_" . $currentMonth . "') THEN a.category = 'A_" . $currentMonth . "' ELSE a.category = 'A_" . ($currentMonth-1) . "' END)
该逻辑已覆盖的正常场景:
- 当月存在对应分类记录时,直接查询当月数据
- 当月无对应分类记录时,自动查询上一月数据
对应的可直接运行的测试版完整查询为:
(SELECT * FROM `annon` as a LEFT JOIN `users` as u ON a.id = u.id WHERE a.year = '2021' AND (CASE WHEN EXISTS(SELECT id FROM `annon` WHERE year = '2021' AND category = 'A_3') THEN a.category = 'A_3' ELSE a.category = 'A_2' END) GROUP BY a.category, u.name ORDER BY a.points DESC LIMIT 5) UNION (SELECT * FROM `annon` as a LEFT JOIN `users` as u ON a.id = u.id WHERE a.year = '2021' AND (CASE WHEN EXISTS(SELECT id FROM `annon` WHERE year = '2021' AND category = 'B_3') THEN a.category = 'B_3' ELSE a.category = 'B_2' END) GROUP BY a.category, u.name ORDER BY a.points DESC LIMIT 5) UNION (SELECT * FROM `annon` as a LEFT JOIN `users` as u ON a.id = u.id WHERE a.year = '2021' AND (CASE WHEN EXISTS(SELECT id FROM `annon` WHERE year = '2021' AND category = 'C_3') THEN a.category = 'C_3' ELSE a.category = 'C_2' END) GROUP BY a.category, u.name ORDER BY a.points DESC LIMIT 5)
现存问题与核心需求
当前方案仅支持向前回溯1个月的数据,若当月、上一月都没有对应分类的有效记录,查询会返回空结果。
核心需求:无需通过硬编码嵌套多层CASE实现校验,自动向前遍历查找距离当前月份最近的、存在有效记录的月份,最终返回该月份对应分类下按积分倒序排列的Top5记录。
内容的提问来源于stack exchange,提问作者ossUter
相关产品推荐
相关产品推荐

