You need to enable JavaScript to run this app. 不修改表结构的SQL实现:查找A/B/C类最近有记录月份的Top5
优惠活动
大模型
产品
解决方案
定价
更多

不修改表结构的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

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级,最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分), 限时 9.9 元起

最近更新时间:2026.10.30 21:36:03