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 )

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

第一种：一般写法

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;

第二种：公共表达式写法

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;

对比以上两种写法，第一种写法主要使用子查询，第二种写法是使用 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

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

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

union all
select h.ID,h.ParentID,h.Name,c.Level+