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>@StartDate
	union 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>@StartDate
	union 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