SQLServer中withtmp临时表的⽤法
SQL Server 中 with tmp 临时表的⽤法
----------with临时表⽤法，有时候采⽤临时表⽐采⽤in的效率更⾼，避免了全表扫描。实例中实现了查询普通题、⼤题、⼦题⽬的sql
-----------(对于⼤题套⼩题，采⽤先查⼩题信息再反查⼤题相关信息的⽅式)
⼀、简答
1. 会计师的定义？ a。AAAA b。BBBB c。CCCC d。DDDD⼆、判断
2.下⾯那个是对的？ a。对 b。错三、简答题
3. 看⼀段⽂字描述，回答每个⼩题⽬： <1>、第⼀个⼩题⽬ a。AAAA b。BBBB c。CCCC d。DDDD
<2>、第⼆个⼩题⽬ a。AAAA b。BBBB c。CCCC d。DDDD------------with tmp as (
select distinct parentID
from QZ_SIM_PAPER_QUESTION WHERE simPaperID = 205 and isDel <> 1 and ansResult = -1 and queType <> 4 and parentID <> 0)
select * from (SELECT t.*
FROM QZ_SIM_PAPER_QUESTION t,tmp
WHERE (t.questionID = tmp.parentID or t.parentID = tmp.parentID) and t.simPaperID = 205) tmp2 union all (SELECT *
FROM QZ_SIM_PAPER_QUESTION
WHERE isDel <> 1 and queType <> 4 and parentID = 0 and simPaperID = 205 and ansResult = -1) order by parentID,queType在使⽤CTE时应注意如下⼏点：
1. CTE后⾯必须直接跟使⽤CTE的SQL语句（如select、insert、update等），否则，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中间⽤逗号（,）分隔;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语句使⽤的就是数据表或视图了;-- 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 ⽤在属于批处理的⼀部分的语句中，那么在它之前的语句必须以分号结尾。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)
