SQL点滴10—使用with语句来写一个稍微复杂sql语句，附加和子查询的性能对比

最新推荐文章于 2023-09-27 14:19:42 发布
最新推荐文章于 2023-09-27 14:19:42 发布
阅读量515 收藏
点赞数 1
分类专栏： sql 文章标签： sql server

 今天偶尔看到sql中也有with关键字，好歹也写了几年的sql语句，居然第一次接触，无知啊。看了一位博主的文章，自己添加了一些内容，做了简单的总结，这个语句还是第一次见到，学习了。我从简单到复杂地写，希望高手们不要见笑。下面的sql语句设计到三个表，表的内容我用txt文件复制进去，这里不妨使用上一个随笔介绍的建立端到端的package的方法将这些表导入到数据库中，具体的就不说了。

从这里下载文件employees.txt，customers.txt，orders.txt

参考文章：http://www.cnblogs.com/wwan/archive/2011/02/24/1964279.html

使用package导入数据：http://www.cnblogs.com/tylerdonet/archive/2011/04/17/2017471.html

简单的聚合

从orders表中选择各个年份共有共有多少客户订购了商品

  •  第一种写法，我们可以写成这样 
        
    
    
     
     1
     
     select
     
     YEAR
     
     (o.orderdate) orderyear,
     
     COUNT
     
     (
     
     distinct
     
     (custid)) numCusts

     
     2
     
     from
     
      Sales.Orders o

     
     3
     
     group
     
     by
     
     YEAR
     
     (o.orderdate)

     
     4
     
     go
    要注意的是如果把group by YEAR(o.orderdata)换成group by orderyear就会出错，这里涉及到sql语句的执行顺序问题，有时间再了解一下　　　　　　　　　　
  • 第二种写法， 
        
    
    
     
     1
     
     select
     
      orderyear,
     
     COUNT
     
     (
     
     distinct
     
     (custid))numCusts

     
     2
     
     from
     
      (
     
     select
     
     YEAR
     
     (orderdate) 
     
     as
     
      orderyear,custid 
     
     from
     
      sales.orders) 
     
     as
     
      D

     
     3
     
     group
     
     by
     
      orderyear

     
     4
     
     go
    在from语句中先得到orderyear，然后再select语句中就不会出现没有这个字段的错误了
  • 第三种写法， 
        
    
    
     
     1
     
     select
     
      orderyear,
     
     COUNT
     
     (
     
     distinct
     
     (custid)) numCusts

     
     2
     
     from
     
      (
     
     select
     
     YEAR
     
     (orderdate),custid 
     
     from
     
      sales.orders) 
     
     as
     
      D(orderyear,custid)

     
     3
     
     group
     
     by
     
      orderyear

     
     4
     
     go
    在as D后面加上选择出的字段，是不是更加的清楚明了呢！
  • 第四种写法，with出场了 
        
    
    
     
     1
     
     with
     
      c 
     
     as
     
     (

     
     2
     
     select
     
     YEAR
     
     (orderdate) orderyear, custid 
     
     from
     
      sales.orders)

     
     3
     
     select
     
      orderyear,
     
     COUNT
     
     (
     
     distinct
     
     (custid)) numCusts 
     
     from
     
      c 
     
     group
     
     by
     
      orderyear

     
     4
     
     go
    with可以使语句更加的经凑，下面是权威解释。　　
    　　　　
    指定临时命名的结果集，这些结果集称为公用表表达式 (CTE)。该表达式源自简单查询，并且在单条 SELECT、INSERT、UPDATE、MERGE 或 DELETE 语句的执行范围内定义。该子句也可用在 CREATE VIEW 语句中，作为该语句的 SELECT 定义语句的一部分。公用表表达式可以包括对自身的引用。这种表达式称为递归公用表达式。　　　　　　　　　　　　　　　

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　----MSDN

  • 第五种写法，也可以借鉴第三种写法，这样使语句更加清楚明了，便于维护 
        
    
    
     
     1 
     
     with
     
      c(orderyear,custid) 
     
     as
     
     (

     
     2 
     
      
     
     select
     
     YEAR
     
     (orderdate),custid 
     
     from
     
      sales.orders)

     
     3 
     
      
     
     select
     
      orderyear,
     
     COUNT
     
     (
     
     distinct
     
     (custid)) numCusts 
     
     from
     
      c 
     
     group
     
     by
     
      c.orderyear

     
     4 
     
      
     
     go
    上面5中写法都得到相同的结果，如下图1：图1

添加计算

  • 现在要求要求计算出订单表中每年比上一年增加的客户数目，这个稍微复杂
    复制代码 
        
    
    
     
     1 
     
     with
     
      yearcount 
     
     as
     
     (

     
     2 
     
      
     
     select
     
     YEAR
     
     (orderdate) orderyear,
     
     COUNT
     
     (
     
     distinct
     
     (custid)) numCusts 
     
     from
     
      sales.orders 
     
     group
     
     by
     
     YEAR
     
     (orderdate))

     
     3 
     
      
     
     select
     
      cur.orderyear curyear,cur.numCusts curNumCusts,prv.orderyear prvyear,prv.numCusts prvNumCusts,cur.numCusts
     
     -
     
     prv.numCusts growth

     
     4 
     
      
     
     from
     
      yearcount cur 
     
     left
     
     join
     
      yearcount prv 
     
     on
     
      cur.orderyear
     
     =
     
     prv.orderyear
     
     +
     
     1
     
     

     
     5 
     
     go
    复制代码
    这里两次使用到with结果集。查询得到的结果如下图2

    图2

复杂的计算

  • 查找客户id，这些客户和所有来自美国的雇员至少有一笔交易记录，查询语句如下
    复制代码 
        
    
    
     
     1 
     
     with
     
      TheseEmployees 
     
     as
     
     (

     
     2 
     
     select
     
      empid 
     
     from
     
      hr.employees 
     
     where
     
      country
     
     =
     
     '
     
     USA
     
     '
     
     ),

     
     3 
     
     CharacteristicFunctions 
     
     as
     
     (

     
     4 
     
     select
     
      custid,

     
     5 
     
     case
     
     when
     
      custid 
     
     in
     
      (
     
     select
     
      custid 
     
     from
     
      sales.orders 
     
     as
     
      o 
     
     where
     
      o.empid
     
     =
     
     e.empid) 
     
     then
     
     1
     
     else
     
     0
     
     end
     
     as
     
      charfun

     
     6 
     
     from
     
      sales.customers 
     
     as
     
      c 
     
     cross
     
     join
     
      TheseEmployees 
     
     as
     
      e)

     
     7 
     
     select
     
      custid,
     
     min
     
     (charfun) 
     
     from
     
      CharacteristicFunctions 
     
     group
     
     by
     
      custid 
     
     having
     
     min
     
     (charfun)
     
     =
     
     1
     
     

     
     8 
     
     go
    复制代码
    这里嵌套with语句，第with语句查找美国雇员的id，第二个语句使用这个结果和拥有客户的客户id和拥有关系标识做笛卡尔积运算。最后从这个笛卡尔积中通过标识找到最终的custid。
    结果如下图3

    图3

这里只有简单地介绍，没有深入，高手们不要见笑啊。

---------------------------------------------------------分界线----------------------------------------------------------

with语句和子查询的性能比较

在博友SingleCat的提醒下，对with语句做一些性能测试，这里使用的测试工具是SQL Server Profile。我选择了最后一个语句，因为这个语句比较复杂一点。开始的时候单独执行一次发现他们的差别不大，就差几个毫秒，后来想让他们多执行几次，连续执行10

次看看执行的结果。下面贴出测试用的语句。

复制代码 
  
  

   
    1 
   
   /*
   
   with查询
   
   */
   
   

   
    2 
   
   declare
   
   @withquery
   
   varchar
   
   (
   
   5000
   
   )

   
    3 
   
   declare
   
   @execcount
   
   int
   
   =
   
   0
   
   

   
    4 
   
   set
   
   @withquery
   
   =
   
   '
   
   with TheseEmployees as(

   
    5 
   
   select empid from hr.employees where country=N
   
   ''
   
   USA
   
   ''
   
   ),

   
    6 
   
   CharacteristicFunctions as(

   
    7 
   
   select custid,

   
    8 
   
          case when custid in (select custid from sales.orders as o where o.empid=e.empid) then 1 else 0 end as charfun

   
    9 
   
   from sales.customers as c cross join TheseEmployees as e)

   
   10 
   
   select custid from CharacteristicFunctions group by custid having min(charfun)=1 order by custid

   
   11 
   
   '
   
    

   
   12 
   
   while
   
   @execcount
   
   <
   
   10
   
   

   
   13 
   
   begin
   
   

   
   14 
   
   exec
   
    (
   
   @withquery
   
   );

   
   15 
   
   set
   
   @execcount
   
   =
   
   @execcount
   
   +
   
   1
   
   

   
   16 
   
   end
   
   

   
   17 
   
   

   
   18 
   
   /*
   
   子查询
   
   */
   
   

   
   19 
   
   declare
   
   @subquery
   
   varchar
   
   (
   
   5000
   
   )

   
   20 
   
   declare
   
   @execcount
   
   int
   
   =
   
   0
   
   

   
   21 
   
   set
   
   @subquery
   
   =
   
   '
   
   select custid from Sales.Orders where empid in

   
   22 
   
   (select empid from HR.Employees where country = N
   
   ''
   
   USA
   
   ''
   
   ) group by custid

   
   23 
   
   having count(distinct empid)=(select count(*) from HR.Employees where country = N
   
   ''
   
   USA
   
   ''
   
   );

   
   24 
   
   '
   
   

   
   25 
   
   while
   
   @execcount
   
   <
   
   10
   
   

   
   26 
   
   begin
   
   

   
   27 
   
   exec
   
    (
   
   @subquery
   
   );

   
   28 
   
   set
   
   @execcount
   
   =
   
   @execcount
   
   +
   
   1
   
   

   
   29 
   
   end
复制代码

从SQL Server Profile中截图如下

从图中可以看到子查询语句的执行时间要少于with语句，我觉得主要是with查询中有一个cross join做了笛卡尔积的关系，于是又实验了上面的那个简单一点的，下面是测试语句。

复制代码 
  
  

   
    1 
   
   /*
   
   with语句
   
   */
   
   

   
    2 
   
   declare
   
   @withquery
   
   varchar
   
   (
   
   5000
   
   )

   
    3 
   
   declare
   
   @execcount
   
   int
   
   =
   
   0
   
   

   
    4 
   
   set
   
   @withquery
   
   =
   
   '
   
   with c(orderyear,custid) as(

   
    5 
   
   select YEAR(orderdate),custid from sales.orders)

   
    6 
   
   select orderyear,COUNT(distinct(custid)) numCusts from c group by c.orderyear
   
   '
   
    

   
    7 
   
   while
   
   @execcount
   
   <
   
   100
   
   

   
    8 
   
   begin
   
   

   
    9 
   
   exec
   
    (
   
   @withquery
   
   );

   
   10 
   
   set
   
   @execcount
   
   =
   
   @execcount
   
   +
   
   1
   
   

   
   11 
   
   end
   
   

   
   12 
   
   

   
   13 
   
   /*
   
   子查询
   
   */
   
   

   
   14 
   
   declare
   
   @subquery
   
   varchar
   
   (
   
   5000
   
   )

   
   15 
   
   declare
   
   @execcount
   
   int
   
   =
   
   0
   
   

   
   16 
   
   set
   
   @subquery
   
   =
   
   '
   
   select orderyear,COUNT(distinct(custid)) numCusts

   
   17 
   
   from (select YEAR(orderdate),custid from sales.orders) as D(orderyear,custid)

   
   18 
   
   group by orderyear
   
   '
   
   

   
   19 
   
   while
   
   @execcount
   
   <
   
   100
   
   

   
   20 
   
   begin
   
   

   
   21 
   
   exec
   
    (
   
   @subquery
   
   );

   
   22 
   
   set
   
   @execcount
   
   =
   
   @execcount
   
   +
   
   1
   
   

   
   23 
   
   end
复制代码

 

这次做10次查询还是没有多大的差距，with语句用10个duration，子查询用了11个，有时候还会翻过来。于是把执行次数改成100，这次还是子查询使用的时间要少，截图如下

最终结论，子查询好比with语句效率高。

kylin-v
关注
专栏目录
SQL中的WITH语句
java专栏
03-31 192
[DB][ORACLE]SQL中使用WITH AS提高性能和可读性
王德封-逐浪
08-26 7373
利用WITH AS子句提高SQL的性能和可读性 一、 原理         WITH AS短语，也叫做子查询部分（subquery factoring），可以让你做很多事情，定义一个SQL片断，该SQL片断会被整个SQL语句所用到。         有的时候，是为了让SQL
关于SQL语句中With的用法
Strawberry0924的专栏
01-30 1万+
创建和使用 CTE 的指南下列指南应用于非递归 CTE。有关适用于递归 CTE 的指南，请参阅后面的“定义和使用递归 CTE 的指南”。CTE 之后必须跟随引用部分或全部 CTE 列的 SELECT、INSERT、UPDATE 或 DELETE 语句。也可以在 CREATE VIEW 语句中将 CTE 指定为视图中 SELECT 定义语句的一部分。 可以在非递归 CTE 中定义
SQL点滴9—使用with语句来写一个稍微复杂sql语句
weixin_34294649的博客
01-08 364
今天偶尔看到sql中也有with关键字，好歹也写了几年的sql语句，居然第一次接触，无知啊。看了一位博主的文章，自己添加了一些内容，做了简 单的总结，这个语句还是第一次见到，学习了。我从简单到复杂地写，希望高手们不要见笑。下面的sql语句设计到三个表，表的内容我用txt文件复制进去， 这里不妨使用上一个随笔介绍的建立端到端的package的方法将这些表导入到数据库中，具体的就不说了。 从这里...
SQL之WITH语句进阶
dacoolbaby的专栏
08-24 564
WITH语句其实是SQL ANSI标准语句之一。 我们在Oracle里面使用CONNECT BY语句进行递归查询。实际上，CONNECT BY仅仅属于Oracle自身的递归查询实现。 在标准的数据库中，如DB2，SQL SERVER，PostgreSQL都是支持WITH语句进行递归查询。   通过使用WITH语句进行递归查询，我们完全可以手动实现Oracle中的SYS_CONNECT_BY...
sql查询点滴记录
09-11
此外，SQL查询还可以涉及更复杂的操作，如JOINs（内连接、外连接、交叉连接等）、子查询、联接类型（如CROSS APPLY、OUTER APPLY）、透视（PIVOT）和反透视（UNPIVOT）。这些技术用于处理多表关联、数据转换和复杂...
程序员最好的SQL编辑助手
11-22
对于复杂的联接和子查询，SqlEditPlus的查询优化器能给出执行计划，帮助开发者分析和优化SQL性能。 除此之外，SqlEditPlus的宏功能使得自动化任务成为可能。程序员可以通过录制和编辑宏来实现重复性任务的自动化，...
Spark | 解析SparkSQL运行原理之Sql Parse 阶段
俗人
08-04 1581
spark.version=2.4.4 在学习SparkSQL运行原理前可以先了解下SparkSQL中涉及到的一些基础概念，SparkSQL架构 待续补充~~~~~
使用阿里云ODPS之点滴
百岁
10-07 1134
    趁着这次做网聚宝搜索服务化这个项目，体验了一下阿里云的重量级云产品ODPS，也就是传说中的云梯2。项目几近结束，现在回过头来看当初选择使用OPDS的决定还是相当正确的，ODPS在网聚宝搜索上云这个项目中起到了非常至关重要的作用，一句话总结：ODPS是非常靠谱的。从刚开始里了解ODPS开始，到引入项目中使用，进而通过优化加速在项目中使用得游刃有余，经历了一个过程，中间也走了不少弯路。在此...
点滴空间 v1.0 优雅版.rar
08-30
"点滴空间 v1.0 优雅版"很可能是一个经过精心设计的模板，具备用户管理和内容管理等功能，适合个人博客、小型企业网站或者代码分享社区使用。ASP源码的特点在于服务器端执行，能够根据用户请求动态生成HTML页面，...
SQL之with子句
热门推荐
积一时之步，臻千里之遥程
03-29 3万+
with 语句 with 语句相当于建立了一张 临时虚拟表 即利用with子句为子查询的数据集作为一个内存临时表. 在内存中解析，提高执行效率.，并且提高SQL语句的可读性，用完即销毁。 语法 可以同时定义多个临时表 With Subtable1 as (select 1...), //as和select中的括号都不能省略 Subtable2 as (select 2...), //后面的没有with，逗号分割，同一个主查询同级别地方，with子查询只能定义一次 … Subtablen as (
sql中的with子句
qq_44833298的博客
03-28 965
在此示例中，employee_salary是一个临时结果集，它包含一个查询，用于检索工资大于50000的员工的名称和工资。SQL中的WITH子句是一种语法结构，用于在查询中创建一个临时的、命名的结果集（或子查询）。在此语法中，alias是临时结果集的名称，subquery是定义结果集的子查询，SELECT和WHERE语句是用于与结果集进行进一步操作的主查询。WITH子句的优点在于，它可以使查询更清晰、更具可读性，减少嵌套查询的复杂性，并且可以多次在同一查询中使用同一个临时结果集。
SQL常用语法( WITH 语句)
ChinaLiyq的博客
07-26 2万+
WITH语句
sql with as 用法
qq_35741557的博客
08-04 1547
  WITH AS短语，也叫做子查询部分（subquery factoring）,如果WITH AS短语所定义的表名被调用两次以上，则优化器会自动将WITH AS短语所获取的数据放入一个TEMP表里，如果只是被调用一次，则不会。将WITH AS短语里的数据放入一个全局临时表里。很多查询通过这种方法都可以提高速度。 with cr as( select CountryRegionCode from...
【SQL中with的用法及代码经验分享】
xuexizhilua的博客
03-01 7775
Sql中with的日常使用场景及经验分享。
WITH AS 子查询
江城宴的博客
07-30 3969
WITH AS短语，也叫做子查询部分（subquery factoring），是用来定义一个SQL片断，该SQL片断会被整个SQL语句所用到。 1.with子句的返回结果存到用户的临时表空间中，只做一次查询，反复使用,提高效率。 2.在同级select前有多个查询定义的时候，第1个用with，后面的不用with，并且用逗号隔开。 3.前面的with子句定义的查
SQL中使用WITH AS提高性能
arhxwpp82382的博客
03-12 296
1. 案例起因 　　公司门店应用程序每天都要出一份报表，用来统计所有商品当天的期初库存数量、入库数量、出库数量 　　及当天的期末库存数量。运行半年以后，这份报表运行越来越慢，到现在，每次运行该报表显示当天数据时需要近20秒的时间。于是开发人员找到我，希望我看看，是不是可以使该报表运行的时间更短。 　　该报表就是一段SQL语句，主要由三部分组成，第一部分是计算每个商品的期初数量，第二...
WITH语句和子查询的性能比较 - SQL Server
最新发布
ElServer的博客
09-27 805
这是因为WITH语句可以将查询结果存储在临时表中，并在后续查询中重复使用，减少了重复计算的开销。本文将比较WITH语句和子查询在SQL Server中的性能，并提供相应的源代码作为示例。它允许我们在查询中定义一个临时表，并在后续的查询中引用它。综上所述，WITH语句和子查询在SQL Server中都是有用的工具，但在不同的情况下可能会有不同的性能表现。子查询的优点之一是它可以在查询中使用过滤条件，并针对每个主查询的结果进行处理。例如，当子查询的结果集很小，并且只需要执行一次时，使用子查询可能更简洁和高效。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值