通用表表達式

来源:https://www.cnblogs.com/liurui12138/archive/2023/03/08/17196263.html
-Advertisement-
Play Games

通用表表達式 通用表表達式（Common Table Expression、CTE）是一個臨時的查詢結果或者臨時表，可以 在其他 SELECT、INSERT、UPDATE 以及 DELETE 語句中使用。通用表表達式只在當前語句中 有效，類似於子查詢。 使用 CTE 的主要好處包括： 提高複雜查詢的 ...


通用表表達式

通用表表達式（Common Table Expression、CTE）是一個臨時的查詢結果或者臨時表，可以 在其他 SELECT、INSERT、UPDATE 以及 DELETE 語句中使用。通用表表達式只在當前語句中 有效，類似於子查詢。

使用 CTE 的主要好處包括：

  • 提高複雜查詢的可讀性。CTE 可以將複雜查詢模塊化，組織成容易理解的結構。

  • 支持遞歸查詢。CTE 通過引用自身實現遞歸，可以方便地處理層次結構數據和圖數據。

--臨時表t 一個欄位為n 括弧里是表的內容
with t(n) as(
	select 1
)
select * from  t;

-- 也可以定義多個
with t(n) as(
	select 1
),
t2(m) as(
	select 1
)
select * from  t cross join t2;

相當於一個變數，可以重覆使用, 後面的臨時表可以應用前面臨時表的變數

with t(n) as(
	select 1
),
t2(m) as(
	select n+1 from  t
)
select * from  t cross join t2;

with子句

--查詢每個部門的平均薪資
select d.department_name ,ds.avg_sal
from departments d 
join (select department_id,avg(salary) avg_sal from employees  group by department_id) ds 
on d.department_id =ds.department_id



with department_avg(department_id,avg_sal) as (
		select department_id,avg(salary) avg_sal from employees  group by department_id
)
select d.department_name ,department_avg.avg_sal
from departments d
join department_avg
on d.department_id =department_avg.department_id;

遞歸

遞歸 CTE 允許在它的定義中進行自引用，理論上來說可以實現任何複雜的計算功能，最常 用的場景就是遍歷層次結構的數據和圖結構數據。

WITH RECURSIVE cte_name AS(
 cte_query_initial -- 初始化部分
 UNION [ALL]
 cte_query_iterative -- 遞歸部分
) SELECT * FROM cte_name;
  • RECURSIVE 表示遞歸；
  • cte_query_initial 是初始化查詢，用於創建初始結果集；
  • cte_query_iterative 是遞歸部分，可以引用 cte_name；
  • 如果遞歸查詢無法從上一次迭代中返回更多的數據，將會終止遞歸併返回結果。

一個經典的遞歸 CTE 案例就是生成數字序列：

with recursive t(n) as (
	select 1   -- 初始化
	union all     
	select n+1 from t  where n<10 -- 遞歸
)
select * from t

-- 組織遞歸舉例
select * from employees;

with recursive emp_path(employee_id,emp_name,path_name) as (
	select employee_id ,first_name||last_name ,first_name||last_name from employees where manager_id isnull 
	union all 
	select e.employee_id ,e.first_name||e.last_name ,path_name||'-->'||e.first_name||e.last_name from employees e join emp_path p on (e.manager_id=p.employee_id)
)
select * from emp_path

PL/pgSQL 存儲過程

除了標準 SQL 語句之外，PostgreSQL 還支持使用各種過程語言（例如 PL/pgSQL、C、PL/Tcl、 PL/Python、PL/Perl、PL/Java 等 ）創建複雜的過程和函數，稱為存儲過程（Stored Procedure） 和自定義函數（User-Defined Function）。存儲過程支持許多過程元素，例如控制結構、迴圈和 複雜的計算。

代碼塊結構

[ <<label>> ]
[ DECLARE
 declarations ]
BEGIN
 statements;
 ...
END [ label ];

其中，label 是一個可選的代碼塊標簽，可以用於 EXIT 語句退出指定的代碼塊，或者限定 變數的名稱；DECLARE 是一個可選的聲明部分，用於定義變數；BEGIN 和 END 之間是代碼主 體，也就是主要的功能代碼；所有的語句都使用分號（;）結束，END 之後的分號表示代碼塊結 束。

-- $$ 避免引號拼接 $$用於替換單引號（'），因為 PL/pgSQL 代碼主體必須是字元串文本，意味著代碼中所有的單引號都必須轉義（重覆寫兩次）。

DO $$ 
DECLARE
 name text;
BEGIN 
 name := 'PL/pgSQL';
 RAISE NOTICE 'Hello %!', name;
END $$;

代碼子塊

DO $$ 
<<outer_block>>
DECLARE
 name text;
BEGIN 
 name := 'outer_block';
 RAISE NOTICE 'This is %', name;

	 DECLARE 
	 name text := 'sub_block';
	 BEGIN 
	 RAISE NOTICE 'This is %', name;
	 RAISE NOTICE 'The name FROM the outer block is %', outer_block.name;
	 END;
	
  RAISE NOTICE 'This is %', name;
 
END outer_block $$;

聲明與賦值

1.變數

在使用變數之前，需要在代碼的聲明部分進行聲明：

variable_name data_type [ NOT NULL ] [ { DEFAULT | := | = } expression ];

user_id integer;
quantity numeric(5) DEFAULT 0;
url varchar := 'http://mysite.com';

do $$
declare 
	id integer;
	price numeric(5,2) default 0.6;
	name text;
begin
	id :=1;
	name :='Postgresql';
	raise notice 'id : %' ,id;
	raise notice 'price : %' ,price;
	raise notice 'name : %' ,name;
end;
$$

除了基本的 SQL 數據類型之外，PL/pgSQL 還支持基於表的欄位或行或者其他變數定義變數：

myrow tablename%ROWTYPE;
myfield tablename.columnname%TYPE;

常量

DO $$ 
DECLARE
 PI CONSTANT NUMERIC := 3.14159265;
 radius NUMERIC;
BEGIN 
 radius := 1.0;
 RAISE NOTICE 'The area is %', PI * radius * radius;
END $$;
NOTICE: The area is 3.1415926500

if語句

IF 語句可以基於條件選擇性執行操作， PL/pgSQL 提供了三種形式的 IF 語句。

  • IF … THEN … END IF
  • IF … THEN … ELSE … END IF
  • IF … THEN … ELSIF … THEN … ELSE … END IF
DO $$
BEGIN 
 IF 2 > 3 THEN
 RAISE NOTICE '2 大於 3';
 END IF;
 IF 2 < 3 THEN
 RAISE NOTICE '2 小於 3';
 END IF;
END $$;
NOTICE: 2 小於 3



DO $$
BEGIN 
 IF 2 > 3 THEN
 RAISE NOTICE '2 大於 3';
 ELSE
 RAISE NOTICE '2 小於 3';
 END IF;
END $$;
NOTICE: 2 小於 3


DO $$
DECLARE
 i integer := 3;
 j integer := 3;
BEGIN 
 IF i > j THEN
 RAISE NOTICE 'i 大於 j';
 ELSIF i < j THEN
 RAISE NOTICE 'i 小於 j';
 ELSE
 RAISE NOTICE 'i 等於 j';
 END IF;
END $$;
NOTICE: i 等於 j
DO

case語句

DO $$
DECLARE
 i integer := 3;
BEGIN 
 CASE i
 WHEN 1, 2 THEN
 RAISE NOTICE 'one or two';
 WHEN 3, 4 THEN
 RAISE NOTICE 'three or four';
 ELSE
 RAISE NOTICE 'other value';
 END CASE;
END $$;



DO $$
DECLARE
 i integer := 3;
BEGIN 
 CASE 
 WHEN i BETWEEN 0 AND 10 THEN
 RAISE NOTICE 'value is between zero and ten';
 WHEN i BETWEEN 11 AND 20 THEN
 RAISE NOTICE 'value is between eleven and twenty';
 ELSE
 RAISE NOTICE 'other value';
 END CASE;
END $$;

迴圈語句

PostgreSQL 提供 4 種迴圈執行命令的語句：LOOP、WHILE、FOR 和 FOREACH 迴圈，以 及迴圈控制的 EXIT 和 CONTINUE 語句。

loop

  • EXIT 語句用於退出迴圈。

  • CONTINUE 表示忽略後面的語句

DO $$
DECLARE
 i integer := 0;
BEGIN 
 LOOP
 EXIT WHEN i = 5;
 i := i + 1;
 RAISE NOTICE 'Loop: %', i;
 END LOOP;
END $$;

while

DO $$
DECLARE
 i integer := 0;
BEGIN 
 WHILE i < 5 LOOP
 i := i + 1;
 RAISE NOTICE 'Loop: %', i;
 END LOOP;
END $$;

FOR 迴圈

DO $$
BEGIN 
 FOR i IN 1..5 BY 2 LOOP
 RAISE NOTICE 'Loop: %', i;
 END LOOP;
END $$;

NOTICE: Loop: 1
NOTICE: Loop: 3
NOTICE: Loop: 5


FOR 迴圈預設從小到大進行遍歷
REVERSE 表示從大到小遍歷；BY 用於指定每次的增量，預設為 1。

遍歷查詢結果集的 FOR 迴圈如下

[ <<label>> ]
FOR target IN query LOOP
 statements
END LOOP [ label ];

DO $$
DECLARE
 emp record;
BEGIN 
 FOR emp IN (SELECT * FROM employees LIMIT 5) LOOP
 	RAISE NOTICE 'Loop: %,%', emp.first_name, emp.last_name;
 END LOOP;
END $$;

NOTICE: Loop: Steven,King
NOTICE: Loop: Neena,Kochhar
NOTICE: Loop: Lex,De Haan
NOTICE: Loop: Alexander,Hunold
NOTICE: Loop: Bruce,Ernst

FOREACH 迴圈 ( 數組 )

DO $$
DECLARE
 x int;
BEGIN
 FOREACH x IN ARRAY (ARRAY[[1,2,3],[4,5,6]])
 LOOP
 RAISE NOTICE 'x = %', x;
 END LOOP;
END $$;
NOTICE: x = 1
NOTICE: x = 2
NOTICE: x = 3
NOTICE: x = 4
NOTICE: x = 5
NOTICE: x = 6

DO $$
DECLARE
 x int[];
BEGIN
 FOREACH x SLICE 1 IN ARRAY (ARRAY[[1,2,3],[4,5,6]])
 LOOP
 RAISE NOTICE 'row = %', x;
 END LOOP;
END $$;
NOTICE: row = {1,2,3}
NOTICE: row = {4,5,6}

您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • Linux系統調優介紹
    前言： Linux伺服器運行了很多應用，在高負載下，伺服器可能會出現性能瓶頸，例如CPU利用率過高、記憶體不足、磁碟I/O瓶頸等，從而導致系統卡頓，服務無法正常運行等問題。所以針對以上問題，可以通過調整內核參數和系統的相關組件，優化應用程式來提高伺服器的性能和穩定性，避免系統崩潰和服務中斷。 Linu ...
  • centos8-x86_64安裝kylinv10-arrch64
    1. vmware esxi安裝centos8，開啟內核虛擬化 2. 安裝qemu-system-arrch64 操作： wget https://download.qemu.org/qemu-2.11.0.tar.xz tar xvJf qemu-2.11.0.tar.xz cd qemu-2.1 ...
  • ssh 遠程連接方式總結
    無論你是後端程式員還是前端程式員，都避免不了和Linux打交道。上篇介紹了Linux常用的20個命令其中的10個，本文繼續介紹剩下的10個命令。 11.man 命令 manual的縮寫，即使用手冊的意思，是Linux下最核心的命令之一，用來列出一份完整的說明 語法格式 : man [參數] 常用參數 ...
  • CentOS7.9 搭建內部yum源伺服器同步阿裡yum源
    CentOS7.9 搭建內部yum源伺服器同步阿裡yum源 環境說明： 1、企業內網需求：企業內部許多伺服器是不能連接互聯網，但每台centos主機安裝軟體包時都配置本地yum源很麻煩，也缺少第三方yum源，因此在內網搭建一臺yum源伺服器滿足這一需求，同時網路層面策略只允許此yum源伺服器每周日晚 ...
  • 動態切換JDK8和JDK17
    一、下載安裝兩個版本的JDK 官網下載地址：Java Downloads | Oracle 下載安裝成功後，安裝路徑E:\Java\上 JDK8 有兩個包一個jdk1.8.0_231，一個jre1.8.0_231。JDK17只有一個jdk-17.0.5 二、JDK的環境配置 切換環境變數 在系統變數 ...
  • Liunx Vim常用命令
    Liunx Vim常用命令 1、打開命令： vi / vim + filename(文件名) 2、退出命令： 強制退出不保存修改的內容 :q! 退出並且保存修改的內容 :wq 強制保存修改的內容然後退出（修改了只讀文件會用到） :wq! 註意：輸入法需要改為英文，中文不生效！ 3、游標移動命令 個人 ...
  • 一次慘痛教訓讓我寫了個Windows定期備份文件腳本
    前言 說實話在寫這篇文章的時候，鹹魚不禁又想起了那件男默女淚的往事 我喜歡做筆記，我覺得好記性不如爛筆頭，所以在我的學生以及職業生涯階段，我用過四款筆記應用——Onenote、語雀、印象筆記、Typora 其中我個人覺得體驗最好的非 Typora 莫屬了 ​ 在 2022 年的時候，由於 Typor ...
  • 不愧是微軟出品的工具，逆天！
    系統增強工具PowerToys 下載地址：https://github.com/microsoft/PowerToys 什麼是 Windows 系統中，最好的輔助工具？ PowerToys 一定可以獲得提名。PowerToys 是一款來自微軟的系統增強工具，就像是一個神奇的系統外掛，整套軟體由若幹子 ...
一周排行
    -Advertisement-
    Play Games
  • Dapr Outbox 執行流程
    Dapr Outbox 是1.12中的功能。 本文只介紹Dapr Outbox 執行流程，Dapr Outbox基本用法請閱讀官方文檔 。本文中appID=order-processor，topic=orders 本文前提知識：熟悉Dapr狀態管理、Dapr發佈訂閱和Outbox 模式。 Outbo ...
  • Advanced .Net Debugging 7：托管堆與垃圾收集
    引言 在前幾章我們深度講解了單元測試和集成測試的基礎知識，這一章我們來講解一下代碼覆蓋率,代碼覆蓋率是單元測試運行的度量值,覆蓋率通常以百分比表示，用於衡量代碼被測試覆蓋的程度，幫助開發人員評估測試用例的質量和代碼的健壯性。常見的覆蓋率包括語句覆蓋率（Line Coverage）、分支覆蓋率（Bra ...
  • C#S7.NET實現西門子PLCDB塊數據採集的完整步驟
    前言 本文介紹瞭如何使用S7.NET庫實現對西門子PLC DB塊數據的讀寫，記錄了使用電腦模擬，模擬PLC，自至完成測試的詳細流程，並重點介紹了在這個過程中的易錯點，供參考。 用到的軟體： 1.Windows環境下鏈路層網路訪問的行業標準工具（WinPcap_4_1_3.exe）下載鏈接：http ...
  • WPF控制項：密碼框綁定MVVM
    從依賴倒置原則（Dependency Inversion Principle, DIP）到控制反轉（Inversion of Control, IoC）再到依賴註入（Dependency Injection, DI）的演進過程，我們可以理解為一種逐步抽象和解耦的設計思想。這種思想在C#等面向對象的編 ...
  • Python中的私有屬性與私有方法
    關於Python中的私有屬性和私有方法 Python對於類的成員沒有嚴格的訪問控制限制，這與其他面相對對象語言有區別。關於私有屬性和私有方法，有如下要點： 1、通常我們約定，兩個下劃線開頭的屬性是私有的（private）。其他為公共的（public）； 2、類內部可以訪問私有屬性（方法）； 3、類外 ...
  • C++ 訪問說明符詳解：封裝數據，控制訪問，提升安全性
    C++ 訪問說明符 訪問說明符是 C++ 中控制類成員（屬性和方法）可訪問性的關鍵字。它們用於封裝類數據並保護其免受意外修改或濫用。 三種訪問說明符： public：允許從類外部的任何地方訪問成員。 private：僅允許在類內部訪問成員。 protected：允許在類內部及其派生類中訪問成員。 示 ...
  • static_cast與dynamic_cast到底是什麼？
    寫這個隨筆說一下C++的static_cast和dynamic_cast用在子類與父類的指針轉換時的一些事宜。首先，【static_cast，dynamic_cast】【父類指針，子類指針】，兩兩一組，共有4種組合：用 static_cast 父類轉子類、用 static_cast 子類轉父類、使用 ...
  • C語言數據結構：雙向鏈表的增刪操作
    /******************************************************************************************************** * * * 設計雙向鏈表的介面 * * * * Copyright (c) 2023-2 ...
  • @ComponentScan註解的實現，Spring掃描包的過程
    相信接觸過spring做開發的小伙伴們一定使用過@ComponentScan註解 @ComponentScan("com.wangm.lifecycle") public class AppConfig { } @ComponentScan指定basePackage，將包下的類按照一定規則註冊成Be ...
  • opensips開啟python支持
    操作系統 ：CentOS 7.6_x64 opensips版本： 2.4.9 python版本：2.7.5 python作為腳本語言，使用起來很方便，查了下opensips的文檔，支持使用python腳本寫邏輯代碼。今天整理下CentOS7環境下opensips2.4.9的python模塊筆記及使用 ...
所有分類