postgresql group by first 的三種寫法

在sql 查詢過程中經(jīng)常有一種查詢，某一類別的第一名 比如：

limingyao=# \d employee
                Table "public.employee"
   Column   |  Type   | Collation | Nullable | Default
------------+---------+-----------+----------+---------
 id         | integer |           |          |
 name       | text    |           |          |
 department | text    |           |          |
 salary     | integer |           |          |

查詢 employee表每一個部門工資最高的員工信息

  1. 第一種最直觀的寫法
SELECT  
    *
FROM
    employee
WHERE
    (department, salary) IN (
        SELECT 
            department,
            MAX(salary)
        FROM
            employee
        GROUP BY
            department
    )
ORDER BY
    department;
  1. 使用窗口函數(shù) row_number()
WITH ranked_employees AS (
    SELECT 
        ROW_NUMBER() OVER (
           PARTITION BY department ORDER BY salary DESC
        ) AS rn,
        *
    FROM 
        employee
)
SELECT
    *
FROM
    ranked_employees
WHERE
    rn = 1
ORDER BY 
    department;
  1. 使用 distinct on
SELECT DISTINCT ON (department)
    *
FROM
    employee
ORDER BY
    department,
    salary DESC;

Note: 這三種寫法結(jié)果是不相同?

https://stackoverflow.com/questions/3800551/select-first-row-in-each-group-by-group

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時請結(jié)合常識與多方信息審慎甄別。
平臺聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡書系信息發(fā)布平臺，僅提供信息存儲服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容