【解决马上结贴】SQL中With as 为什么不可以这样用啊..

Question

如果这样就可以
WITH tb as
(
SELECT * FROM SCE.Booth
)
SELECT * FROM tb但我改成下面的方法就不行..
DECLARE @sql varchar(4000)
SET @sql= 'SELECT * FROM SCE.Booth'
WITH tb as
(
EXEC(@sql)
)
SELECT * FROM tb如何改可以是上面的运行呢?

Answer 1

[ WITH <common_table_expression> [ ,...n ] ]<common_table_expression>::=
        expression_name [ ( column_name [ ,...n ] ) ]
    AS
        ( CTE_query_definition )CTE_query_definition 
Specifies a SELECT statement whose result set populates the common table expression. The SELECT statement for CTE_query_definition must meet the same requirements as for creating a view, except a CTE cannot define another 
从以上描述可以看出，as 后面必须是一个SELECT 语句

Answer 2

1 :普通SQL语句可以用Exec执行 eg: Select * from tableName 
Exec('select * from tableName') 
Exec sp_executesql N'select * from tableName' -- 请注意字符串前一定要加N 2:字段名，表名，数据库名之类作为变量时，必须用动态SQL eg: 
declare @fname varchar(20) 
set @fname = 'FiledName' 
Select @fname from tableName -- 错误,不会提示错误，但结果为固定值FiledName,并非所要。 
Exec('select ' + @fname + ' from tableName') -- 请注意 加号前后的 单引号的边上加空格 当然将字符串改成变量的形式也可 
declare @fname varchar(20) 
set @fname = 'FiledName' --设置字段名 declare @s varchar(1000) 
set @s = 'select ' + @fname + ' from tableName' 
Exec(@s) -- 成功 
exec sp_executesql @s -- 此句会报错 declare @s Nvarchar(1000) -- 注意此处改为nvarchar(1000) 
set @s = 'select ' + @fname + ' from tableName' 
Exec(@s) -- 成功 
exec sp_executesql @s -- 此句正确 3. 输出参数 
declare @num int, 
@sqls nvarchar(4000) 
set @sqls='select count(*) from tableName' 
exec(@sqls) 
--如何将exec执行结果放入变量中？ declare @num int, 
@sqls nvarchar(4000) 
set @sqls='select @a=count(*) from tableName ' 
exec sp_executesql @sqls,N'@a int output',@num output 
select @num 

Answer 3

公用表表达式（CTE）
CTE的语法：
[ WITH <common_table_expression> [ , n ] ]
<common_table_expression>::=
        expression_name [ ( column_name [ , n ] ) ]
    AS
        ( CTE_query_definition )
在使用CTE时应注意如下几点：
1. CTE后面必须直接跟使用CTE的SQL语句（如select、insert、update等），否则，CTE将失效。如下面的SQL语句将无法正常使用CTE：
2. CTE后面也可以跟其他的CTE，但只能使用一个with，多个CTE中间用逗号（,）分隔
3. 如果CTE的表达式名称与某个数据表或视图重名，则紧跟在该CTE后面的SQL语句使用的仍然是CTE
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 用在属于批处理的一部分的语句中，那么在它之前的语句必须以分号结尾

Answer 4

SELECT 
sku, 
sum(initqty) as initqty, 
sum(inqty) as inqty,sum(outqty) as outqty, 
sum(lastqty) as lastqty 
FROM ( 
SELECT sku,lastqty as initqty,0 as inqty,0 as outqty,0 as lastqty 
FROM fin 
WHERE day=to_date('20030713','yyyymmdd') 
UNION ALL 
SELECT sku,0 as initqty,inqty,outqty,0 as lastqty 
FROM fin 
WHERE day>=to_date('20030714','yyyymmdd') and day<=to_date('20030714','yyyymmdd') 
UNION ALL 
SELECT sku,0 as initqty,0 as inqty,0 as outqty,lastqty 
FROM fin 
WHERE day=to_date('20030714','yyyymmdd') 
) 
GROUP BY sku;
我们来看该SQL所花费的时间为： 
SQL> set timing on 
SQL> / 
。。 
SKU INITQTY INQTY OUTQTY LASTQTY 
-------- ---------- ---------- ---------- ---------- 
00106162 0 0 12 60 
00106467 0 20 10 60 
已选择956行。已用时间: 00: 00: 19.08然后，我们来对该SQL进行改写一番，如下所示： 
WITH result AS ( 
SELECT /*+ materialize */ day,sku,inqty,outqty,lastqty 
FROM fin 
WHERE day>=to_date('20030713','yyyymmdd') AND day<=to_date('20030714','yyyymmdd')) 
SELECT 
sku, 
sum(initqty) as initqty, 
sum(inqty) as inqty, 
sum(outqty) as outqty, 
sum(lastqty) as lastqty 
FROM ( 
SELECT sku,lastqty as initqty,0 as inqty,0 as outqty,0 as lastqty 
FROM result 
WHERE day=to_date('20030713','yyyymmdd') 
UNION ALL 
SELECT sku,0 as initqty,inqty,outqty,0 as lastqty 
FROM result 
WHERE day=to_date('20030714','yyyymmdd') 
UNION ALL 
SELECT sku,0 as initqty,0 as inqty,0 as outqty,lastqty 
FROM result 
WHERE day=to_date('20030714','yyyymmdd') 
) 
GROUP BY sku;我们来看修改后的SQL所花费的时间为： 
SQL> set timing on 
SQL> / 
。。 
SKU INITQTY INQTY OUTQTY LASTQTY 
-------- ---------- ---------- ---------- ---------- 
00106162 0 0 12 60 
00106467 0 20 10 60 
已选择956行。已用时间: 00: 00: 06.06从这里可以看到，通过WITH AS可以从20秒降低到6秒，几乎提高了65%的性能。