如何在 CTE 之后使用 if 语句（SQL Server 2005）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1558091/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I use if statement after a CTE (SQL Server 2005)
提问by priyanka.sarkar
Last night I was writing a simple T-SQL program something like this
昨晚我正在写一个简单的 T-SQL 程序，就像这样
DECLARE @ROLEID AS INT
SELECT @ROLEID = [ROLE ID] FROM TBLROLE
;WITH CTE
AS
(
SELECT * FROM SOMETABLE
)
IF (@ROLEID = 1)
BEGIN
//SOMECODE
END
ELSE IF(@ROLEID = 2)
BEGIN
//SOMECODE
END
ELSE
BEGIN
//SOMECODE
END
I found after compilation that it is throwing error something like "Incorrect statement near if"
我在编译后发现它抛出错误，例如“if 附近的错误语句”
What is wrong?
怎么了？
However, I did that by using some other way. But I wanted to know why it did not work!
但是，我通过其他方式做到了这一点。但我想知道为什么它不起作用！
采纳答案by Remus Rusanu
Common table expressionsare defined within the context of a single statement:
公共表表达式在单个语句的上下文中定义：
WITH cte_name AS (
<cte definition>)
<statement that uses cte>;
So you can do something like:
因此，您可以执行以下操作：
WITH CTE
AS
(
SELECT * FROM SOMETABLE
)
SELECT * FROM CTE;
or
或者
WITH CTE
AS
(
SELECT * FROM SOMETABLE
)
UPDATE CTE
SET somefield = somevalue
WHERE id = somekey;
A CTE must be followed by a single SELECT, INSERT, UPDATE, MERGE, or DELETE statement that references some or all the CTE columns. A CTE can also be specified in a CREATE VIEW statement as part of the defining SELECT statement of the view
CTE 后面必须跟一个引用部分或全部 CTE 列的 SELECT、INSERT、UPDATE、MERGE 或 DELETE 语句。CTE 也可以在 CREATE VIEW 语句中指定为视图的定义 SELECT 语句的一部分
回答by gbn
The closest you'll get is using a UNION ALL to do a crude switched select:
最接近的是使用 UNION ALL 进行粗略的切换选择：
DECLARE @ROLEID AS INT
SELECT @ROLEID = [ROLE ID] FROM TBLROLE
;WITH CTE
AS
(
SELECT * FROM SOMETABLE
)
SELECT
--somecolumns
FROM
CTE
--other stuff too
WHERE
@ROLEID = 1
UNION ALL
SELECT
--somecolumns
FROM
CTE
--other stuff too
WHERE
@ROLEID = 2
UNION ALL
SELECT
--somecolumns
FROM
CTE
--other stuff too
WHERE
@ROLEID = 3
...
UNION ALL
SELECT
--somecolumns
FROM
CTE
--other stuff too
WHERE
@ROLEID = n
回答by DerpyNerd
A little late but I can't be the only one bumping into this.
有点晚了，但我不能是唯一一个遇到这种情况的人。
A solution could be to create a temporary table like this:
一个解决方案可能是创建一个这样的临时表：
-- If previous run of this query fails, the temp table will be deleted.
-- Selecting into creates the temp table which fails if it already exists
IF EXISTS(SELECT [name] FROM tempdb.sys.tables WHERE [name] like '#dtBalansOpgesteldGefilterd%') BEGIN
DROP TABLE #temp
END;
;WITH CTE
AS
(
SELECT * FROM SOMETABLE
)
-- Followed by select statement as required
SELECT *
INTO #temp
FROM CTE
IF @awsome = 1
BEGIN
SELECT 'WHATEVERYOUWANT' AS WhateverColumnNameYouWant, *
FROM #temp
END
回答by CW1255
Try putting the CTE in the IF. It worked for me.
尝试将 CTE 放在 IF 中。它对我有用。
IF @awsome = 1
BEGIN
;WITH CTE
AS
(
SELECT * FROM SOMETABLE
)
SELECT 'WHATEVERYOUWANT' FROM CTE
END
ELSE IF @awesome = 2
BEGIN
;WITH CTE2
AS
(
SELECT * FROM SOMETABLE
)
SELECT 'WHATEVERYOUWANT' FROM CTE2
END
