同表关联查询时，如何将关联结果按独立行展示而非独立列？

方案1：基于现有查询改造 #

你原有的JOIN逻辑已经匹配到了所有id连续且分数均≥80的id对，只需要将两张关联表的记录拆为独立行再合并去重即可：

SELECT DISTINCT id, marks
FROM (
    -- 取出连续对中前一个id的记录
    SELECT s1.id, s1.marks
    FROM `Students` s1
    JOIN `Students` s2 
      ON s2.id = s1.id + 1 
      AND s1.marks >= 80 
      AND s2.marks >= 80
    UNION ALL
    -- 取出连续对中后一个id的记录
    SELECT s2.id, s2.marks
    FROM `Students` s1
    JOIN `Students` s2 
      ON s2.id = s1.id + 1 
      AND s1.marks >= 80 
      AND s2.marks >= 80
) t
ORDER BY id ASC;

方案2：窗口函数实现（性能更优） #

使用LAG/LEAD窗口函数一次扫描就能拿到相邻行的分数做判断，逻辑更简洁，执行效率更高：

WITH qualify_stu AS (
    SELECT 
        id,
        marks,
        LAG(marks) OVER (ORDER BY id) AS prev_mark,
        LEAD(marks) OVER (ORDER BY id) AS next_mark
    FROM `Students`
)
SELECT id, marks
FROM qualify_stu
WHERE marks >= 80 
AND (prev_mark >= 80 OR next_mark >= 80)
ORDER BY id ASC;

上述两种方案在你的样例数据上执行，都会输出id为3、4、6、7、8的对应记录，完全符合预期。

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