求和視窗子句的決議函式及方法-有解無憂

我正在使用 Oracle 和 SQL Developer。我已經下載了 HR 模式，需要用它做一些查詢。現在我正在使用表 Employees。作為用戶，我需要每個部門工資最高的 3 名員工的工資總和。我已經完成查詢以定義每個部門薪水最高的 3 名員工：

SELECT
    *
FROM
    (
        SELECT
            employee_id,
            first_name
            || ' '
            || last_name,
            department_id,
            salary,
            ROW_NUMBER()
            OVER(PARTITION BY department_id
                 ORDER BY
                     salary DESC  
                 --ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING     
            ) result
        FROM
            employees
    )
WHERE
    result <= 3;

我需要使用視窗子句的方式。我做了這樣的事情：

SELECT
    department_id,
    SUM(salary)
    OVER (PARTITION BY department_id ORDER BY salary       
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) total_sal
FROM
    (
        SELECT
            employee_id,
            first_name
            || ' '
            || last_name,
            department_id,
            salary,
            ROW_NUMBER()
            OVER(PARTITION BY department_id
                 ORDER BY
                     salary DESC  
                 --ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING     
            ) result
        FROM
            employees
    )
WHERE
    result <= 3;

這是結果：

求和視窗子句的決議函式及方法

它具有部門中 3 人的必要總和和 2 人的其他不必要的結果，依此類推。我需要這樣的結果：

求和視窗子句的決議函式及方法

如何修改我的查詢以接收適當的結果（我需要使用視窗子句和分析函式）？

uj5u.com熱心網友回復：

您想要聚合而不是在外部查詢中開窗：

SELECT
    department_id,
    SUM(salary) total_sal
FROM
    (
        SELECT
            employee_id,
            first_name
            || ' '
            || last_name,
            department_id,
            salary,
            ROW_NUMBER()
            OVER(PARTITION BY department_id
                 ORDER BY
                     salary DESC  
                 --ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING     
            ) result
        FROM
            employees
    ) e
WHERE
    result <= 3
GROUP BY department_id

如果我們只使用視窗函式執行相同的任務，那么，從現有查詢開始，我們可以添加另一層某種嵌套，或者使用WITH TIES. 兩者都追求相同的效果，就是將結果限制在每組一行。

后者看起來像：

SELECT 
    department_id,
    SUM(salary) OVER(PARTITION BY department_id) total_sal
FROM (
        SELECT e.*, 
            ROW_NUMBER() OVER(PARTITION BY department_id ORDER BY salary DESC) result
        FROM employees e
) e
WHERE result <= 3
ORDER BY result FETCH FIRST ROW WITH TIES

而前者會說成：

SELECT department_id, total_sal
FROM (
    SELECT e.*,
        SUM(salary) OVER(PARTITION BY department_id) total_sal
    FROM (
        SELECT e.*, 
            ROW_NUMBER() OVER(PARTITION BY department_id ORDER BY salary DESC) result
        FROM employees e
    ) e
    WHERE result <= 3
) e
where result = 1

轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/534408.html

標籤：数据库甲骨文聚合函数每组最大n窗口函数

上一篇：Oracle變長子串函式

下一篇：如何解決錯誤“System.InvalidCastException-列包含NULL資料”