你的位置:首页 > 数据库

[数据库]Sql — CTE公用表表达式和With用法总结


CTE(Common Table Expression) 公用表表达式,它是在单个语句的执行范围内定义的临时结果集,只在查询期间有效。它可以自引用,也可在同一查询中多次引用,实现了代码段的重复利用。

CTE最大的好处是提升T-Sql代码的可读性,可以更加优雅简洁的方式实现递归等复杂的查询。

CTE可用于:
  ⒈ 创建递归查询,这个应该是CTE最好用的地方
  ⒉ 在同一语句中多次引用生成的表
  3. 减少子查询和表变量,提高执行效率

CTE优点:
  1. 使用 CTE 可以获得提高可读性和轻松维护复杂查询的优点。同时,CTE要比表变量的效率高得多。
  2. 可以用来定义一个SQL片断,该SQL片断会被整个SQL语句所用到。有的时候,是为了让SQL语句的可读性更高些,也有可能是在UNION ALL的不同部分,作为提供数据的部分。
  3. 查询可以分为单独块、简单块、逻辑生成块。之后,这些简单块可用于生成更复杂的临时 CTE,直到生成最终结果集。

下面是CTE的语法:

WITH cte_name ( column_name [,...n] )AS(  CTE_query_definition –- Anchor member is defined.) 

 

使用示例
1. 查询临时结果集

WITH cte(CategoryID,CategoryName,ParentID,CategoryLevel)AS (SELECT CategoryID,CategoryName,ParentID,CategoryLevelFROM Category(NOLOCK)WHERE Status = 1 and parentid = 23)select * from cte;

注意:1.使用CTE的SQL语句应紧跟在相关的CTE后面。
   2.多重CTE中间用逗号,分隔。
     3.可以被紧跟着的一条SQL语句所使用多次,但不能被紧跟着的多条SQL语句使用。

2. 创建递归查询

WITH cte(CategoryID ,CategoryName,ParentID,CategoryLevel)AS (SELECT CategoryID,CategoryName,ParentID,CategoryLevelFROM Category(NOLOCK)WHERE Status= 1 and parentid in (21,22,23,25,26)UNION ALLSELECT t.CategoryID,t.CategoryName,t.ParentID,t.CategoryLevelFROM Category(NOLOCK) AS tINNER JOIN cte AS c ON t.parentid = c.CategoryID where Status= 1)select * from cte;

 

3. cte结果集和数据表关联

WITH cte(CategoryID,CategoryName,ParentID,CategoryLevel)AS (SELECT CategoryID,CategoryName,ParentID,CategoryLevelFROM Category(NOLOCK)WHERE Status = 1 and parentid = 23)select p.ProductId,p.ProductName,c.CategoryID,c.CategoryName,c.CategoryLevel from product p(NOLOCK)inner join cte c(NOLOCK) on p.CategoryId=c.CategoryID

 

以上三种,应该是cte表达式,最常见的用法,如果还有其他用法,非常乐意咱们一起探讨。

 




什么时候去马尔代夫旅游最便宜去马尔代夫旅游跟团要多少钱去马尔代夫旅游需要多少钱马尔代夫旅游大概多少钱到马尔代夫旅游要花多少钱魅力陕西 学生党最爱的七大旅游地 俗事如此纷繁 最佳静心地推荐(组图) 万圣节鬼混尝惊喜 咖啡厅妙趣横生 2015年深圳元宵节哪里有元宵灯会? 广州大学城科学馆订票?大学城科学馆门票预订? 大学城科学馆门票多少钱?广州大学城科学馆有没有学生票? 广东科学馆门票团购价格?大学城广东科学馆要门票吗? 广东科学馆好玩吗?大学城广东科学馆怎么样? 新兴天露山杜鹃花花期多长?云浮天露山杜鹃花开到什么时候? 霞客古渡自助游攻略?上林霞客古渡开车要多久? 新兴天露山漂流节好玩吗?2015天露山漂流节玩什么? 上林霞客古渡景区一日游攻略?霞客古渡景区路线? 出境旅游攻略之(一)谨对低价诱惑 四川康定特色菜:“麦秋”熏牛肉和牟家牛杂汤 感受海螺沟冰雪世界的温泉(图) 出境旅游攻略之(二)境外刷卡秘籍 ICL3241ECAZ-T Datasheet ICL3241ECAZ-T Datasheet ICL3243ECAZ-T Datasheet ICL3243ECAZ-T Datasheet ICL3244ECAZ-T Datasheet ICL3244ECAZ-T Datasheet 跟团去捷克旅游 跟团去捷克旅游 跟团去捷克旅游 去捷克旅游团 去捷克旅游团 去捷克旅游团 从深圳去捷克旅游 从深圳去捷克旅游 从深圳去捷克旅游