PostgreSQL WITH 子句

In PostgreSQL, la clausola WITH fornisce un metodo per scrivere istruzioni ausiliarie da utilizzare in query più grandi.

La clausola WITH aiuta a decomporre query complesse e grandi in forme più semplici, facilitando la lettura. Queste istruzioni sono solitamente chiamate espressioni tabellari comuni (Common Table Expressions, CTE) e possono anche essere considerate come una tabella temporanea esistente per la query.

La clausola WITH è particolarmente utile quando si eseguono subquery più volte, permettendo di riferirsi a essa tramite il suo nome (che potrebbe essere più volte) nella query.

La clausola WITH deve essere definita prima di essere utilizzata.

Sintassi

La sintassi di base per la query WITH è la seguente:

WITH
　　　name_for_summary_data AS (
　　　　　　Statement SELECT)
　　　SELECT colonne
　　　FROM name_for_summary_data
　　　WHERE condizioni <=> (
　　　　　　SELECT colonna
　　　　　　FROM name_for_summary_data)
　　　[ORDER BY colonne]

name_for_summary_data è il nome della clausola WITH,name_for_summary_data Può avere lo stesso nome della tabella esistente e avere priorità.

È possibile utilizzare istruzioni INSERT, UPDATE o DELETE nel WITH, permettendo di eseguire diverse operazioni diverse nella stessa query.

WITH ricorsivo

I dati di output possono essere utilizzati nella clausola WITH.

L'espressione tabellare comune (CTE) ha un vantaggio importante, ovvero essere in grado di riferirsi a se stessa, creando un CTE ricorsivo. Un CTE ricorsivo è un'espressione tabellare comune che esegue ripetutamente l'CTE iniziale per restituire un sottoinsieme di dati fino a ottenere l'insieme completo dei risultati.

Esempio online

Crea la tabella COMPANY (Scarica il file SQL di COMPANY ），i dati sono i seguenti：

w3codeboxdb# 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 righe)

Di seguito utilizzeremo il prefisso WITH per eseguire una query sulla tabella superiore:

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

Risultato ottenuto come segue:

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 righe)

In seguito utilizzeremo RECURSIVE Utilizzando il termine chiave e il prefisso WITH per eseguire una query per trovare: SALARY (stipendio) I dati con campo inferiore a 20000 e calcolare la loro somma:

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

Risultato ottenuto come segue:

　somma
-------
　25000
(1 riga)

Di seguito creiamo una tabella simile a COMPANY chiamata COMPANY1, utilizzando l'istruzione DELETE e il prefisso WITH per eliminare i dati dalla tabella COMPANY: SALARY (stipendio) I dati con campo maggiore o uguale a 30000 verranno eliminati e inseriti nella tabella COMPANY1, per realizzare il trasferimento dei dati dalla tabella COMPANY alla tabella COMPANY1:

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);

Risultato ottenuto come segue:

INSERT 0 3

Al momento, i dati delle tabelle CAMPANY e CAMPANY1 sono i seguenti:

w3codeboxdb=# 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 righe)
w3codeboxdb=# 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 righe)