湖兆福彩快三走势图—官方网址22270.COM鈥擯ostgreSQL WITH 瀛愬彞

鍦 湖兆福彩快三走势图—官方网址22270.COM鈥擯ostgreSQL 涓紝WITH 瀛愬彞鎻愪緵浜嗕竴绉嶇紪鍐欒緟鍔╄鍙ョ殑鏂规硶锛屼互渚垮湪鏇村ぇ鐨勬煡璇腑浣跨敤銆

WITH 瀛愬彞鏈夊姪浜庡皢澶嶆潅鐨勫ぇ鍨嬫煡璇㈠垎瑙d负鏇寸畝鍗曠殑琛ㄥ崟锛屼究浜庨槄璇汇湖兆福彩快三走势图—官方网址22270.COM杩欎簺璇彞閫氬父绉颁负閫氱敤琛ㄨ〃杈惧紡锛圕ommon Table Express锛 CTE锛夛紝涔熷彲浠ュ綋鍋氫竴涓负鏌ヨ鑰屽瓨鍦ㄧ殑涓存椂琛ㄣ

WITH 瀛愬彞鏄湪澶氭鎵ц瀛愭煡璇㈡椂鐗瑰埆鏈夌敤锛屽厑璁告垜浠湪鏌ヨ涓氳繃瀹冪殑鍚嶇О(鍙兘鏄娆)寮曠敤瀹冦

湖兆福彩快三走势图—官方网址22270.COMWITH 瀛愬彞鍦ㄤ娇鐢ㄥ墠蹇呴』鍏堝畾涔夈

璇硶

WITH 鏌ヨ鐨勫熀纭璇硶濡備笅锛

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]

name_for_summary_data 鏄 WITH 瀛愬彞鐨勫悕绉帮紝name_for_summary_data湖兆福彩快三走势图—官方网址22270.COM鍙互涓庣幇鏈夌殑琛ㄥ悕鐩稿悓锛屽苟涓斿叿鏈変紭鍏堢骇銆

鍙互鍦 WITH 涓娇鐢ㄦ暟鎹 INSERT, UPDATE 鎴 DELETE 璇彞锛屽厑璁告偍鍦ㄥ悓涓涓煡璇腑鎵ц澶氫釜涓嶅悓鐨勬搷浣溿

WITH 閫掑綊

鍦 WITH 瀛愬彞涓彲浠ヤ娇鐢ㄨ嚜韬緭鍑虹殑鏁版嵁銆

鍏敤琛ㄨ〃杈惧紡 (CTE) 鍏锋湁涓涓噸瑕佺殑浼樼偣锛岄偅灏辨槸鑳藉寮曠敤鍏惰嚜韬紝浠庤屽垱寤洪掑綊 CTE銆傞掑綊 CTE 鏄竴涓噸澶嶆墽琛屽垵濮 CTE 浠ヨ繑鍥炴暟鎹瓙闆嗙洿鍒拌幏鍙栧畬鏁寸粨鏋滈泦鐨勫叕鐢ㄨ〃琛ㄨ揪寮忋

瀹炰緥

鍒涘缓 COMPANY 琛紙涓嬭浇 COMPANY SQL 鏂囦欢 锛夛紝鏁版嵁鍐呭濡備笅锛

runoobdb# 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)

涓嬮潰灏嗕娇鐢 WITH 瀛愬彞鍦ㄤ笂琛ㄤ腑鏌ヨ鏁版嵁锛

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

寰楀埌缁撴灉濡備笅锛

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)

鎺ヤ笅鏉ヨ鎴戜滑浣跨敤 RECURSIVE 鍏抽敭瀛楀拰 WITH 瀛愬彞缂栧啓涓涓煡璇紝鏌ユ壘 SALARY(宸ヨ祫) 瀛楁灏忎簬 20000 鐨勬暟鎹苟璁$畻瀹冧滑鐨勫拰锛

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

寰楀埌缁撴灉濡備笅锛

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

涓嬮潰鎴戜滑寤虹珛涓寮犲拰 COMPANY 琛ㄧ浉浼肩殑 COMPANY1 琛紝浣跨敤 DELETE 璇彞鍜 WITH 瀛愬彞鍒犻櫎 COMPANY 琛ㄤ腑 SALARY(宸ヨ祫) 瀛楁澶т簬绛変簬 30000 鐨勬暟鎹紝骞跺皢鍒犻櫎鐨勬暟鎹彃鍏 COMPANY1 琛紝瀹炵幇灏 COMPANY 琛ㄦ暟鎹浆绉诲埌 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

寰楀埌缁撴灉濡備笅锛

INSERT 0 3

姝ゆ椂锛孋AMPANY 琛ㄥ拰 CAMPANY1 琛ㄧ殑鏁版嵁濡備笅锛

runoobdb=# 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)


runoobdb=# 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)