SQL 通用表表达式（CTE ）

目录

概念：CTE： Common table Expression 

CTE 语法

CTE Demo

---

概念：CTE： Common table Expression 

通用表表达式（CTE）是SQL中用于简化复杂查询的工具，第一次上线于SQL Server 2005。

CTE提供了一个临时的结果集，可用于SELECT、INSERT、UPDATE、DELETE和MERGE操作。本文详细介绍了CTE的语法、非递归和递归使用方式，并通过实例展示了如何生成行号。非递归CTE用于一次性查询，而递归CTE则能自我引用，直至满足终止条件。CTE在需要临时结果集的场合非常有用。

相比于TempDB而言的优点是减少I/O操作， 不用主动释放。

CTE 语法

CTE语法

-- Demo 1: expression_name 
WITH expression_name [ ( column_name [,...n] ) ] 
AS 
( CTE_query_definition )select column_name [,...n] from expression_name -- Demo 2: expression_name , expression_name2 
WITH expression_name [ ( column_name [,...n] ) ] 
AS 
( CTE_query_definition )
，expression_name2 [ ( column_name [,...n] ) ] 
AS 
( CTE_query_definition )select column_name [,...n] from expression_name 
union all
select column_name2 [,...n] from expression_name -- Demo 3: expression_name , expression_name2 . 此文章中统计结果基于这个脚本
WITH expression_name [ ( column_name [,...n] ) ] 
AS 
( CTE_query_definition )select column_name [,...n] from expression_name ; with expression_name2 [ ( column_name [,...n] ) ] 
AS 
( CTE_query_definition )select column_name2 [,...n] from expression_name 

CTE Demo

DemoDB是主数据库，DemoDB.Archive 是Archive数据库

AbpAuditLogs table scripts

CREATE TABLE [dbo].[AbpAuditLogs]([Id] [bigint] IDENTITY(1,1) NOT NULL,[TenantId] [int] NULL,[UserId] [bigint] NULL,[ServiceName] [nvarchar](256) NULL,[MethodName] [nvarchar](256) NULL,[Parameters] [nvarchar](1024) NULL,[ExecutionTime] [datetime] NOT NULL,[ExecutionDuration] [int] NOT NULL,[ClientIpAddress] [nvarchar](64) NULL,[ClientName] [nvarchar](128) NULL,[BrowserInfo] [nvarchar](256) NULL,[Exception] [nvarchar](2000) NULL,[ImpersonatorUserId] [bigint] NULL,[ImpersonatorTenantId] [int] NULL,[CustomData] [nvarchar](2000) NULL,CONSTRAINT [PK_dbo.AbpAuditLogs] PRIMARY KEY CLUSTERED 
([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

 用于统计每分钟和每秒钟的并发用户数量。

declare @StartDate datetime=DateAdd(day,-30,getdate());
with tmpT as (select UserId,ExecutionTime=FORMAT(ExecutionTime, 'yyyy-MM-dd HH:mm') from AbpAuditLogs where UserId>0 and ExecutionTime>@StartDateunion all select UserId,ExecutionTime=FORMAT(ExecutionTime, 'yyyy-MM-dd HH:mm') from [DemoDB.Archive].[dbo].[AbpAuditLogs]  where UserId>0 and ExecutionTime>@StartDate
)select top 10 ExecutionTime,[AVG-User-In-Minutes]=count(distinct UserId) from tmpT group by ExecutionTime order by [AVG-User-In-Minutes] desc;with tmpTS as (select UserId,ExecutionTime=FORMAT(ExecutionTime, 'yyyy-MM-dd HH:mm:ss') from AbpAuditLogs where UserId>0 and ExecutionTime>@StartDateunion all select UserId,ExecutionTime=FORMAT(ExecutionTime, 'yyyy-MM-dd HH:mm:ss') from [DemoDB.Archive].[dbo].[AbpAuditLogs]  where UserId>0 and ExecutionTime>@StartDate
)select top 10 ExecutionTime,[AVG-User-In-Second]=count(distinct UserId) from tmpTS group by ExecutionTime order by [AVG-User-In-Second] desc