如果在SQL中使用公共表达式(CTE)的条件

huangapple go评论102阅读模式
英文:

If condition with CTE in sql

问题

以下是已翻译的代码部分:

  1. 我正在尝试创建一个视图,在该视图中,我需要对年份进行条件筛选。我正在尝试以下代码,但是我遇到了错误。请问是否可能实现这个要求?而且要求的输出必须仅使用CTE
  2. 错误信息如下:'if'附近的语法不正确。
  3. 以下是代码:
  4. Declare @year varchar(20)
  5. Set @year = '2022';
  6. With CTE as
  7. (
  8. If @year = '2022'
  9. Begin
  10. (
  11. Select
  12. @year ,
  13. Prev_date = (select DATEADD(month, -1,GETDATE()))
  14. )
  15. End;
  16. Else
  17. Begin
  18. (
  19. Select @year,
  20. Prev_date= (select DATEADD(month, -2,GETDATE()))
  21. )
  22. End;
  23. )
  24. Select *
  25. 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 :

  1. Declare @year varchar(20)
  2. Set @year = 2022’;
  3. With CTE as
  4. (
  5. If @year = 2022
  6. Begin
  7. (
  8. Select
  9. @year ,
  10. Prev_date = (select DATEADD(month, -1,GETDATE()))
  11. )
  12. End;
  13. Else
  14. Begin
  15. (
  16. Select @year,
  17. Prev_date= (select DATEADD(month, -2,GETDATE()))
  18. )
  19. End;
  20. )
  21. Select *
  22. From CTE

答案1

得分: 0

IF, BEGIN and END与过程代码相关,不能在这个上下文中使用。

没有进一步的规定,可以使用以下代码替换您的代码:

  1. DECLARE @Year varchar(20);
  2. SET @Year = '2022';
  3. WITH CTE AS
  4. (
  5. SELECT Year = @year,
  6. PrevDate = DATEADD(month, -1,GETDATE())
  7. )
  8. SELECT *
  9. 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:

  1. DECLARE @Year varchar(20);
  2. SET @Year = '2022';
  3. WITH CTE AS
  4. (
  5. SELECT Year = @year,
  6. PrevDate = DATEADD(month, -1,GETDATE())
  7. )
  8. SELECT *
  9. 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实现什么,但是关于条件,您可以尝试将以下内容放入您的视图/查询/任何内容中:

  1. 声明 @year varchar(20)
  2. 设置 @year = '2022';
  3. 选择 [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:

  1. Declare @year varchar(20)
  2. Set @year = '2022';
  3. 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.

  1. Declare @year varchar(20) Set @year = '2023';
  2. select @year [Year], prev_date = case when @year='2022' then (Select DATEADD(month, -1,GETDATE())) else DATEADD(month, -2,GETDATE()) end

答案4

得分: -2

您没有提供错误信息以及您正在使用的数据库类型。
我猜您的SQL语句可能存在语法错误,请尝试以下修改:

  1. Declare @year varchar(20)
  2. Set @year = '2022'
  3. With CTE as
  4. (
  5. If @year = '2022'
  6. Begin
  7. Select
  8. @year ,
  9. (select DATEADD(month, -1,GETDATE())) as previous_date
  10. End
  11. )
  12. Select *
  13. 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

  1. Declare @year varchar(20)
  2. Set @year = 2022
  3. With CTE as
  4. (
  5. If @year = 2022
  6. Begin
  7. {
  8. Select
  9. @year ,
  10. (select DATEADD(month, -1,GETDATE())) as previous_date
  11. }
  12. END;
  13. )
  14. Select *
  15. From CTE

BEGIN ... END; must use with {..} in the case use have multiple statments in between.
Hope this help.

huangapple
  • 本文由 发表于 2023年1月9日 18:43:56
  • 转载请务必保留本文链接:https://go.coder-hub.com/75056113.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定