欢迎来到尧图网

客户服务 关于我们

您的位置:首页 > 科技 > IT业 > SQL 通用表表达式(CTE )

SQL 通用表表达式(CTE )

2025/4/26 2:54:55 来源:https://blog.csdn.net/wzcool273509239/article/details/146496208  浏览:    关键词: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

版权声明:

本网仅为发布的内容提供存储空间,不对发表、转载的内容提供任何形式的保证。凡本网注明“来源:XXX网络”的作品,均转载自其它媒体,著作权归作者所有,商业转载请联系作者获得授权,非商业转载请注明出处。

我们尊重并感谢每一位作者,均已注明文章来源和作者。如因作品内容、版权或其它问题,请及时与我们联系,联系邮箱:809451989@qq.com,投稿邮箱:809451989@qq.com

热搜词