SQL Server CTE的一些實用例子

来源:https://www.cnblogs.com/atomy/archive/2022/05/13/16265307.html
-Advertisement-
Play Games

一、引言 CTE(Common Table Expression) 公用表達式,它是在單個語句的執行範圍內定義的臨時結果集,只在查詢期間有效。它可以自引用,也可在同一查詢中多次引用,實現了代碼段的重覆利用。 CTE最大的好處是提升T-Sql代碼的可讀性,可以以更加優雅簡潔的方式實現遞歸等複雜的查詢。 ...


一、引言

CTE(Common Table Expression) 公用表達式,它是在單個語句的執行範圍內定義的臨時結果集,只在查詢期間有效。它可以自引用,也可在同一查詢中多次引用,實現了代碼段的重覆利用。

CTE最大的好處是提升T-Sql代碼的可讀性,可以以更加優雅簡潔的方式實現遞歸等複雜的查詢。

二、測試數據

CREATE TABLE [dbo].[Product](
    [PRD_ID] [INT] NOT NULL,
    [PRD_NAME] [VARCHAR](100) NULL,
    [UP] [NUMERIC](8, 2) NULL,
 CONSTRAINT [PK_Product] PRIMARY KEY NONCLUSTERED 
(
    [PRD_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT INTO Product (PRD_ID,PRD_NAME,UP) VALUES (1,'滑鼠',108)
INSERT INTO Product (PRD_ID,PRD_NAME,UP) VALUES (2,'鍵盤',108)
INSERT INTO Product (PRD_ID,PRD_NAME,UP) VALUES (3,'記憶體條',150)
INSERT INTO Product (PRD_ID,PRD_NAME,UP) VALUES (4,'硬碟',300)
INSERT INTO Product (PRD_ID,PRD_NAME,UP) VALUES (5,'主機',3000)
INSERT INTO Product (PRD_ID,PRD_NAME,UP) VALUES (6,'顯示器',750)
INSERT INTO Product (PRD_ID,PRD_NAME,UP) VALUES (7,'U盤',35)
GO

三、實用例子

3.1、基本用法

WITH CTE1(ID,[NAME])
AS
(
    SELECT PRD_ID,PRD_NAME FROM PRODUCT
)
SELECT * FROM CTE1

3.2、多次引用

WITH CET1(ID,[NAME])
AS
(
    SELECT PRD_ID,PRD_NAME FROM PRODUCT WHERE PRD_ID<5
)
,CET2(ID,[NAME])
AS
(
    SELECT PRD_ID,PRD_NAME FROM PRODUCT WHERE PRD_ID<5
)
SELECT * FROM CET1
UNION ALL
SELECT * FROM CET2

3.3、分頁

WITH CET1(ID,[NAME],[ROWID])
AS
(
    SELECT PRD_ID AS ID,PRD_NAME AS NAME,ROW_NUMBER() OVER (ORDER BY PRD_ID) AS ROWID
    FROM PRODUCT
)
SELECT * FROM CET1 WHERE ROWID BETWEEN 1 AND 5

3.4、遞歸

DECLARE @T TABLE (ID INT, ParentID INT)

INSERT INTO @T VALUES (1,NULL)
INSERT INTO @T VALUES (11,1)
INSERT INTO @T VALUES (12,1)
INSERT INTO @T VALUES (13,1)
INSERT INTO @T VALUES (1101,11)
INSERT INTO @T VALUES (1102,11)
INSERT INTO @T VALUES (1201,12)
INSERT INTO @T VALUES (1301,13)
INSERT INTO @T VALUES (1302,13)

;WITH CTE1 AS
(
    SELECT T.ID,T.PARENTID,1 AS [LEVEL]
    FROM @T AS T
    WHERE T.PARENTID IS NULL
    UNION ALL
    SELECT T.ID,T.PARENTID,CTE1.[LEVEL]+1 AS [LEVEL]
    FROM @T AS T INNER JOIN CTE1 ON CTE1.ID=T.PARENTID
)
SELECT * FROM CTE1 ORDER BY [LEVEL]

3.5、遞歸查詢

查詢某個節點下的所有節點。

DECLARE @T TABLE (ID INT, ParentID INT)

INSERT INTO @T VALUES (1,NULL)
INSERT INTO @T VALUES (11,1)
INSERT INTO @T VALUES (12,1)
INSERT INTO @T VALUES (13,1)
INSERT INTO @T VALUES (1101,11)
INSERT INTO @T VALUES (1102,11)
INSERT INTO @T VALUES (1201,12)
INSERT INTO @T VALUES (1301,13)
INSERT INTO @T VALUES (1302,13)

;WITH CTE1 AS
(
    SELECT T.ID,T.PARENTID,1 AS [LEVEL]
    FROM @T AS T
    WHERE T.PARENTID=11
    UNION ALL
    SELECT T.ID,T.PARENTID,CTE1.[LEVEL]+1 AS [LEVEL]
    FROM @T AS T INNER JOIN CTE1 ON CTE1.ID=T.PARENTID
)
SELECT * FROM CTE1 ORDER BY [LEVEL]

3.6、生成連續數字

WITH GCN
AS
(
    SELECT 0 AS ID
    UNION ALL
    SELECT ID+1 FROM GCN WHERE ID<2047
)
--MAXRECURSION:控制遞歸的最⼤次數
SELECT ID FROM GCN OPTION (MAXRECURSION 2047)

3.7、生成連續日期

WITH GCD
AS
(
    SELECT CAST('2022-05-01' AS DATE) AS [DATE]
    UNION ALL
    SELECT DATEADD(D,1,[DATE]) FROM GCD WHERE [DATE]<'2022-05-31'
)
SELECT [DATE] FROM GCD

3.8、生成連續間隔時間點

WITH GCT
AS
(
    SELECT 1 AS ID,CAST('00:00:00' AS TIME(0)) AS TC
    UNION ALL
    SELECT ID+1 AS ID,CAST(DATEADD(MI,30,TC) AS TIME(0)) AS TC FROM GCT WHERE ID<49
)
SELECT * FROM GCT

 


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

-Advertisement-
Play Games
更多相關文章
  • 一、輸入重定向和輸出重定向 輸入重定向就是把文件作為命令的參數,輸出重定向就是把原本要輸出到屏幕上的內容寫到文件裡面。 (1)輸入重定向: (2)輸出重定向; 對於重定向中的標準輸出模式,可以省略文件描述符1不寫,而錯誤輸出模式的文件描述符2是必須要寫的。 示例: 重定向中的覆蓋寫入和追加寫入的不同 ...
  • 7、 1 滑鼠 // 7-1-滑鼠.cpp : 定義應用程式的入口點。 // #include "framework.h" #include "7-1-滑鼠.h" #define MAX_LOADSTRING 100 #define MAXPOINTS 1000 // 全局變數: HINSTANCE ...
  • 一、安裝zabbix-server 操作系統:CentOS 7.5 1、首先關閉防火牆與SElinux 關閉防火牆 systemctl stop firewalld&&systemctl disable firewalld 關閉SELinux sed -i 's/SELINUX=enforcing/ ...
  • 鏡像下載、功能變數名稱解析、時間同步請點擊 阿裡雲開源鏡像站 一、前言 就是我們需要集群部署hadoop,Flink時,寫ip地址太長了,然後大家想的就是能不能用比如:node1,node2,node3去代替IP地址,在一個區域網中,每台機器都有一個主機名,便於主機與主機之間的區分,因此為每台機器設置主機名 ...
  • 鏡像下載、功能變數名稱解析、時間同步請點擊 阿裡雲開源鏡像站 最近換了台新電腦,系統是Win11的,因為之前用Win10的時候,基本都是裝上就能用的,Win11裝好了一打開突然就重啟了,還是有一點驚嚇的。 百度搜索問題,找到了的解決辦法大致分為兩個方面: 1.當前使用的電腦是否支持虛擬化 1.1 CPU是 ...
  • 本文先給出“win10找不到無線網路報錯”的通用解決方案,併在方案中介紹本次出現的“Windows無法自動將IP協議堆棧綁定到網路適配器”問題。 ...
  • 前言 由於部分企業要求本地部署系統(使用企業伺服器進行部署系統且資料庫也部署在同台伺服器),本地部署系統的伺服器往往達不到我們的雲部署伺服器,速度性能更是有所欠缺,特別是在查詢統計報表的時候,雲上幾秒鐘的速度,本地企業需要幾分鐘以上,所以最近對企業資料庫進行了性能優化,簡單一點其實主要進行查詢緩存優 ...
  • 5月《中國資料庫行業分析報告》已正式發佈,報告通過墨天輪“中國資料庫流行度排行”、國內行業動態、典型產品的介紹,以及全球與國內資料庫行業市場份額等數據情況,對國產資料庫在雲、開源道路上的發展現狀、趨勢進行深入盤點分析,嘗試釐清行業發展的關鍵要素,助力資料庫國產化發展。 ...
一周排行
    -Advertisement-
    Play Games
  • 分組和樹形結構是不一樣的。 樹形結構是以遞歸形式存在。分組是以鍵值對存在的形式,類似於GroupBy這樣的形式。 舉個例子 ID NAME SEX Class 1 張三 男 1 2 李四 女 2 3 王二 男 1 當以Sex為分組依據時則是 Key Value 男 1 張三 男 1 3 王二 男 1 ...
  • NetCore中將SQLServer資料庫備份為Sql腳本 描述: 最近寫項目收到了一個需求, 就是將SQL Server資料庫備份為Sql腳本, 如果是My Sql之類的還好說, 但是在網上搜了一大堆, 全是教你怎麼操作SSMS的, 就很d疼! 解決方案: 通過各種查找資料, 還有一些老哥的幫助, ...
  • 我的Notion Clowd.Squirrel Squirrel.Windows 是一組工具和適用於.Net的庫,用於管理 Desktop Windows 應用程式的安裝和更新。 Squirrel.Windows 對 Windows 應用程式的實現語言沒有任何要求,甚至無需服務端即可完成增量更新。 ...
  • 轉載請註明來源 https://www.cnblogs.com/brucejiao/p/16188865.html 謝謝! 轉載請註明來源 https://www.cnblogs.com/brucejiao/p/16188865.html 謝謝! 轉載請註明來源 https://www.cnblog ...
  • 1. Netty源碼研究筆記(3)——Channel系列 依舊是通過先縱向再橫向的研究方法,在開篇中,我們發現不管是Sever還是Client,最終的啟動是通過調用channel的對應方法來完成的,而這個動作實際在channel綁定的eventLoop中執行。 接下來,我們繼續EchoSever、E ...
  • 大家好,今天給大家介紹一款輕量、快速、穩定可編排的組件式規則引擎框架LiteFlow。 一、LiteFlow的介紹 LiteFlow官方網站和代碼倉庫地址 官方網站:https://yomahub.com/liteflow Gitee托管倉庫:https://gitee.com/dromara/li ...
  • 我使用Spring AOP實現了用戶操作日誌功能 今天答辯完了,復盤了一下系統,發現還是有一些東西值得拿出來和大家分享一下。 需求分析 系統需要對用戶的操作進行記錄,方便未來溯源 首先想到的就是在每個方法中,去實現記錄的邏輯,但是這樣做肯定是不現實的,首先工作量大,其次違背了軟體工程設計原則(開閉原 ...
  • 《零基礎學Java》 繪製幾何圖形 Java可以分別使用 Graphics 和 Graphics2D 繪製圖形,Graphics類 使用不同的方法繪製不同的圖形(drawLine()方法可f以繪製線、drawRect()方法用於繪製矩形、drawOval()方法用於繪製橢圓形)。 Graphics類 ...
  • 本期教程人臉識別第三方平臺為虹軟科技,本文章講解的是人臉識別RGB活體追蹤技術,免費的功能很多可以自行搭配,希望在你看完本章課程有所收穫。 ...
  • 很多人都喜歡使用黑色的主題樣式,包括我自己,使用了差不多三年的黑色主題,但是個人覺得在進行視窗轉換的時候很廢眼睛。 比如IDEA是全黑的,然後需要看PDF或者WORD又變成白色的了,這樣來回切換導致眼睛很累,畢竟現在網頁以及大部分軟體的界面都是白色的。那麼還是老老實實的使用原來比較順眼的模式吧。 1 ...