当前位置:   article > 正文

SQL 开发的十个高级概念_sql开发

sql开发

导读

SQL 是一种每位数据开发者必备的开发语言,不同的用户使用 SQL 语言的程度不同,最开始接触到的 SQL 就是 SELECT ,INSERT, UPDATE, DELETE 以及 WHERE 子句对数据进行筛选,如果需要关联,可能会使用 JOIN 关联查询多张表。随着数据量的增多以及需求复杂性的要求,对数据开发者的要求可以不仅仅以上简单的使用方式。今天我们一起来了解一些日常开发中常用的几种 SQL 高级概念,带你在 SQL Server 数据开发中起飞。

1,公共表达式 CTE

CTE ( Common Table Expression ), 公共表达式,在 SQL Server 2005 中引入的一个特性。

  1. 单个语句的执行范围内定义的临时结果集
  2. 只在查询期间有效
  3. 可以自引用,也可以在查询中多次引用
  4. 实现代码的重复利用,提升代码可读性
  5. 以优雅的方式实现递归等复杂的查询
WITH <common_table_expression> [ ,...n ] ]

<common_table_expression>::=
    expression_name [ ( column_name [ ,...n ] ) ]
    AS
    ( CTE_query_definition )
  • 1

对比下面的两种查询语句,第一个语句中使用了子查询进行查询,近乎难以理解。

第一种:一般写法
select Orders.orderid, Orders.orderdate, Orders.requireddate,
Orders.shippeddate,Orders.shipcity,Orders.shipaddress
from Sales.Orders
left join Sales.OrderDetails
on OrderDetails.orderid = Orders.orderid
where custid in(
select custid
from sales.Customers
where country in ('USA','Italy')
)
and OrderDetails.qty * OrderDetails.unitprice > 100
and datediff(day,requireddate,shippeddate) > 1;
  • 1
第二种:公共表达式写法
with cust as (
select custid
from sales.Customers
where country in ('USA','Italy')
),
qty as(
select orderid
from Sales.OrderDetails
where OrderDetails.qty * OrderDetails.unitprice > 100
)
select Orders.orderid, Orders.orderdate, Orders.requireddate,
Orders.shippeddate,Orders.shipcity,Orders.shipaddress
from Sales.Orders, qty, cust
where Orders.orderid = qty.orderid 
and Orders.custid = cust.custid
and datediff(day,requireddate,shippeddate) > 1;
  • 1

对比以上两种写法,第一种写法主要使用子查询,第二种写法是使用 CTE 公共表达式的写法,代码可读性更高;其中 CTE 将代码分解为较小的快,更利于后期的运维工作;而且 CTE 允许为每个 CTE 分配不同的名称。代码可读性也是项目交付的指标之一,除了代码可读性之外,CTE 可以用于实现递归查询。

2,递归查询

递归 CTE 是引用自己的 CTE, 就像编程中的递归函数一样。递归 CTE 经常用于查询组织结构图,文件系统,网页之间的链接图等的分层数据。

CTE 递归查询构建需要三个部分:初始条件(也称为锚构件),递归调用表达式(引用 CTE 的递归查询),终止条件(停止递归构建的终止条件)。CTE 递归查询的伪代码如下:

WITH cte_name ( column_name [,...n] )
AS
(
--Anchor member is defined 初始条件
CTE_query_definition 
UNION ALL
--Recursive member is defined referencing cte_name 
--递归调用表达式
CTE_query_definition 
)
-- Statement using the CTE
-- 递归查询没有显式的递归终止条件,只有当递归子查询返回空结果集(没有数据行返回)或是超出了递归次数的最大限制时,才停止递归。
SELECT *
FROM cte_name
  • 1

如下案例是使用递归查询行政区划的例子,详细代码可通过关注发送 “高级SQL” 获取样例代码。

with cte(Id,ParentID,Name,Levelas
(
select ID,ParentID,Name,0 as Level
from dbo.hierarchy 
where id=1

union all
select h.ID,h.ParentID,h.Name,c.Level+
  • 1
声明:本文内容由网友自发贡献,不代表【wpsshop博客】立场,版权归原作者所有,本站不承担相应法律责任。如您发现有侵权的内容,请联系我们。转载请注明出处:https://www.wpsshop.cn/w/神奇cpp/article/detail/931465
推荐阅读
相关标签
  

闽ICP备14008679号