PostgreSQL：dvdrental库中获取各门店2017年营收最高员工（禁用窗口函数）

我正在使用dvdrental数据库，需要获取2017年各门店的营收最高员工。目前的查询会返回所有员工的营收数据，但我需要每个门店仅显示1名营收最高的员工，且不能使用窗口函数。

原查询语句 #

SELECT staff.first_name, 
        staff.last_name, 
        address.address,
        SUM (payment.amount) AS revenue_generated
FROM payment
INNER JOIN staff ON staff.staff_id = payment.staff_id 
INNER JOIN store ON store.store_id = staff.store_id 
INNER JOIN address ON address.address_id = store.address_id 
WHERE EXTRACT(YEAR FROM payment.payment_date) = 2017
GROUP BY staff.staff_id, address.address_id
ORDER BY revenue_generated DESC;

当前查询结果 #

Hanna   Carry    28 MySQL Boulevard 79736.45
Hanna   Rainbow  47 MySakila Drive  40537.94
Peter   Lockyard 47 MySakila Drive  40077.97
Jon     Stephens 28 MySQL Boulevard 33927.04
Mike    Hillyer  47 MySakila Drive  33489.47

期望结果（不使用LIMIT） #

Hanna   Carry    28 MySQL Boulevard 79736.45
Hanna   Rainbow  47 MySakila Drive  40537.94

解决方案 #

由于不能使用窗口函数，我们可以通过嵌套子查询实现需求：

1. 先统计每个员工的2017年营收及所属门店信息
2. 再计算每个门店的最高营收值
3. 最后将两个结果关联，筛选出每个门店中营收等于该门店最高值的员工

最终查询语句 #

SELECT s.first_name, 
       s.last_name, 
       a.address,
       emp_revenue.revenue_generated
FROM (
    -- 统计每个员工的2017年营收及所属门店
    SELECT 
        staff.staff_id,
        store.store_id,
        SUM(payment.amount) AS revenue_generated
    FROM payment
    INNER JOIN staff ON staff.staff_id = payment.staff_id
    INNER JOIN store ON store.store_id = staff.store_id
    WHERE EXTRACT(YEAR FROM payment.payment_date) = 2017
    GROUP BY staff.staff_id, store.store_id
) emp_revenue
INNER JOIN (
    -- 统计每个门店的最高营收
    SELECT 
        store.store_id,
        MAX(emp_rev.revenue_generated) AS max_store_revenue
    FROM (
        SELECT 
            staff.staff_id,
            store.store_id,
            SUM(payment.amount) AS revenue_generated
        FROM payment
        INNER JOIN staff ON staff.staff_id = payment.staff_id
        INNER JOIN store ON store.store_id = staff.store_id
        WHERE EXTRACT(YEAR FROM payment.payment_date) = 2017
        GROUP BY staff.staff_id, store.store_id
    ) emp_rev
    GROUP BY store.store_id
) store_max_revenue 
    ON emp_revenue.store_id = store_max_revenue.store_id 
    AND emp_revenue.revenue_generated = store_max_revenue.max_store_revenue
INNER JOIN staff s ON emp_revenue.staff_id = s.staff_id
INNER JOIN store st ON s.store_id = st.store_id
INNER JOIN address a ON st.address_id = a.address_id;

结果说明 #

这个查询会精准匹配每个门店中营收最高的员工，输出与你期望的结果一致，且不需要使用LIMIT或窗口函数。

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