SQL分组查询：获取index目标score维度的年度全局最值记录

从Ocean_Health表中筛选出goal_id为'index'、dimension为'score'的记录，找出对应全局最高值或最低值的唯一年份与数值，结果按年份升序、数值降序排列。

数据库表结构 #

已执行的查询及结果 #

查询1（获取年度最小值） #

SELECT DISTINCT year, MIN(value) as 'Value'
from ocean_health
where dimension = 'score' AND goal_id = 'index'
group by year
ORDER BY year ASC, value DESC;

查询结果：

查询2（获取年度最大值） #

SELECT DISTINCT year, MAX(value) as 'Value'
from ocean_health
where dimension = 'score' AND goal_id = 'index'
group by year
ORDER BY year ASC, value DESC;

查询结果：

正确查询语句及期望结果 #

要获取全局最值对应的记录，需先计算全局范围内的最大、最小值，再关联原表筛选匹配项：

WITH global_stats AS (
    SELECT 
        MIN(value) AS min_val,
        MAX(value) AS max_val
    FROM ocean_health
    WHERE dimension = 'score' AND goal_id = 'index'
)
SELECT DISTINCT oh.year, oh.value AS 'Value'
FROM ocean_health oh
CROSS JOIN global_stats gs
WHERE oh.dimension = 'score' 
  AND oh.goal_id = 'index'
  AND (oh.value = gs.min_val OR oh.value = gs.max_val)
ORDER BY oh.year ASC, oh.value DESC;

期望查询结果：

内容的提问来源于stack exchange，提问作者Ho Jun Lee