Clause WITH PostgreSQL

Dans PostgreSQL, la clause WITH offre une méthode pour écrire des statements d'assistance, permettant leur utilisation dans des requêtes plus grandes.

La clause WITH aide à décomposer des requêtes complexes et volumineuses en des formes plus simples, ce qui facilite leur lecture. Ces expressions sont généralement appelées Expressions Table Courantes (ETC) et peuvent également être considérées comme une table temporaire existante pour la requête.

La clause WITH est particulièrement utile lorsque plusieurs sous-requêtes sont exécutées, permettant de faire référence à elle par son nom (peut-être plusieurs fois) dans la requête.

La clause WITH doit être définie avant d'être utilisée.

Syntaxe

La syntaxe de base d'une requête WITH est la suivante :

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

name_for_summary_data est le nom de la clause WITH,name_for_summary_data Peut être le même que le nom de la table existante et avoir la priorité.

On peut utiliser des instructions INSERT, UPDATE ou DELETE dans WITH, ce qui permet d'exécuter plusieurs opérations différentes dans la même requête.

WITH récursif

On peut utiliser les données de sortie de la clause WITH.

L'expression régulière de table (CTE) a une grande avantage, c'est qu'elle peut se référer à elle-même, permettant de créer une CTE récursive. Une CTE récursive est une expression régulière de table公用 qui exécute à nouveau l'expression régulière initiale pour renvoyer un sous-ensemble de données jusqu'à ce que l'ensemble de résultats complet soit obtenu.

Exemple en ligne

Créer la table COMPANY (Télécharger le fichier SQL COMPANY ），le contenu des données est le suivant :

w3codeboxdb# select　*　from COMPANY;
　id | nom | âge | adresse | salaire
----+-------+-----+-----------+--------
　　1　|　Paul　　|　　32　| Californie|　　20000
　　2　|　Allen　|　　25　| Texas |　　15000
　　3　|　Teddy　|　　23　| Norvège |　　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)

Nous allons utiliser la clause WITH pour interroger les données du tableau ci-dessus :

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

Résultat obtenu comme suit :

id | nom | âge | adresse | salaire
----+-------+-----+-----------+--------
　　1　|　Paul　　|　　32　| Californie|　　20000
　　2　|　Allen　|　　25　| Texas |　　15000
　　3　|　Teddy　|　　23　| Norvège |　　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)

Ensuite, utilisons RECURSIVE Écrire une requête avec le mot-clé et la clause WITH pour trouver SALARY( salaire) le champ est inférieur à 2Calculer la somme des données de 0000 :

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

Résultat obtenu comme suit :

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

Nous allons créer une table similaire à la table COMPANY appelée COMPANY1 Table, utiliser la phrase DELETE et la clause WITH pour supprimer les données de la table COMPANY SALARY( salaire) champ supérieur ou égal à 30000 des données, et insérer les données supprimées dans COMPANY1 Table, pour réaliser le transfert des données de la table COMPANY vers COMPANY1 Dans le tableau :

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

Résultat obtenu comme suit :

INSERT　0　3

À ce moment-là, la table CAMPANY et CAMPANY1 Les données du tableau sont les suivantes :

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