我的编程空间,编程开发者的网络收藏夹
学习永远不晚

SQL Server怎么使用T-SQL公用表表达式

短信预约 -IT技能 免费直播动态提醒
省份

北京

  • 北京
  • 上海
  • 天津
  • 重庆
  • 河北
  • 山东
  • 辽宁
  • 黑龙江
  • 吉林
  • 甘肃
  • 青海
  • 河南
  • 江苏
  • 湖北
  • 湖南
  • 江西
  • 浙江
  • 广东
  • 云南
  • 福建
  • 海南
  • 山西
  • 四川
  • 陕西
  • 贵州
  • 安徽
  • 广西
  • 内蒙
  • 西藏
  • 新疆
  • 宁夏
  • 兵团
手机号立即预约

请填写图片验证码后获取短信验证码

看不清楚,换张图片

免费获取短信验证码

SQL Server怎么使用T-SQL公用表表达式

本文小编为大家详细介绍“SQL Server怎么使用T-SQL公用表表达式”,内容详细,步骤清晰,细节处理妥当,希望这篇“SQL Server怎么使用T-SQL公用表表达式”文章能帮助大家解决疑惑,下面跟着小编的思路慢慢深入,一起来学习新知识吧。

在编写T-SQL代码时,往往需要临时存储某些结果集。前面我们已经广泛使用和介绍了两种临时存储结果集的方法:临时表和表变量。除此之外,还可以使用公用表表达式的方法。

公用表表达式(Common Table Expression)是SQL Server2005版本的引入的一个特性。CTE可以看组是一个临时的结果集,可以再接下来来的一个SELECT,INSERT,UPDATE,DELETE,MERGE语句中多次引用。

一、3种方法比较

使用公用表达式CTE可以让语句更加清晰简练。与公用表达式作用类似的还有临时表和表变量。下面给出三种方法的对比。

  • 临时表#:需要在临时数据库TempDB中通过I/O操作来创建表结构,一旦用户退出SQL Server环境则自动被删除。

  • 表变量@:在内存中以表结构的形式存在,其定义与变量一致,其使用与表类似,不需要产生I/O。

  • 公用表表达式with as:定义在内存中保存的临时存储结果集对象,不产生I/O,不需要按照表变量这样定义,使用方法和表类似。可以自己引用,也可以再查询中被多次引用。

1、使用CTE好处

根据微软对CTE好处的描述,可以归结为四点:

  • 可以定义递归公用表表达式(CTE)

  • 当不需要将结果集作为视图被多个地方引用时,CTE可以使其更加简洁

  • GROUP BY 语句可以直接作用于子查询所得的标量列

  • 可以在一个语句中多次引用公用表表达式(CTE)

二、WITH AS的含义

WITH AS-做子查询部分(subquery factoring)。

它用于定义一个SQL片段,该片段会被是整个SQL语句所用到。如果WITH AS所以定的表名被调用两次以上,则优化器会自动将WITH AS所获取的数据放入临时表里,如果只是被调用一次,则不会。

可以通过materialize将WITH AS短语里的数据强制放入全局临时表里。

WITH AS可以被紧跟着的一条SQL语句所使用多次,但不能被紧跟着的多条SQL语句使用。

WITH B AS (    SELECT * FROM xxx WHERE Id > 5)SELECT * FROM B

三、CTE的定义

CTE的定义语法如下,主要包括3个部分。

  • Expression_name:CTE表达式的名称。

  • Column_name:列名列表。

  • CTE_query_definition:定义CTE结果集的Select查询语句

WITH expression_name [(column_name [,...n] )]AS(   cte_query_definition )

按照是否递归,可以将公用表(CTE)表达式分为递归公用表表达式和非递归公用表表达式.

1、非递归公用表表达式(CTE):

非递归公用表表达式(CTE)是查询结果仅仅一次性返回一个结果集用于外部查询调用。并不在其定义的语句中调用其自身的CTE。

非递归公用表表达式(CTE)的使用方式和视图以及子查询一致。

比如一个简单的非递归公用表表达式:

WITH CTE_TestAS(    SELECT * FROM Person_1)SELECT * FROM CTE_Test

公用表表达式的好处之一是可以在接下来一条语句中多次引用:

with CTE_Test  as (select * from Person_1)select * from CTE_Test as a --第一次引用     inner join CTE_Test as b --第二次引用         on a.Id=b.Idorder by a.Id desc;

虽然以上引用了多次,但是只是一条语句,所以可以正常执行。

如果多条语句引用,如下面这样,是会报错的。

with CTE_Test as (select * from Person_1)select * from CTE_Test;select * from CTE_Test;

输出结果如下:

SQL Server怎么使用T-SQL公用表表达式

由于CTE只能在接下来一条语句中使用,因此,当需要接下来的一条语句中引用多个CTE时,可以定义多个,中间用逗号分隔。下面是一次定义多个CTE的例子:

with CTE_Test1  as (select * from Person_1),      CTE_Test2  as (select * from Person_2)select * from CTE_Test1unionselect * from CTE_Test2;

结果如下:

SQL Server怎么使用T-SQL公用表表达式

2、递归公用表表达式(CTE):

对于递归公用表达式来说,只需要在语句中定义两部分:

  • 基本语句

  • 递归语句

先建一张表栏目表如下,栏目Id,栏目名称,栏目的父栏目。

SQL Server怎么使用T-SQL公用表表达式

现在使用CTE查询其每个栏目是第几层栏目的代码如下:

declare @table1 table(id int, Name varchar(10), ParentId int);insert into @table1(id, Name, ParentId)values(1, '国内新闻', 0),    (2, '广东新闻', 1),    (3, '广州新闻', 2),    (4, '天河新闻', 3),    (5, '山东新闻', 1),    (5, '青岛新闻', 5);select * from @table1;with COL_CTE(Id, Name, ParentId, tLevel) as (    --基本语句    select id, Name, ParentId, 0 as tLevel from @table1 where ParentId=0    union all    --递归语句    select c.id, c.Name, c.ParentId, ce.tLevel+1 as tLevel from @table1 as c    inner join COL_CTE as ce --递归调用    on c.ParentId=ce.Id)select * from COL_CTE;

输出结果如下:

SQL Server怎么使用T-SQL公用表表达式

0表示顶级栏目。1就是1级栏目。语法非常优雅。就一个SELECT * FRON COL_CTE。这正是CTE强大的地方,但是,这要有约束,否则如果无限制递归可以会消耗掉非常多的系统资源。下面来看看如何限制递归的最大次数。

如将上面的查询语法改为:

WITH COL_CTE(Id,Name,ParentId,tLevel )AS(    --基本语句    SELECT Id,Name,ParentId,0 AS tLevel FROM @table1 WHERE ParentId = 0    UNION ALL    --递归语句    SELECT c.Id,c.Name,c.ParentId,ce.tLevel+1 AS tLevel FROM @table1 as c     INNER JOIN COL_CTE AS ce     ON c.ParentId = ce.Id)SELECT * FROM COL_CTEOPTION(MAXRECURSION 2)  --指定最大递归次数为2

我们知道在上面的查询中,要查到天河区新闻最少要递归3次,但是现在只递归2次,运行是什么结果呢?

SQL Server怎么使用T-SQL公用表表达式

提示信息如下:

消息 530,级别 16,状态 1,第 1 行  语句被终止。完成执行语句前已用完最大递归 2。

CTE是一种十分优雅的存在。CTE所带来最大的好处是代码可读性的提升,这是良好代码的必须品质之一。使用递归CTE可以更加轻松愉快的用优雅简洁的方式实现复杂的查询。

读到这里,这篇“SQL Server怎么使用T-SQL公用表表达式”文章已经介绍完毕,想要掌握这篇文章的知识点还需要大家自己动手实践使用过才能领会,如果想了解更多相关内容的文章,欢迎关注编程网行业资讯频道。

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

SQL Server怎么使用T-SQL公用表表达式

下载Word文档到电脑,方便收藏和打印~

下载Word文档

猜你喜欢

SQL Server怎么使用T-SQL公用表表达式

本文小编为大家详细介绍“SQL Server怎么使用T-SQL公用表表达式”,内容详细,步骤清晰,细节处理妥当,希望这篇“SQL Server怎么使用T-SQL公用表表达式”文章能帮助大家解决疑惑,下面跟着小编的思路慢慢深入,一起来学习新知
2023-06-30

SQL 查询之公用表表达式(CTE)

在关系型数据库中,表示数据层级结构的表很常见,例如,上下级关系、父子组件关系等。为了在这些层级结构中进行从上到下或从下到上的遍历,可以使用递归 CTE。

sql三元表达式怎么使用

在SQL中,可以使用CASE语句来模拟三元表达式。例如,可以使用以下语法来实现三元表达式:SELECT column_name,CASEWHEN condition THEN value1ELSE value2END AS result
sql三元表达式怎么使用
2024-04-16

MyBatis动态SQL表达式怎么使用

本篇内容介绍了“MyBatis动态SQL表达式怎么使用”的有关知识,在实际案例的操作过程中,不少人都会遇到这样的困境,接下来就让小编带领大家学习一下如何处理这些情况吧!希望大家仔细阅读,能够学有所成!动态 sql 简单来讲就是我们能通过条件
2023-07-04

SQL Server怎么使用T-SQL语句批处理

本篇内容介绍了“SQL Server怎么使用T-SQL语句批处理”的有关知识,在实际案例的操作过程中,不少人都会遇到这样的困境,接下来就让小编带领大家学习一下如何处理这些情况吧!希望大家仔细阅读,能够学有所成!批处理简介批处理是作为一个逻辑
2023-06-30

sql server怎么用sql语句创建表

在 sql server 中使用 sql 语句创建表的方法:打开 sql server management studio 并连接到数据库服务器。选择要创建表的数据库。输入 create table 语句,指定表名、列名、数据类型和约束。单
sql server怎么用sql语句创建表
2024-08-19

SQL Server在T-SQL语句中怎么使用变量

这篇文章主要介绍了SQL Server在T-SQL语句中怎么使用变量的相关知识,内容详细易懂,操作简单快捷,具有一定借鉴价值,相信大家阅读完这篇SQL Server在T-SQL语句中怎么使用变量文章都会有所收获,下面我们一起来看看吧。变量的
2023-06-30

Mybatis怎么使用ognl表达式实现动态sql

这篇文章主要为大家展示了“Mybatis怎么使用ognl表达式实现动态sql”,内容简而易懂,条理清晰,希望能够帮助大家解决疑惑,下面让小编带领大家一起研究并学习一下“Mybatis怎么使用ognl表达式实现动态sql”这篇文章吧。新建Us
2023-06-15

sql中如何使用正则表达式

sql中使用正则表达式可通过regexp_like()函数,使用posix语法匹配字符串。常用字符包括锚点字符、字符类和量词。正则表达式可用于在select、where和其他语句中搜索和提取数据,但不同数据库系统对正则表达式支持略有不同。如
sql中如何使用正则表达式
2024-05-02

sql三元表达式有什么作用

SQL中的三元表达式类似于其他编程语言中的三元运算符,用于在查询语句中根据条件返回不同的值。它的语法结构为:CASEWHEN condition THEN value1ELSE value2END三元表达式的作用是根据特定条件来返回不同
sql三元表达式有什么作用
2024-04-16

编程热搜

  • Python 学习之路 - Python
    一、安装Python34Windows在Python官网(https://www.python.org/downloads/)下载安装包并安装。Python的默认安装路径是:C:\Python34配置环境变量:【右键计算机】--》【属性】-
    Python 学习之路 - Python
  • chatgpt的中文全称是什么
    chatgpt的中文全称是生成型预训练变换模型。ChatGPT是什么ChatGPT是美国人工智能研究实验室OpenAI开发的一种全新聊天机器人模型,它能够通过学习和理解人类的语言来进行对话,还能根据聊天的上下文进行互动,并协助人类完成一系列
    chatgpt的中文全称是什么
  • C/C++中extern函数使用详解
  • C/C++可变参数的使用
    可变参数的使用方法远远不止以下几种,不过在C,C++中使用可变参数时要小心,在使用printf()等函数时传入的参数个数一定不能比前面的格式化字符串中的’%’符号个数少,否则会产生访问越界,运气不好的话还会导致程序崩溃
    C/C++可变参数的使用
  • css样式文件该放在哪里
  • php中数组下标必须是连续的吗
  • Python 3 教程
    Python 3 教程 Python 的 3.0 版本,常被称为 Python 3000,或简称 Py3k。相对于 Python 的早期版本,这是一个较大的升级。为了不带入过多的累赘,Python 3.0 在设计的时候没有考虑向下兼容。 Python
    Python 3 教程
  • Python pip包管理
    一、前言    在Python中, 安装第三方模块是通过 setuptools 这个工具完成的。 Python有两个封装了 setuptools的包管理工具: easy_install  和  pip , 目前官方推荐使用 pip。    
    Python pip包管理
  • ubuntu如何重新编译内核
  • 改善Java代码之慎用java动态编译

目录