PostgreSQL - Mệnh đề VỚI

Trong PostgreSQL, truy vấn WITH cung cấp một cách để viết các câu lệnh bổ trợ để sử dụng trong một truy vấn lớn hơn. Nó giúp chia nhỏ các truy vấn phức tạp và lớn thành các dạng đơn giản hơn, dễ đọc. Các câu lệnh này thường được gọi là Biểu thức Bảng chung hoặc CTE, có thể được coi là xác định các bảng tạm thời chỉ tồn tại cho một truy vấn.

Truy vấn WITH là truy vấn CTE, đặc biệt hữu ích khi truy vấn con được thực thi nhiều lần. Nó cũng hữu ích không kém thay cho các bảng tạm thời. Nó tính toán tổng hợp một lần và cho phép chúng tôi tham chiếu nó bằng tên của nó (có thể nhiều lần) trong các truy vấn.

Mệnh đề WITH phải được xác định trước khi nó được sử dụng trong truy vấn.

Cú pháp

Cú pháp cơ bản của truy vấn WITH như sau:

WITH
   name_for_summary_data AS (
      SELECT Statement)
   SELECT columns
   FROM name_for_summary_data
   WHERE conditions <=> (
      SELECT column
      FROM name_for_summary_data)
   [ORDER BY columns]

Trong đó name_for_summary_data là tên được đặt cho mệnh đề WITH. Tên_for_summary_data có thể giống với tên bảng hiện có và sẽ được ưu tiên hơn.

Bạn có thể sử dụng các câu lệnh sửa đổi dữ liệu (INSERT, UPDATE hoặc DELETE) trong WITH. Điều này cho phép bạn thực hiện một số thao tác khác nhau trong cùng một truy vấn.

Đệ quy VỚI

Truy vấn đệ quy WITH hoặc Hierarchical, là một dạng CTE trong đó CTE có thể tham chiếu đến chính nó, tức là, một truy vấn WITH có thể tham chiếu đến đầu ra của chính nó, do đó có tên là đệ quy.

Thí dụ

Xem xét bảng COMPANY có các bản ghi như sau:

testdb# select * from COMPANY;
 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

Bây giờ, chúng ta hãy viết một truy vấn bằng mệnh đề WITH để chọn các bản ghi từ bảng trên, như sau:

With CTE AS
(Select
 ID
, NAME
, AGE
, ADDRESS
, SALARY
FROM COMPANY )
Select * From CTE;

Câu lệnh PostgreSQL đã cho ở trên sẽ tạo ra kết quả sau:

id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

Bây giờ, chúng ta hãy viết một truy vấn bằng cách sử dụng từ khóa RECURSIVE cùng với mệnh đề WITH, để tìm tổng số tiền lương nhỏ hơn 20000, như sau:

WITH RECURSIVE t(n) AS (
   VALUES (0)
   UNION ALL
   SELECT SALARY FROM COMPANY WHERE SALARY < 20000
)
SELECT sum(n) FROM t;

Câu lệnh PostgreSQL đã cho ở trên sẽ tạo ra kết quả sau:

sum
-------
 25000
(1 row)

Hãy để chúng tôi viết một truy vấn bằng cách sử dụng các câu lệnh sửa đổi dữ liệu cùng với mệnh đề WITH, như hình dưới đây.

Đầu tiên, tạo một bảng COMPANY1 tương tự như bảng COMPANY. Truy vấn trong ví dụ này chuyển các hàng từ COMPANY sang COMPANY1 một cách hiệu quả. DELETE in WITH xóa các hàng được chỉ định khỏi COMPANY, trả lại nội dung của chúng bằng mệnh đề RETURNING của nó; và sau đó truy vấn chính đọc đầu ra đó và chèn nó vào BẢNG CÔNG TY1 -

CREATE TABLE COMPANY1(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

WITH moved_rows AS (
   DELETE FROM COMPANY
   WHERE
      SALARY >= 30000
   RETURNING *
)
INSERT INTO COMPANY1 (SELECT * FROM moved_rows);

Câu lệnh PostgreSQL đã cho ở trên sẽ tạo ra kết quả sau:

INSERT 0 3

Bây giờ, các bản ghi trong bảng COMPANY và COMPANY1 như sau:

testdb=# SELECT * FROM COMPANY;
 id | name  | age |  address   | salary
----+-------+-----+------------+--------
  1 | Paul  |  32 | California |  20000
  2 | Allen |  25 | Texas      |  15000
  3 | Teddy |  23 | Norway     |  20000
  7 | James |  24 | Houston    |  10000
(4 rows)


testdb=# SELECT * FROM COMPANY1;
 id | name  | age | address | salary
----+-------+-----+-------------+--------
  4 | Mark  |  25 | Rich-Mond   |  65000
  5 | David |  27 | Texas       |  85000
  6 | Kim   |  22 | South-Hall  |  45000
(3 rows)