MySQL如何实现薪资最高与最低、次高与次低逐行配对输出

• 未限制两表关联条件，直接笛卡尔积连接会生成所有满足筛选条件的行组合，产生大量冗余数据
• 筛选条件逻辑完全错误：E1.salary < (select max(salary) from employees) 会直接排除最高薪资行，E2.salary < (select min(salary) from employees) 是恒假条件，不存在比最低工资还低的薪资，整体筛选逻辑完全和需求相反
• 未给薪资做排序序号标记，无法实现第N高薪资和第N低薪资的一一匹配规则

实现思路：先给所有员工数据分别按薪资降序、升序生成排序序号，再关联相同序号的高低薪资行，最后仅保留前半部分序号的行避免重复配对。

MySQL 8.0+ 窗口函数版本（兼容大部分在线编译器） #

WITH ranked_salary AS (
    SELECT 
        Name,
        Salary,
        -- 降序排名：值越大排名越靠前
        ROW_NUMBER() OVER (ORDER BY Salary DESC) AS desc_rank,
        -- 升序排名：值越小排名越靠前
        ROW_NUMBER() OVER (ORDER BY Salary ASC) AS asc_rank
    FROM employees
)
SELECT 
    h.Name AS Name,
    h.Salary AS salary_highest,
    l.Name AS name,
    l.Salary AS salary_Lowest
FROM ranked_salary h
INNER JOIN ranked_salary l 
    ON h.desc_rank = l.asc_rank
-- 仅取前半部分排名，避免重复配对，奇数总行数时可自行调整是否保留中间行
WHERE h.desc_rank <= (SELECT CEIL(COUNT(*)/2) FROM employees)

MySQL 5.x 兼容版本（无窗口函数支持时使用） #

SELECT 
    h.Name AS Name,
    h.Salary AS salary_highest,
    l.Name AS name,
    l.Salary AS salary_Lowest
FROM 
    (SELECT @dr := @dr + 1 AS desc_rank, Name, Salary 
     FROM employees, (SELECT @dr := 0) init 
     ORDER BY Salary DESC) h
INNER JOIN 
    (SELECT @ar := @ar + 1 AS asc_rank, Name, Salary 
     FROM employees, (SELECT @ar := 0) init 
     ORDER BY Salary ASC) l
ON h.desc_rank = l.asc_rank
WHERE h.desc_rank <= (SELECT CEIL(COUNT(*)/2) FROM employees)

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