英文:
If condition with CTE in sql
问题
以下是已翻译的代码部分：
我正在尝试创建一个视图，在该视图中，我需要对年份进行条件筛选。我正在尝试以下代码，但是我遇到了错误。请问是否可能实现这个要求？而且要求的输出必须仅使用CTE。
错误信息如下：'if'附近的语法不正确。
以下是代码：
Declare @year varchar(20)
Set @year = '2022';
With CTE as
(
If @year = '2022'
Begin
(
Select
@year ,
Prev_date = (select DATEADD(month, -1,GETDATE()))
)
End;
Else
Begin
(
Select @year,
Prev_date= (select DATEADD(month, -2,GETDATE()))
)
End;
)
Select *
From CTE
英文:
I am trying to Create a view where i need to put condition on year and I am trying below code but I get an error. Can someone please suggest if its possible . And the output which is required I have to use CTE only.
Error is as below : incorrect Syntax near ‘if’
Code is as below :
Declare @year varchar(20)
Set @year = ‘2022’;
With CTE as
(
If @year = ‘2022’
Begin
(
Select
@year ,
Prev_date = (select DATEADD(month, -1,GETDATE()))
)
End;
Else
Begin
(
Select @year,
Prev_date= (select DATEADD(month, -2,GETDATE()))
)
End;
)
Select *
From CTE
答案1
得分: 0
IF, BEGIN and END与过程代码相关，不能在这个上下文中使用。
没有进一步的规定，可以使用以下代码替换您的代码：
DECLARE @Year varchar(20);
SET @Year = '2022';
WITH CTE AS
(
SELECT Year = @year,
PrevDate = DATEADD(month, -1,GETDATE())
)
SELECT *
FROM CTE;
一条注释和一些建议：看起来（我错了吗？）您正在学习的初期。请尽量遵循SQL Server的编码准则。我已经进行了一些快速改进。可以参考示例链接：https://blog.sqlauthority.com/2008/09/25/sql-server-guidelines-and-coding-standards/
英文:
IF, BEGIN and END are related to the procedural code, and cannot be used in this context.
Your code, without further specification, can be replaced with the following:
DECLARE @Year varchar(20);
SET @Year = '2022';
WITH CTE AS
(
SELECT Year = @year,
PrevDate = DATEADD(month, -1,GETDATE())
)
SELECT *
FROM CTE;
A note and a piece of advice: it looks (am I wrong?) that you are at the beginning of the learning. Try to keep coding guidelines for SQL Server, please. I have made some quick improvements. An example link to follow: https://blog.sqlauthority.com/2008/09/25/sql-server-guidelines-and-coding-standards/
答案2
得分: 0
我不太确定您想要通过您的CTE实现什么，但是关于条件，您可以尝试将以下内容放入您的视图/查询/任何内容中：
声明 @year varchar(20)
设置 @year = '2022';
选择 [year] = @year, [prev_date] = DATEADD(month, -1 * CASE WHEN @year = '2022' THEN 1 ELSE 2 END, GETDATE())
英文:
I'm not entirely sure what you want to achieve with your cte, but concerning the condition, you can try to put the following into your view / query / whatever:
Declare @year varchar(20)
Set @year = '2022';
SELECT [year] = @year, [prev_date] = DATEADD(month, -1 * CASE WHEN @year = '2022' THEN 1 ELSE 2 END, GETDATE())
答案3
得分: 0
你可以使用简单有效的查询，而不是使用用于复杂查询的CTE。
Declare @year varchar(20) Set @year = '2023';
select @year [年份], prev_date = case when @year='2022' then (Select DATEADD(month, -1,GETDATE())) else DATEADD(month, -2,GETDATE()) end
英文:
You can use simple effective query rather than going for CTE which should be used for complex queries.
Declare @year varchar(20) Set @year = '2023';
select @year [Year], prev_date = case when @year='2022' then (Select DATEADD(month, -1,GETDATE())) else DATEADD(month, -2,GETDATE()) end
答案4
得分: -2
您没有提供错误信息以及您正在使用的数据库类型。
我猜您的SQL语句可能存在语法错误，请尝试以下修改：
Declare @year varchar(20)
Set @year = '2022'
With CTE as
(
If @year = '2022'
Begin
Select
@year ,
(select DATEADD(month, -1,GETDATE())) as previous_date
End
)
Select *
From CTE
在这种情况下，BEGIN ... END; 必须与 {..} 一起使用，以表示在其中有多个语句。希望这对您有所帮助。
英文:
You have not give the error and what database you are using.
I suppose you sql should be syntax error, please try this
Declare @year varchar(20)
Set @year = ‘2022’
With CTE as
(
If @year = ‘2022’
Begin
{
Select
@year ,
(select DATEADD(month, -1,GETDATE())) as previous_date
}
END;
)
Select *
From CTE
BEGIN ... END; must use with {..} in the case use have multiple statments in between.
Hope this help.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论