通用表表達式

来源: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
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類