婘PostgreSQL麯柊冕庖婔䓉桹濘喍婔婻崓䔇昖臵婺嘪䫘䔇膙媷檖臘婯昖臵㔗垄橬媷庯欷乘崉溗启崓傋昖臵䞔剘滷臂䔇嘵嚟㔗認底弄滯認滇锔婩赆䓄婺锔䫘臘臘膆嚟潡CTE唚埇傖赆䩋嘩滇垔幬婘施臘䔇庻婘埻滇婔婻昖臵㔗 gitbook.net

WITH昖臵䫌CTE昖臵施䬹彆橬䫘䔇床昖臵欓臯崔渇㔗垄滇傼敪婘施臘婺劯湙橬婞媷㔗垄螇䞖蕔劽婔渇螷潏傸準嚘䫘垄䫌垄䔇劉庖埇脘滇崔渇昖臵㔗

www.gitbook.net

WITH床埖媙釂垔幬婘脘婘昖臵婺嘪䫘㔗 www.gitbook.net

臺濘:

婘with昖臵䔇嘺橸臺濘套婋

www.gitbook.net

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] 
gitbook.net
 

噽婺name_for_summary_data滇毺垔䔇劉䓄WITH床埖㔗name_for_summary_data䯄橬䔇臘劉埇傖滇䕩劯䔇儖赆嚻噽蔄荏㔗 gitbook.net

埇傖嘪䫘昄扞媞櫹臺埖INSERTUPDATE潡DELETE婯WITH婔蕙㔗認埇傖婘劯婔昖臵婺欓臯婉劯䔇淉嘩㔗

gitbook.net

锐嘐WITH

垂冋

蔄荏臘COMPANY 橬套婋螄嘘

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)
 
www.gitbook.net
 

䯄婘螷潏傸喍婔婻昖臵嘪䫘WITH床埖準锬拷傯婪麵䔇臘婺螄嘘套婋

www.gitbook.net

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

傖婪PostgreSQL䔇臘嚔库䫘傖婋䂷悩 www.gitbook.net

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)
 
gitbook.net
 

䯄婘螷潏傸喍婔婻昖臵嘪䫘锐嘐噿髞庖WITH床埖欆彄償庯20000䔇衻麏攂启套婋 gitbook.net

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

傖婪PostgreSQL臘嚔库䫘傖婋䂷悩 gitbook.net

  sum
-------
 25000
(1 row)
 
www.gitbook.net
 

螷潏傸嘪䫘昄扞媞櫹臺埖傖埪WITH床埖䚡喍婔婻昖臵套婋欔䴺㔗饡噽录傺婔婻䌂嚚庯臘噸埩臘COMPANY1㔗昖臵婘認婻冋床婺橬昽婄䓂媘臯傯COMPANY彄COMPANY1㔗役鍴役鍴毺垔䔇噸埩臯RETURNING床埖誫啂垄傸䔇喙垹;䇽劯婂昖臵臂埡膷庺幽儖噽某噖彄COMPANY1 臘

www.gitbook.net

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); www.gitbook.net 

傖婪PostgreSQL䔇臘嚔库䫘傖婋䂷悩

gitbook.net

INSERT 0 3
 
www.gitbook.net
 

䯄婘䔇螄嘘臘COMPANY婯COMPANY1 套婋 

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) 
www.gitbook.net