将MySQL存储过程unluckyEmployees转换为PostgreSQL

存储过程版本 #

CREATE OR REPLACE PROCEDURE unluckyEmployees()
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT dep_name, emp_number, total_salary
    FROM (
        SELECT 
            dep_name, 
            emp_number, 
            total_salary,
            ROW_NUMBER() OVER(ORDER BY total_salary DESC, emp_number DESC, dept_id) AS seqnum
        FROM (
            SELECT 
                d.name AS dep_name,
                COUNT(e.id) AS emp_number,
                COALESCE(SUM(e.salary), 0) AS total_salary,
                d.id AS dept_id
            FROM Department d
            LEFT JOIN Employee e ON e.department = d.id
            GROUP BY d.id, d.name
            HAVING COUNT(e.id) < 6
        ) t
    ) tt
    WHERE MOD(seqnum, 2) = 1;
END;
$$;

更易用的表函数版本（推荐） #

如果需要直接返回结果集，PostgreSQL的表函数比存储过程调用更便捷：

CREATE OR REPLACE FUNCTION unluckyEmployees()
RETURNS TABLE(dep_name TEXT, emp_number INTEGER, total_salary NUMERIC)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT t.dep_name, t.emp_number, t.total_salary
    FROM (
        SELECT 
            d.name AS dep_name,
            COUNT(e.id) AS emp_number,
            COALESCE(SUM(e.salary), 0) AS total_salary,
            ROW_NUMBER() OVER(ORDER BY COALESCE(SUM(e.salary), 0) DESC, COUNT(e.id) DESC, d.id) AS seqnum
        FROM Department d
        LEFT JOIN Employee e ON e.department = d.id
        GROUP BY d.id, d.name
        HAVING COUNT(e.id) < 6
    ) t
    WHERE MOD(t.seqnum, 2) = 1;
END;
$$;

转换核心要点 #

• 序号生成：用PostgreSQL标准的ROW_NUMBER()窗口函数替代MySQL的会话变量自增，必须指定OVER(ORDER BY ...)，排序规则完全匹配原逻辑（总工资降序、员工数降序、部门ID升序），确保序号顺序一致。
• 聚合逻辑优化：用COUNT(e.id)替代原IF(e.id IS NULL, 0, COUNT(*))，因为COUNT(非空列)会自动忽略NULL值，无员工的部门直接返回0，逻辑更简洁准确。
• 空值处理：用PostgreSQL标准函数COALESCE()替代MySQL的IFNULL()，两者功能完全一致。
• 分组兼容性：PostgreSQL要求GROUP BY包含所有非聚合列（或依赖主键隐式覆盖），这里显式添加d.name分组，保证全版本兼容。

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