none
求助关于表变量和临时表,with as 的一些问题 RRS feed

  • 问题

  • 在编写一些存储过程中会遇到一些表数据,用临时表或表变量都可以完成。
    最近看到with as·······关键字

    with tempTB
    as
    (
       select * from 表

    那么tempTB也可以当表一样查询,但是他好像不是表,找了下资料,也没有揭开迷惑,来这里向大家求教。

    with as 这样生成的表和 临时表或者表变量 他们之间有什么区别,请详细告知。
    还有在效率上也给予一些介绍 谢了。
    2009年5月21日 11:19

答案

  • 公用表表达式(CTE)可以提高SQL语句的可维护性,同时CTE要比表变量的效率高得多。CTE除了可以简化嵌套SQL语句外,还可以进行递归调用.

    在使用CTE时应注意如下几点:

    1.CTE后面必须直接跟使用CTE的SQL语句(如select、insert、update等),否则,CTE将失效。
    2.CTE后面也可以跟其他的CTE,但只能使用一个with,多个CTE中间用逗号(,)分隔。
    3.如果CTE的表达式名称与某个数据表或视图重名,则紧跟在该CTE后面的SQL语句使用的仍然是CTE,当然,后面的SQL语句使用的就是数据表或视图了,如下面的SQL语

    句所示:

      -- table1是一个实际存在的表
      with
      table1 as
      (
        select * from persons where age < 30
      )
      select * from table1 -- 使用了名为table1的公共表表达式
      select * from table1 -- 使用了名为table1的数据表

    4.CTE 可以引用自身,也可以引用在同一 WITH 子句中预先定义的 CTE。不允许前向引用。

    5.不能在 CTE_query_definition 中使用以下子句:

      (1)COMPUTE 或 COMPUTE BY

      (2)ORDER BY(除非指定了 TOP 子句)

      (3)INTO

      (4)带有查询提示的 OPTION 子句

      (5)FOR XML

      (6)FOR BROWSE

    6.如果将CTE用在属于批处理的一部分的语句中,那么在它之前的语句必须以分号结尾,如下面的SQL所示:

    declare@snvarchar(3)
    set@s='C%'
    ; --必须加分号
    with
    t_tree as
    (
      ...
    )
    ...

    • 已建议为答案 肖小勇 2009年5月22日 4:09
    • 已标记为答案 如蜗牛 2009年5月23日 5:48
    2009年5月22日 4:06

全部回复

  • with 子句和视图有些类似。不是临时表或表变量

    你可以查一查CTE这个概念

    还有相关的一些帖子

    http://social.msdn.microsoft.com/forums/en-US/transactsql/thread/f2042366-bc5a-4a43-94be-2f5e08d0dd50/


    SQL SERVER Engine Test
    2009年5月21日 12:54
    版主
  • 在SQL Server Books Online中有详尽的描述 http://msdn.microsoft.com/en-us/library/ms175972(SQL.90).aspx
    2009年5月22日 3:33
  • 公用表表达式(CTE)可以提高SQL语句的可维护性,同时CTE要比表变量的效率高得多。CTE除了可以简化嵌套SQL语句外,还可以进行递归调用.

    在使用CTE时应注意如下几点:

    1.CTE后面必须直接跟使用CTE的SQL语句(如select、insert、update等),否则,CTE将失效。
    2.CTE后面也可以跟其他的CTE,但只能使用一个with,多个CTE中间用逗号(,)分隔。
    3.如果CTE的表达式名称与某个数据表或视图重名,则紧跟在该CTE后面的SQL语句使用的仍然是CTE,当然,后面的SQL语句使用的就是数据表或视图了,如下面的SQL语

    句所示:

      -- table1是一个实际存在的表
      with
      table1 as
      (
        select * from persons where age < 30
      )
      select * from table1 -- 使用了名为table1的公共表表达式
      select * from table1 -- 使用了名为table1的数据表

    4.CTE 可以引用自身,也可以引用在同一 WITH 子句中预先定义的 CTE。不允许前向引用。

    5.不能在 CTE_query_definition 中使用以下子句:

      (1)COMPUTE 或 COMPUTE BY

      (2)ORDER BY(除非指定了 TOP 子句)

      (3)INTO

      (4)带有查询提示的 OPTION 子句

      (5)FOR XML

      (6)FOR BROWSE

    6.如果将CTE用在属于批处理的一部分的语句中,那么在它之前的语句必须以分号结尾,如下面的SQL所示:

    declare@snvarchar(3)
    set@s='C%'
    ; --必须加分号
    with
    t_tree as
    (
      ...
    )
    ...

    • 已建议为答案 肖小勇 2009年5月22日 4:09
    • 已标记为答案 如蜗牛 2009年5月23日 5:48
    2009年5月22日 4:06