WITH AS ,case when then else end 在sql中的运用
WITH FOOTNOTE_LIST (CARRIER_CD, TARIFF_CD, FOOTNOTE_CD, CATEGORY_NO, SEQUENCE_NO)AS (SELECT DISTINCT X.CARRIER_CD,
X.TARIFF_CD,
X.FOOTNOTE_CD,
X.CATEGORY_NO,
X.SEQUENCE_NO
FROM${parSchema}FTNREC2_CATCTRL X
INNER JOIN ${parSchema}FTNBATCH Y
ON X.FTNT_BTCH_SG = Y.FTNT_BTCH_SG
WHEREX.FOOTNOTE_CD = :ftntCode
AND X.CATEGORY_NO = :categoryNumber
AND Y.FTNT_BTCH_SG IN ( :batchSGs ))
SELECT A.CARRIER_CD,
A.TARIFF_CD,
A.FOOTNOTE_CD,
A.CATEGORY_NO,
A.SEQUENCE_NO,
A.FTNREC2_SG,
A.CREATE_TS,
A.BATCH_CI,
A.BATCH_NO,
A.LOCALE_1_TYPE,
A.LOCALE_1_CD,
A.LOCALE_2_TYPE,
A.LOCALE_2_CD,
A.EDOT_ACTION_TG,
A.POSTPONE_ACTION_TG,
CASE WHEN B.BATCH_STATUS_TG IS NULL THEN 'F' ELSE B.BATCH_STATUS_TG END AS BATCH_STATUS_TG,
B.FTNT_BTCH_SG,
C.SEGMENT_ORDER_NO,
C.FTNCAT_SG,
C.RELATIONAL_IND,
C.INBND_OUTBND_TG,
C.DIRECTIONAL_TG
FROM FOOTNOTE_LIST T
INNER JOIN ${parSchema}FTNREC2_CATCTRL A
ON T.CARRIER_CD = A.CARRIER_CD
AND T.TARIFF_CD = A.TARIFF_CD
AND T.FOOTNOTE_CD = A.FOOTNOTE_CD
AND T.CATEGORY_NO = A.CATEGORY_NO
AND T.SEQUENCE_NO = A.SEQUENCE_NO
LEFT OUTER JOIN ${parSchema}FTNBATCH B
ON A.FTNT_BTCH_SG = B.FTNT_BTCH_SG
LEFT OUTER JOIN ${parSchema}FTNSTR_CAT C
ON A.FTNREC2_SG = C.FTNREC2_SG
ORDERBY A.CARRIER_CD,
A.TARIFF_CD,
A.FOOTNOTE_CD,
A.CATEGORY_NO,
A.SEQUENCE_NO,
A.FTNREC2_SG,
C.SEGMENT_ORDER_NO,
A.CREATE_TS
================================
使用WITH AS提高性能简化嵌套SQL
一．WITH AS的含义
WITH AS短语，也叫做子查询部分（subquery factoring），可以让你做很多事情，定义一个SQL片断，该SQL片断会
被整个SQL语句所用到。有的时候，是为了让SQL语句的可读性更高些，也有可能是在UNION ALL的不同部分，作为提供数
据的部分。
特别对于UNION ALL比较有用。因为UNION ALL的每个部分可能相同，但是如果每个部分都去执行一遍的话，则成本太高，
所以可以使用WITH AS短语，则只要执行一遍即可。如果WITH AS短语所定义的表名被调用两次以上，则优化器会自动将
WITH AS短语所获取的数据放入一个TEMP表里，如果只是被调用一次，则不会。而提示materialize则是强制将WITH AS
短语里的数据放入一个全局临时表里。很多查询通过这种方法都可以提高速度。
二．使用方法
先看下面一个嵌套的查询语句：
select * from person.StateProvince where CountryRegionCode in (
select CountryRegionCode from person.CountryRegion where Name like 'C%') 上面的查询语句使用了一个子查询。虽然这条SQL语句并不复杂，但如果嵌套的层次过多，会使SQL语句非常难以阅
读和维护。因此，也可以使用表变量的方式来解决这个问题。
SQL语句如下：
declare @t table(CountryRegionCode nvarchar(3))insert into @t(CountryRegionCode)(select CountryRegionCode from person.CountryRegion where Name like 'C%')select * from person.StateProvince where CountryRegionCodein (select * from @t) 虽然上面的SQL语句要比第一种方式更复杂，但却将子查询放在了表变量@t中，这样做将使SQL语句更容易维护，但又
会带来另一个问题，就是性能的损失。由于表变量实际上使用了临时表，从而增加了额外的I/O开销，因此，表变量的方式
并不太适合数据量大且频繁查询的情况。为此，在SQL Server 2005中提供了另外一种解决方案，这就是公用表表达式（CTE），使用CTE，可以增加SQL语句的可维护性，同时，CTE要比表变量的效率高得多。
下面是CTE的语法：
[ WITH[ ,n ] ]
::=
expression_name [ ( column_name [ ,n ] ) ]
AS
( CTE_query_definition )
现在使用CTE来解决上面的问题，SQL语句如下：
http://common.cnblogs.com/images/copycode.gif
with cr
as (
select CountryRegionCode from person.CountryRegion where Name like 'C%' )
select * from person.StateProvince where CountryRegionCode in (select * from cr) http://common.cnblogs.com/images/copycode.gif
其中cr是一个公用表表达式，该表达式在使用上与表变量类似，只是SQL Server 2005在处理公用表表达式的方式上有
所不同。
在使用CTE时应注意如下几点：
1. CTE后面必须直接跟使用CTE的SQL语句（如select、insert、update等），否则，CTE将失效。如下面的SQL语句将无法正
常使用CTE：
http://common.cnblogs.com/images/copycode.gif
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) http://common.cnblogs.com/images/copycode.gif
2. CTE后面也可以跟其他的CTE，但只能使用一个with，多个CTE中间用逗号（,）分隔，如下面的SQL语句所示：
http://common.cnblogs.com/images/copycode.gif
with cte1
as (
select * from table1 where name like 'abc%' ),
cte2
as (
select * from table2 where>), 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 http://common.cnblogs.com/images/copycode.gif
3. 如果CTE的表达式名称与某个数据表或视图重名，则紧跟在该CTE后面的SQL语句使用的仍然是CTE，当然，后面的SQL语句
使用的就是数据表或视图了，如下面的SQL语句所示：
http://common.cnblogs.com/images/copycode.gif
--table1是一个实际存在的表with table1
as (
select * from persons where age < 30 )
select * from table1--使用了名为table1的公共表表达式select * from table1--使用了名为table1的数据表http://common.cnblogs.com/images/copycode.gif
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所示：
http://common.cnblogs.com/images/copycode.gif
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) ====================================================================
CASE命令有两种语句格式。
语法形式1：CASE expression
WHEN expression_11 THEN expression_12
…
WHEN expression_n1 THEN expression_n2
END
该语句的执行过程是：将CASE后面表达式的值与各WHEN子句中的表达式的值进行比较，如果两者相等，则返回THEN后的表达式的值，然后跳出CASE语句，否则返回ELSE子句中的表达式的值。ELSE子句是可选项。当CASE语句中不包含ELSE子句时，如果所有比较失败时，CASE语句将返回NULL。
【例】从数据表stud_info中，选取stud_id、grade，如果grade为“男”则输出“M”，如果为“女”输出“F”。
SELECT stud_id, sex=CASE gender
WHEN ’男’ THEN ’M’
WHEN ’女’ THEN ’F’
END
FROM stud_info
语法形式2：CASE WHEN condition_expression_1 THEN expression_1
…
WHEN condition_expression_n THEN expression_n
END
该语句的执行过程是：首先测试WHEN后的条件表达式的值，如果其值为真，则返回THEN后面的表达式的值，否则测试下一个WHEN子句中的表达式的值，如果所有WHEN子句后的表达式的值都为假，则返回ELSE后的表达式的值，如果在CASE语句中没有ELSE子句，则CASE表达式返回NULL。
注意：CASE命令可以嵌套到SQL语句中。
【例】从stud_grade表中查询所有同学考试成绩情况，凡成绩为空者输出“未考”、小于60分输出“不及格”、60分至70分输出“及格”、70分至90分输出“良好”、大于或等于90分时输出“优秀”。
SELECT stud_id,name,score=CASE WHEN grade IS NULL THEN ’未考’
WHEN grade=60 AND grade=70 AND grade=90 THEN ’优秀’
END
FROM stud_grade
Case具有两种格式。简单Case函数和Case搜索函数。
--简单Case函数
CASE sex
WHEN '1' THEN '男'
WHEN '2' THEN '女'
ELSE '其他' END
--Case搜索函数
CASE WHEN sex = '1' THEN '男'
WHEN sex = '2' THEN '女'
ELSE '其他' END
这两种方式，可以实现相同的功能。简单Case函数的写法相对比较简洁，但是和Case搜索函数相比，功能方面会有些限制，比如写判断式。
还有一个需要注意的问题，Case函数只返回第一个符合条件的值，剩下的Case部分将会被自动忽略。
两者中可以根据不同的需求来选择
其它网友的实例：
Id name dept
1 aa dept1
2 bb dept1
3 cc dept2
SELECT SUM(CASE decp WHEN 'decp1' THEN 1 ELSE 0 end ) AS decp1,
sum(case decp when 'decp2'then 1 else 0 end)as decp2
FROM teacher
生成:
dept1 dept2
2 1
有一张表，里面有3个字段：语文，数学，英语。其中有3条记录分别表示语文70分，数学80分，英语58分，请用一条sql语句查询出这三条记录并按以下条件显示出来（并写出您的思路）：
大于或等于80表示优秀，大于或等于60表示及格，小于60分表示不及格。
显示格式：
语文 数学 英语
及格 优秀 不及格
------------------------------------------
select
(case when 语文>=80 then '优秀'
when 语文>=60 then '及格'
else '不及格') as 语文,
(case when 数学>=80 then '优秀'
when 数学>=60 then '及格'
else '不及格') as 数学,
(case when 英语>=80 then '优秀'
when 英语>=60 then '及格'
else '不及格') as 英语,
from table
CASE 可能是 SQL 中被误用最多的关键字之一。虽然你可能以前用过这个关键字来创建字段，但是它还具有更多用法。例如，你可以在 WHERE 子句中使用 CASE。
首先让我们看一下 CASE 的语法。在一般的 SELECT 中，其语法如下：
SELECT=
CASE
WHENTHEN
WHENTHEN
ELSE
END
在上面的代码中需要用具体的参数代替尖括号中的内容。下面是一个简单的例子：
USE pubs
GO
SELECT
> 'Price Range' =
CASE
WHEN price IS NULL THEN 'Unpriced'
WHEN price < 10 THEN 'Bargain'
WHEN price BETWEEN 10 and 20 THEN 'Average'
ELSE 'Gift to impress> END
FROM> ORDER BY price
GO
这是 CASE 的典型用法，但是使用 CASE 其实可以做更多的事情。比方说下面的 GROUP BY 子句中的 CASE：
SELECT 'Number of>
FROM> GROUP BY
CASE
WHEN price IS NULL THEN 'Unpriced'
WHEN price < 10 THEN 'Bargain'
WHEN price BETWEEN 10 and 20 THEN 'Average'
ELSE 'Gift to impress> END
GO
你甚至还可以组合这些选项，添加一个 ORDER BY 子句，如下所示：
USE pubs
GO
SELECT
CASE
WHEN price IS NULL THEN 'Unpriced'
WHEN price < 10 THEN 'Bargain'
WHEN price BETWEEN 10 and 20 THEN 'Average'
ELSE 'Gift to impress> END AS Range,
>
FROM> GROUP BY
CASE
WHEN price IS NULL THEN 'Unpriced'
WHEN price < 10 THEN 'Bargain'
WHEN price BETWEEN 10 and 20 THEN 'Average'
ELSE 'Gift to impress> END,
> ORDER BY
CASE
WHEN price IS NULL THEN 'Unpriced'
WHEN price < 10 THEN 'Bargain'
WHEN price BETWEEN 10 and 20 THEN 'Average'
ELSE 'Gift to impress> END,
> GO
注意，为了在 GROUP BY 块中使用 CASE，查询语句需要在 GROUP BY 块中重复 SELECT 块中的 CASE 块。
页:
[1]