【SQL Server学习笔记】21：四种表表达式的基本使用

表表达式通过模块化的方式简化问题的解决方案，好处体现在逻辑上而不是性能上。

派生表

在外部查询的FROM子句中定义，存在范围仅为定义它的外部查询。

USE MyDB;SELECT *FROM(    SELECT num,firstname    FROM dbo.ok    WHERE num>72    ) AS nu_fi;

分配列别名

因为不能在逻辑处理顺序先于SELECT子句的其它查询子句中(如WHERE和GROUP BY)对SELECT子句分配的列别名引用，故可利用外部查询的任何子句都可以引用在内部查询的SELECT子句中分配的列别名。

USE MyDB;--orderyear是内部查询SELECT里定义的别名,COUNT(DISTINCT计算不重复结果数目SELECT orderyear,COUNT(DISTINCT myid) AS numidFROM(    SELECT YEAR(mydate) AS orderyear,myid    FROM dbo.ok    ) AS neiGROUP BY orderyear;--外部查询的任何子句即便先于外SELECT也可以用这个别名

使用参数

定义派生表的查询中可以使用参数。

DECLARE @can AS INT;SET @can=72;    --2008以后可以直接DECLARE @can AS INT=72USE MyDB;SELECT *FROM(    SELECT num,firstname    FROM dbo.ok    WHERE num>@can  --使用参数    ) AS nu_fi;

嵌套

如果要用本身就定义了派生表的查询去定义派生表，得到的就是嵌套派生表。因为派生表在FROM子句中定义，而不是单独定义，所以会产生嵌套，嵌套使代码难读。

派生表的多引用

还是因为派生表在外部查询的FROM子句中定义，逻辑优先顺序并不优先于外部查询，所以对外部查询FROM子句进行处理时派生表并不存在，不能使用同一派生表的多个实例。
可以看到LEFT JOIN前后都是一样的派生表，但是要用两个实例呢。

USE MyDB;SELECT    Cur.orderyear AS 这年,    Cur.sume AS 这年总数,    Prv.sume AS 去年总数,    Cur.sume-Prv.sume AS 增长数FROM(    SELECT YEAR(mydate) AS orderyear,    SUM(CAST(num AS INT)) AS sume    FROM dbo.ok    GROUP BY YEAR(mydate)    ) AS Cur    LEFT JOIN    (    SELECT YEAR(mydate) AS orderyear,    SUM(CAST(num AS INT)) AS sume    FROM dbo.ok    GROUP BY YEAR(mydate)    ) AS Prv    ON Cur.orderyear=Prv.orderyear+1; --相差1年

公用表表达式(CTE)

CTE先定义，再查询，可以一次定义多个，而且可以使用前面定义好的CTE而不用语法嵌套：

USE MyDB;WITH C1 AS(    SELECT YEAR(mydate) AS orderyear,num    FROM dbo.ok),C2 AS(    SELECT orderyear,SUM(CAST(num AS INT)) AS sume    FROM C1    GROUP BY orderyear)SELECT orderyear,sumeFROM C2WHERE sume >10;

CTE的多引用

因为先定义再使用，所以可以引用同一个CTE的多个实例。

USE MyDB;WITH tongyige AS(    SELECT YEAR(mydate) AS orderyear,    SUM(CAST(num AS INT)) AS sume    FROM dbo.ok    GROUP BY YEAR(mydate))SELECT    Cur.orderyear AS 这年,    Cur.sume AS 这年总数,    Prv.sume AS 去年总数,    Cur.sume-Prv.sume AS 增长数FROM    tongyige AS CurLEFT JOIN     tongyige AS PrvON Cur.orderyear=Prv.orderyear+1;

视图和内联表值函数(inline TVF)都是可重用的表表达式，其定义存储在数据库对象中。

视图

USE MyDB;IF OBJECT_ID('dbo.view1') IS NOT NULL    DROP VIEW dbo.view1;--GO前面的如有报错还会执行GO后面的语句GOCREATE VIEW dbo.view1AS    SELECT        firstname,lastname    FROM dbo.ok    WHERE Pid IS NOT NULL;

可以像查询数据库中的其它表一样查询视图。视图可以理解成对现有的表的一种视角，整合到一张表里，方便我们从某些角度去观察和使用。

SELECT *FROM dbo.view1;

用完不妨删除它：

IF OBJECT_ID('dbo.view1') IS NOT NULL    DROP VIEW dbo.view1;

内联表值函数(inline TVF)

内联TVF还能支持输入参数，其它方面和视图相似，所以可以看成参数化的视图。

USE MyDB;IF OBJECT_ID('dbo.fn_GetById') IS NOT NULL    DROP FUNCTION dbo.fn_GetById;GOCREATE FUNCTION dbo.fn_GetById    (@id AS INT) RETURNS TABLE  --指定接受的参数类型和名称ASRETURN  --指定返回的表    SELECT *    FROM dbo.ok    WHERE @id=myid; --在这里用到了参数作相等性比较

查询一下TVF，要提供参数：

SELECT *FROM dbo.fn_GetById(1034) AS CO; --为表表达式指定别名是好习惯

用完不妨删除它：

IF OBJECT_ID('dbo.fn_GetById') IS NOT NULL    DROP FUNCTION dbo.fn_GetById;

还有一些高级的用法，递归CTE、视图-ORDER BY等以后再详学。