在线午夜精品自拍小视频_无码av无码专区线_亚洲无码精品人妻_人人澡欧美一区

MySQL 將FROM中的子查詢稱為派生表（Derived Table）。以下查詢使用了一個派生表：

SELECT * FROM (SELECT 1) AS dt;
+---+
| 1 |
+---+
| 1 |
+---+

不過，MySQL 中的派生表存在一些限制：

    派生表不能是關聯(lián)子查詢
    派生表不能引用它所在的SELECT語句中的其他表
    在 MySQL 8.0.14 之前，派生表不能引用它所在的SELECT語句外部的表

簡單來說，就是派生表必須能夠單獨運行，而不能依賴其他表。

從 MySQL 8.0.14 開始，派生表支持LATERAL關鍵字前綴，表示允許派生表引用它所在的FROM子句中的其他表。橫向派生表能夠完成普通派生表無法完成或者效率低下的操作。

考慮以下應用場景：departments 表存儲了部門的信息，employees 表存儲了員工信息。如何查找每個部門中薪水最高的 Top 5 和對應的員工？

示例表和數(shù)據(jù)
 

首先，使用傳統(tǒng)的方法很難實現(xiàn)這樣的功能：

SELECT d.department_name,
       (SELECT e.salary
          FROM employees e
         WHERE e.department_id = d.department_id
         ORDER BY e.salary DESC LIMIT 5
       )
  FROM departments d;
ERROR 1242 (21000): Subquery returns more than 1 row

以上查詢失敗的原因在于SELECT子查詢只能返回 1 條數(shù)據(jù)。

按照需求，我們可以先按照部門編號對員工信息進行分組，獲得每個組內(nèi)的薪水最高的 5 個員工，然后和部門表進行連接查詢：

SELECT d.department_name, t.first_name, t.last_name, t.salary
  FROM departments d
  LEFT JOIN (SELECT e.department_id, e.first_name, e.last_name, e.salary
               FROM employees e
              WHERE e.department_id = d.department_id
              ORDER BY e.salary DESC LIMIT 5) t
    ON d.department_id = t.department_id
 ORDER BY d.department_name, t.salary DESC;
ERROR 1054 (42S22): Unknown column 'd.department_id' in 'where clause'

以上語句失敗的原因在于子查詢 t 不能引用外部查詢中的 departments 表。

可以利用 MySQL 中的自定義變量實現(xiàn)該功能：

SELECT d.department_name, w.first_name, w.last_name, w.salary, w.rn
  FROM departments d
  LEFT JOIN (
        SELECT *
          FROM (
               SELECT a.*, if(@did = a.department_id, @rn := @rn+1, @rn := 1) AS rn, @did := a.department_id AS did
                 FROM (SELECT e.* FROM employees e ORDER BY department_id, salary DESC) a,
                      (SELECT @rn := 0 rn, @did := 0) b
               ) AS t
         WHERE t.rn <= 5
        ) AS w
    ON d.department_id = w.department_id
 ORDER BY d.department_name, w.salary DESC;
+----------------------+-------------+-----------+----------+------+
| department_name      | first_name  | last_name | salary   | rn   |
+----------------------+-------------+-----------+----------+------+
| Accounting           | Shelley     | Higgins   | 12008.00 |    1 |
| Accounting           | William     | Gietz     |  8300.00 |    2 |
| Administration       | Jennifer    | Whalen    |  4400.00 |    1 |
| Benefits             | NULL        | NULL      |     NULL | NULL |
| Construction         | NULL        | NULL      |     NULL | NULL |
| Contracting          | NULL        | NULL      |     NULL | NULL |
| Control And Credit   | NULL        | NULL      |     NULL | NULL |
| Corporate Tax        | NULL        | NULL      |     NULL | NULL |
| Executive            | Steven      | King      | 24000.00 |    1 |
| Executive            | Neena       | Kochhar   | 17000.00 |    2 |
| Executive            | Lex         | De Haan   | 17000.00 |    3 |
| Finance              | Nancy       | Greenberg | 12008.00 |    1 |
| Finance              | Daniel      | Faviet    |  9000.00 |    2 |
| Finance              | John        | Chen      |  8200.00 |    3 |
| Finance              | Jose Manuel | Urman     |  7800.00 |    4 |
| Finance              | Ismael      | Sciarra   |  7700.00 |    5 |
| Government Sales     | NULL        | NULL      |     NULL | NULL |
| Human Resources      | Susan       | Mavris    |  6500.00 |    1 |
...
51 rows in set (0.00 sec)

可以看出，這種方法比較復雜，也不具有通用性。

現(xiàn)在來看一下如何使用LATERAL派生表實現(xiàn)這個需求：

SELECT d.department_name, t.first_name, t.last_name, t.salary
  FROM departments d
  LEFT JOIN LATERAL (SELECT e.department_id, e.first_name, e.last_name, e.salary
               FROM employees e
              WHERE e.department_id = d.department_id
              ORDER BY e.salary DESC LIMIT 5) t
    ON d.department_id = t.department_id
 ORDER BY d.department_name, t.salary DESC;
+----------------------+-------------+-----------+----------+------+
| department_name      | first_name  | last_name | salary   | rn   |
+----------------------+-------------+-----------+----------+------+
| Accounting           | Shelley     | Higgins   | 12008.00 |    1 |
| Accounting           | William     | Gietz     |  8300.00 |    2 |
| Administration       | Jennifer    | Whalen    |  4400.00 |    1 |
| Benefits             | NULL        | NULL      |     NULL | NULL |
| Construction         | NULL        | NULL      |     NULL | NULL |
| Contracting          | NULL        | NULL      |     NULL | NULL |
| Control And Credit   | NULL        | NULL      |     NULL | NULL |
| Corporate Tax        | NULL        | NULL      |     NULL | NULL |
| Executive            | Steven      | King      | 24000.00 |    1 |
| Executive            | Neena       | Kochhar   | 17000.00 |    2 |
| Executive            | Lex         | De Haan   | 17000.00 |    3 |
| Finance              | Nancy       | Greenberg | 12008.00 |    1 |
| Finance              | Daniel      | Faviet    |  9000.00 |    2 |
| Finance              | John        | Chen      |  8200.00 |    3 |
| Finance              | Jose Manuel | Urman     |  7800.00 |    4 |
| Finance              | Ismael      | Sciarra   |  7700.00 |    5 |
| Government Sales     | NULL        | NULL      |     NULL | NULL |
| Human Resources      | Susan       | Mavris    |  6500.00 |    1 |
...
51 rows in set (0.00 sec)

以上語句基于前面的一個失敗示例，只是在LEFT JOIN之后，派生表之前加上一個LATERAL關鍵字，使得子查詢 t 能夠引用前面的 departments 表。這種方法能夠?qū)崿F(xiàn)很多類似的復雜功能，簡單而且高效。

MySQL 從 8.0.14 開始支持橫向派生表，同時存在以下限制：

    橫向派生表只能出現(xiàn)在FROM子句中，包括使用逗號分隔的表或者標準的連接語句（JOIN、INNER JOIN、CROSS JOIN、LEFT [OUTER] JOIN以及RIGHT [OUTER] JOIN）。

    如果橫向派生表位于連接操作的右側(cè)，并且引用了左側(cè)的表，連接類型必須為INNER JOIN、CROSS JOIN或者LEFT [OUTER] JOIN。

    如果橫向派生表位于連接操作的左側(cè)，并且引用了右側(cè)的表，連接類型必須為INNER JOIN、CROSS JOIN或者RIGHT [OUTER] JOIN。

    如果橫向派生表引用了聚合函數(shù)，那么該函數(shù)的聚合查詢語句不能是橫向派生表所在的FROM子句所屬的查詢語句。

    根據(jù) SQL 標準，表函數(shù)擁有一個隱式的LATERAL，這與 MySQL 8.0 到 MySQL 8.0.14 之前版本的實現(xiàn)一致。但是，根據(jù)標準，函數(shù) JSON_TABLE() 之前不能存在LATERAL關鍵字，包括隱式的LATERAL。

當然，也可以使用 MySQL 8.0 中新增的窗口函數(shù)完成相同的功能：

SELECT *
  FROM (SELECT d.department_name, e.first_name, e.last_name, e.salary,
               row_number() OVER (PARTITION BY d.department_id ORDER BY e.salary DESC) AS rn
          FROM departments d
          LEFT JOIN employees e
            ON (e.department_id = d.department_id)
       ) AS t
 WHERE t.rn <= 5
 ORDER BY t.department_name,t.rn;
+----------------------+-------------+-----------+----------+----+
| department_name      | first_name  | last_name | salary   | rn |
+----------------------+-------------+-----------+----------+----+
| Accounting           | Shelley     | Higgins   | 12008.00 |  1 |
| Accounting           | William     | Gietz     |  8300.00 |  2 |
| Administration       | Jennifer    | Whalen    |  4400.00 |  1 |
| Benefits             | NULL        | NULL      |     NULL |  1 |
| Construction         | NULL        | NULL      |     NULL |  1 |
| Contracting          | NULL        | NULL      |     NULL |  1 |
| Control And Credit   | NULL        | NULL      |     NULL |  1 |
| Corporate Tax        | NULL        | NULL      |     NULL |  1 |
| Executive            | Steven      | King      | 24000.00 |  1 |
| Executive            | Neena       | Kochhar   | 17000.00 |  2 |
| Executive            | Lex         | De Haan   | 17000.00 |  3 |
| Finance              | Nancy       | Greenberg | 12008.00 |  1 |
| Finance              | Daniel      | Faviet    |  9000.00 |  2 |
| Finance              | John        | Chen      |  8200.00 |  3 |
| Finance              | Jose Manuel | Urman     |  7800.00 |  4 |
| Finance              | Ismael      | Sciarra   |  7700.00 |  5 |
| Government Sales     | NULL        | NULL      |     NULL |  1 |
| Human Resources      | Susan       | Mavris    |  6500.00 |  1 |
...
51 rows in set (0.00 sec)

相關文檔：
MySQL 8.0 橫向派生表

其他數(shù)據(jù)庫產(chǎn)品的功能實現(xiàn)：
Oracle 18c 橫向（LATERAL）內(nèi)聯(lián)視圖
Oracle 18c APPLY 連接
SQL Server 2017 APPLY 運算符
PostgreSQL 11 橫向（LATERAL）子查詢
Db2 11 橫向（LATERAL）連接