基于SQL中MAX()/MIN()结果提取对应列值的实现问题

问题背景 #

我有一张包含地点名称（AtSpot）及不同XY坐标的pedInfo表，需要按不同地点名称获取max(X)、max(Y)、min(X)、min(Y)，同时获取每个极值对应的另一坐标值。

表结构 #

pedInfo表结构及示例数据如下：

当前已实现的SQL #

我已经写出了获取极值的SQL语句：

SELECT DISTINCT(AtSpot), MAX(X) AS max_X, MAX(Y) AS max_Y, MIN(X) AS min_X, MIN(Y) AS min_Y FROM pedInfo GROUP BY AtSpot;

但不清楚如何获取每个极值对应的另一坐标值，期望输出如下：

解决方案 #

以下是两种可实现需求的常用方案：

方案1：子查询关联 #

通过分别查询每个极值对应的记录，再按AtSpot关联结果：

SELECT
    m.AtSpot,
    mx.x AS maxX, mx.Y AS maxX_Y,
    mn.x AS minX, mn.Y AS minX_Y,
    my.Y AS maxY, my.x AS maxY_X,
    mny.Y AS minY, mny.x AS minY_X
FROM (
    SELECT DISTINCT AtSpot FROM pedInfo
) m
LEFT JOIN (
    SELECT AtSpot, x, Y FROM pedInfo p
    WHERE x = (SELECT MAX(x) FROM pedInfo WHERE AtSpot = p.AtSpot)
) mx ON m.AtSpot = mx.AtSpot
LEFT JOIN (
    SELECT AtSpot, x, Y FROM pedInfo p
    WHERE x = (SELECT MIN(x) FROM pedInfo WHERE AtSpot = p.AtSpot)
) mn ON m.AtSpot = mn.AtSpot
LEFT JOIN (
    SELECT AtSpot, x, Y FROM pedInfo p
    WHERE Y = (SELECT MAX(Y) FROM pedInfo WHERE AtSpot = p.AtSpot)
) my ON m.AtSpot = my.AtSpot
LEFT JOIN (
    SELECT AtSpot, x, Y FROM pedInfo p
    WHERE Y = (SELECT MIN(Y) FROM pedInfo WHERE AtSpot = p.AtSpot)
) mny ON m.AtSpot = mny.AtSpot;

方案2：窗口函数（适用于MySQL 8+、PostgreSQL等支持窗口函数的数据库） #

使用ROW_NUMBER()窗口函数标记每个极值对应的行，再筛选后聚合：

WITH ranked AS (
    SELECT
        AtSpot,
        x, Y,
        ROW_NUMBER() OVER (PARTITION BY AtSpot ORDER BY x DESC) AS rn_max_x,
        ROW_NUMBER() OVER (PARTITION BY AtSpot ORDER BY x ASC) AS rn_min_x,
        ROW_NUMBER() OVER (PARTITION BY AtSpot ORDER BY Y DESC) AS rn_max_y,
        ROW_NUMBER() OVER (PARTITION BY AtSpot ORDER BY Y ASC) AS rn_min_y
    FROM pedInfo
)
SELECT
    AtSpot,
    MAX(CASE WHEN rn_max_x = 1 THEN x END) AS maxX,
    MAX(CASE WHEN rn_max_x = 1 THEN Y END) AS maxX_Y,
    MAX(CASE WHEN rn_min_x = 1 THEN x END) AS minX,
    MAX(CASE WHEN rn_min_x = 1 THEN Y END) AS minX_Y,
    MAX(CASE WHEN rn_max_y = 1 THEN Y END) AS maxY,
    MAX(CASE WHEN rn_max_y = 1 THEN x END) AS maxY_X,
    MAX(CASE WHEN rn_min_y = 1 THEN Y END) AS minY,
    MAX(CASE WHEN rn_min_y = 1 THEN x END) AS minY_X
FROM ranked
GROUP BY AtSpot;

注意：如果同一AtSpot下存在多个相同的极值（比如多行的x等于max(x)），两种方案都会只取其中一行的对应坐标。若需保留所有极值对应的记录，可将ROW_NUMBER()替换为RANK()，并调整聚合逻辑。

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