当前位置： 首页 > news >正文

SQL Server-表表达式基础

news 来源：原创 2024/5/7 0:29:02

表表达式

表表达式没有任何的物理实例化，在查询表表达式时它们是虚拟的，内部查询是非嵌套的，换句话说，外部查询和内部查询直接合并到一个底层对象的查询中，使用表表达式的好处通常与代码的逻辑方面有关，而与代码的性能无关-摘抄自SQL Server 2012基础教程。在使用表表达式时我们必须满足以下3点要求，否则将会报错。我们下面来简短介绍下表表达式的4中类型。

（1）无法保证顺序。

（2）所有列都必须具有名称。

（3）所有列名都必须是唯一的。

派生表

派生表（也称为子查询表）是在外部查询的FROM子句中定义的，它们存在的范围是外部查询。一旦外部查询完成后，派生表就消失了。我们看一个简单的派生表的例子。

复制代码

USE TSQL2012
GO

SELECT *FROM(
    SELECT * FROM Sales.Customers WHERE country = N'USA') AS USACusts;

复制代码

我们再来具体看下上述已经明确说过表表达式查询满足的条件，接下来我们进行如下查询：

复制代码

USE TSQL2012
GO

SELECT *FROM(
    SELECT * FROM Sales.Customers WHERE country = N'USA' ORDER BY custid) AS USACusts;

复制代码

当我们在子查询中添加ORDER BY之后就出现如上错误，这也就是说的上述表表达式要求的第一点，表表达式作为关系表，因为关系在源于集合理论，所以无法保证输出数据的顺序，看到SQL Server 2012基础教程中是这么说，我也就这么理解，至于真正原因还是无法理解，反正在表表达式中千万不要进行ORDER BY。关于要求的第二点和第三点就不用多说，比如上述此时对表不起别名肯定会报错，还有当对多个表进行联接时，表中列字段肯定有一样的，为保证唯一，我们必须为列名起别名来解决不唯一的问题。使用表表达式的好处之一就是在外部查询的任何子句中，可以引用内部查询的SELECT子句中分配的列别名，如此这样可以帮助我们绕开在SELECT子句逻辑处理之前的查询子句中（如WHERE、GROUP BY）无法引用SELECT子句中分配的列别名的实际问题，到底是什么意思呢，我们知道进行常规的查询时，此时如WHERE、GROUP BY是在SELECT之前进行，所以会导致我们对SELECT中的列通过WHERE、GROUP BY无法进行引用，我们来看一下以下例子。

复制代码

USE TSQL2012
GO

SELECT YEAR(orderdate) AS orderyear, COUNT(DISTINCT custid) AS custids
FROM Sales.Orders
GROUP BY orderyear

复制代码

如上此时我们对SELECT中的orderyear通过GROUP BY来进行分组，但是GROUP BY操作是在SELECT之前所以会导致出现如下错误。

要解决这个问题我们可以通过表表达式中的派生表来查询

复制代码

USE TSQL2012
GO

SELECT orderyear, COUNT(DISTINCT custid) AS custids
FROM (SELECT  YEAR(orderdate) AS orderyear, custid FROM Sales.Orders) AS  SO
GROUP BY orderyear

复制代码

对于派生表可以引用参数来用于存储过程或函数等变量或输入参数，同时派生表可以进行嵌套，如下：

复制代码

USE TSQL2012
GO


SELECT orderyear, numcusts
FROM (
    SELECT orderyear, COUNT(DISTINCT custid) AS numcusts 
    FROM (
        SELECT YEAR(orderdate) AS orderyear, custid 
        FROM Sales.Orders) AS D1 
        GROUP BY orderyear)AS D2
WHERE numcusts > 70;

复制代码

当有多个表时这样进行嵌套时此时代码会越来越复杂，冗长的代码不利于维护容易导致出错，同时也降低了代码的可读性。此时我们可以用表表达式的第2种形式CTE。

公用表表达式（CTE）

CTE通过WITH语句定义，具有如下常用形式。

复制代码

WITH <CTE_NAME>[(<target_column_list>)]
AS
(       <inner_query_defining_CTE>       )<outer_query_against_CTE>

复制代码

我们来看一个关于CTE简单的例子

复制代码

USE TSQL2012
GO

WITH USACusts AS
(
    SELECT custid, companyname
    FROM Sales.Customers
    WHERE country = N'USA')
SELECT * FROM USACusts

复制代码

和派生表相同，一旦外部查询完成后，CTE马上就会消失。在CTE中我们同样可以使用参数，如下：

复制代码

USE TSQL2012
GO

DECLARE @empid AS INT = 3;

WITH C AS
(
    SELECT YEAR(orderdate) AS orderyear, custid
    FROM Sales.Orders
    WHERE empid = @empid
)
SELECT orderyear, COUNT(DISTINCT custid) AS numcusts
FROM C
GROUP BY orderyear

复制代码

我们同样可以类似实现派生表一样的嵌套，如下：

复制代码

USE TSQL2012
GO


WITH C1 AS
(
    SELECT YEAR(orderdate) AS orderyear, custid
    FROM Sales.Orders
),C2 AS
(
    SELECT orderyear,COUNT(DISTINCT custid) AS numcusts
    FROM C1
    GROUP BY orderyear
)

SELECT orderyear, numcusts
FROM C2
WHERE numcusts > 70

复制代码

这里我们利用CTE实现了和派生表同样的结果，派生表和CTE其实只是在语义上有差异，但是相对于派生表最主要的优势在于不需要像派生表那样需要多重嵌套，而CTE只要定义了就无需嵌套，每个CTE在代码中以模块化的方式分别出现。这中模块化的方式和嵌套派生表方式相比，大大提高了代码的可读性和可维护性，若有多个表需要嵌套利用CTE来实现更加清爽并有助于代码的清晰性。而对于派生表的另外一个优势在于就外部查询的FROM子句而言，CTE在之前就已经存在，因此可以引用同一个CTE的多个实例。

视图（VIEW）

视图和内嵌表值函数是两种可以重复使用的表表达式类型，其定义被存储为数据库对象，创建之后，这些对象是数据库的永久部分，并且只有在显式删除它们时才能从数据库中删除。我们看下如何创建视图并使用视图。

复制代码

USE TSQL2012
GO

IF OBJECT_ID('Sales.USACusts') IS NOT NULL
    DROP VIEW Sales.USACusts;
GO

CREATE VIEW Sales.USACusts
AS

SELECT custid, companyname, contactname, contacttitle, [address]
FROM Sales.Customers
WHERE country = N'USA'GO

复制代码

创建视图完之后视图对象就在数据库中已经存在，此时我们再来查询视图

USE TSQL2012
GO

SELECT * FROM Sales.USACusts

内嵌表值函数（TVF）

内嵌表值函数是支持输入参数的可重复使用的表表达式。除了支持输入参数之外的其他所有方面都和视图类似。我们来看下怎么创建内嵌表值函数。

复制代码

USE TSQL2012
GO


IF OBJECT_ID('dbo.GetCustOrders') IS NOT NULL
    DROP FUNCTION dbo.GetCustOrders;
GO

CREATE FUNCTION dbo.GetCustOrders(@cid AS INT) RETURNS TABLE
AS RETURN
    SELECT orderid, custid, empid, orderdate, requireddate, shippeddate, shipperid, shipcity,
            shipaddress, shipregion, freight
    FROM Sales.Orders
    WHERE custid = @cid
GO

复制代码

此时我们创建完毕TVF，我们接下来来调用自定义的TVF

复制代码

USE TSQL2012
GO


SELECT orderid, custid
FROM dbo.GetCustOrders(1) AS O;

复制代码

上述我们为表表达式提供了一个别名，虽然不是必须的，但是推荐这样做，因为它使代码更具有可读性和少出错误。本节我们对表表达式的4种方式作了一下回顾，同样我们来为这4种形式的表表达式来做个结论。

（1）表表达式可以简化代码，提高代码的可维护性和封装查询逻辑。

（2）当需要使用表表达式并且不打算重复使用其定义时，可以使用派生表或CTE，而CTE对派生表具有更多优势不需要像派生表那样嵌套CTE，使用CTE使代码更加模块化和便于维护，此外，还可以引用同一个CTE的多个实例，这一点是派生表无法实现的。

（3）当需要使用表表达式并且需要定义可重复使用的表表达式时，可以使用视图或内嵌表值函数，当不需要支持输入参数时，可以使用视图，否则，应当使用内嵌表值函数（TVF）。

本文转自帅气的头头博客51CTO博客，原文链接http://blog.51cto.com/12902932/1926498如需转载请自行联系原作者


sshpp

相关文章：

  • Oracle 11g RAC 故障之--Instance 启动失败
  • Nginx基于用户名和密码的访问控制
  • VS2005和ASP.NET2.0中使用强类型数据
  • HTML 简介
  • 开机取消按Ctrl+Alt+Del键
  • Android录制视频报错setVideoSize called in a invalid state 1
  • android自动化测试中hierarchyviewer和uiautomatorviewer获取控件信息的方式比对（1）
  • android89 服务service
  • 阿里云服务器使用之一：搭建jsp服务器
  • 安装 virtualenv
  • 实例详解ISA防火墙策略元素：ISA2006系列之五
  • 调查称没互联网35.8％的人将手足无措,您呢?
  • 实现.NET Core配置Provider之EF
  • hbase shell 启动报错
  • Ubuntu 16下安装zabbix agent
  • 实现windows 窗体的自己画，网上摘抄的，学习了
  • __proto__ 和 prototype的关系
  • 8年软件测试工程师感悟——写给还在迷茫中的朋友
  • C++入门教程（10）：for 语句
  • CentOS7 安装JDK
  • crontab执行失败的多种原因
  • ES学习笔记(10)--ES6中的函数和数组补漏
  • javascript 总结（常用工具类的封装）
  • Java超时控制的实现
  • Java精华积累：初学者都应该搞懂的问题
  • PHP那些事儿
  • scrapy学习之路4(itemloder的使用)
  • 函数式编程与面向对象编程[4]:Scala的类型关联Type Alias
  • 基于webpack 的 vue 多页架构
  • 讲清楚之javascript作用域
  • 买一台 iPhone X，还是创建一家未来的独角兽？
  • 手机app有了短信验证码还有没必要有图片验证码？
  • 腾讯大梁：DevOps最后一棒，有效构建海量运营的持续反馈能力
  • C# - 为值类型重定义相等性
  • ​RecSys 2022 | 面向人岗匹配的双向选择偏好建模
  • ​卜东波研究员：高观点下的少儿计算思维
  • ​草莓熊python turtle绘图代码（玫瑰花版）附源代码
  • ​软考-高级-信息系统项目管理师教程 第四版【第23章-组织通用管理-思维导图】​
  • (+3)1.3敏捷宣言与敏捷过程的特点
  • （1）(1.19) TeraRanger One/EVO测距仪
  • (分享)自己整理的一些简单awk实用语句
  • (附源码)springboot人体健康检测微信小程序 毕业设计 012142
  • (数位dp) 算法竞赛入门到进阶 书本题集
  • （一）Mocha源码阅读: 项目结构及命令行启动
  • (轉貼)《OOD启思录》：61条面向对象设计的经验原则 (OO)
  • ****Linux下Mysql的安装和配置
  • **PHP二维数组遍历时同时赋值
  • .NET Core MongoDB数据仓储和工作单元模式封装
  • .net core 连接数据库，通过数据库生成Modell
  • .net mvc 获取url中controller和action
  • .Net6 Api Swagger配置
  • .net的socket示例
  • .NET面试题解析(11)-SQL语言基础及数据库基本原理
  • .net与java建立WebService再互相调用
  • .net专家（高海东的专栏）