• sql with as 用法


    Server 2005中提供了公用表表达式(CTE),使用CTE,可以使SQL语句的可维护性,同时,CTE要比表变量的效率高得多。


    下面是CTE的语法:
    [ WITH <common_table_expression> [ ,n ] ]
    <common_table_expression>::=
            expression_name [ ( column_name [ ,n ] ) ]
        AS
            ( CTE_query_definition )

        现在使用CTE来解决上面的问题,SQL语句如下:

    with
    cr as
    (
        select CountryRegionCode from person.CountryRegion where Name like 'C%'
    )

    select * from person.StateProvince where CountryRegionCode in (select * from cr)

        其中cr是一个公用表表达式,该表达式在使用上与表变量类似,只是SQL Server 2005在处理公用表表达式的方式上有所不同。
        在使用CTE时应注意如下几点:
    1. CTE后面必须直接跟使用CTE的SQL语句(如select、insert、update等),否则,CTE将失效。如下面的SQL语句将无法正常使用CTE:

    with
    cr as
    (
        select CountryRegionCode from person.CountryRegion where Name like 'C%'
    )
    select * from person.CountryRegion  -- 应将这条SQL语句去掉
    -- 使用CTE的SQL语句应紧跟在相关的CTE后面 --
    select * from person.StateProvince where CountryRegionCode in (select * from cr)

    2. CTE后面也可以跟其他的CTE,但只能使用一个with,多个CTE中间用逗号(,)分隔,如下面的SQL语句所示:
    with
    cte1 as
    (
        select * from table1 where name like 'abc%'
    ),
    cte2 as
    (
        select * from table2 where id > 20
    ),
    cte3 as
    (
        select * from table3 where price < 100
    )
    select a.* from cte1 a, cte2 b, cte3 c where a.id = b.id and a.id = c.id

    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 @s nvarchar(3)
    set @s = 'C%'
    ;  -- 必须加分号
    with
    t_tree as
    (
        select CountryRegionCode from person.CountryRegion where Name like @s
    )
    select * from person.StateProvince where CountryRegionCode in (select * from t_tree)

    7、CTE除了可以简化嵌套SQL语句外,还可以进行递归调用

  • 相关阅读:
    IOS AutoLayout 代码实现约束—VFL
    理解iOS Event Handling
    一些优秀的iOS第三方库
    iOS中NSNotification、delegate、KVO三者之间的区别与联系?
    laravel 框架加载自定义函数/类文件
    Nodejs 使用 socket.io 简单实现实时通信
    Redis 与 Memcache 的异同之处
    Redis 服务安装
    PHP 依赖管理神器 Composer 基本使用
    Ajax无刷新图片插件使用
  • 原文地址:https://www.cnblogs.com/wolfocme110/p/3840462.html
Copyright © 2020-2023  润新知