在 sql server 子查询中使用 if else

using if else in sql server subquery

提问by DaneEdw

I'm not sure why the following subquery doesn't work in SQL Server, when I run the subquery by itself it works just fine, but as soon as I add

我不确定为什么下面的子查询在 SQL Server 中不起作用，当我单独运行子查询时它工作得很好，但是只要我添加

Select * from ([subquery]) as table1

the query doesn't run, SQL Server returns an error saying I have incorrect syntax near the keyword 'if', what syntax error exists here and how come it will work just fine otherwise?

查询没有运行，SQL Server 返回一个错误，说我在关键字“if”附近有不正确的语法，这里存在什么语法错误，否则它怎么会正常工作？

Any pointers?

任何指针？

select * from (

    if datepart(MONTH, getdate()) >= MONTH('1/February/0000') 
    --after february we want the next years semesters, 

        begin
        select top 3 sem_name from semester_dates
         where datepart(year, start_date) < datepart(YEAR, getdate()) and sem_name not like 'summer%'
        end

    else 
        begin
        select top 3 sem_name from semester_dates  sd
        where datepart(year, start_date) >= datepart(YEAR, getdate()) and sem_name not like 'summer%' 
        end

    ) table1

回答by BStateham

You may want to consider moving your if/else logic into a table valued function. Something like this perhaps. Note, I didn't check your logic, I just re-used this. You may need to adjust the code below accordingly:

您可能需要考虑将 if/else 逻辑移动到表值函数中。也许是这样的。注意，我没有检查你的逻辑，我只是​​重新使用了这个。您可能需要相应地调整以下代码：

CREATE FUNCTION SemesterNames
(
  @ReferenceDate datetime
)
RETURNS @dates table 
(
  sem_name nvarchar(50) --check the data type
)
AS
BEGIN
  if datepart(MONTH, @ReferenceDate) >= MONTH('1/February/0000') 
    --after february we want the next years semesters, 
    INSERT INTO @dates (sem_name)
    select top 3 sem_name from semester_dates
     where datepart(year, start_date) > datepart(YEAR, @ReferenceDate) and sem_name not like 'summer%'
    end
else 
    INSERT INTO @dates (sem_name)
    select top 3 sem_name from semester_dates  sd
    where datepart(year, start_date) >= datepart(YEAR, @ReferenceDate) and sem_name not like 'summer%' 
END;
GO

SELECT * FROM SemesterNames(GETDATE());

回答by BStateham

Try the following using CASE, instead of IF.

使用 CASE 而不是 IF 尝试以下操作。

SELECT *
FROM
(SELECT 
    CASE 
        WHEN datepart(MONTH, getdate()) >= MONTH('1/February/0000')
            THEN
                (SELECT TOP 3 sem_name 
                 FROM semester_dates
                 WHERE datepart(year, start_date) > datepart(YEAR, getdate()) and sem_name not like 'summer%')
        ELSE
                (SELECT TOP sem_name 
                FROM semester_dates
                WHERE datepart(year, start_date) >= datepart(YEAR, getdate()) and sem_name not like 'summer%')
)

回答by Lord Peter

As far as I can see the only effect of your conditional logic is that datepart(year, start_date) is strictly greater than, or greater than or equal to. So you could replace the whole thing with just a simple query like this:

据我所知，您的条件逻辑的唯一效果是 datepart(year, start_date) 严格大于或大于或等于。所以你可以用一个简单的查询来替换整个事情：

    select top 3 sem_name from semester_dates
    where datepart(year, start_date) >= datepart(YEAR, getdate()) 
    and sem_name not like 'summer%'
    and (
         datepart(MONTH, getdate()) > MONTH('1/February/0000') or 
         datepart(year, start_date) = datepart(YEAR, getdate()
    ) 

In other words, the year of start_date is strictly > year(getdate()) unless, if the month is > Feb, then the year can equal it.

换句话说， start_date 的年份严格来说是 > year(getdate()) 除非，如果月份 > Feb，那么年份可以等于它。

回答by juni

select * from
(select datepart(MONTH, getdate()) >= MONTH('1/February/0000'))
    as department,
(select top 3 sem_name from semester_dates
    where datepart(year, start_date) < datepart(YEAR, getdate()) 
        and sem_name not like 'summer%') 
    as sem_name,
(select top 3 sem_name from semester_dates sd
    where datepart(year, start_date) >= datepart(YEAR, getdate())
        and sem_name not like 'summer%') 
    as top 3 sem_name 
) table

回答by Tim S

For this particular query, could you not acheive the desired result using a single SELECT with the February condition in your WHERE clause?

对于这个特定的查询，您能否在 WHERE 子句中使用带有二月条件的单个 SELECT 来获得所需的结果？

SELECT TOP 3
    Sem_Name
FROM
    semester_dates
WHERE
    sem_name not like 'summer%'
    and 
    (
        (datepart(month, getdate()) >= 2 AND datepart(year, start_date) < datepart(YEAR, getdate()))
        OR
        (datepart(month, getdate()) < 2 AND datepart(year, start_date) >= datepart(YEAR, getdate()))
    )

回答by csg

If/else constructions are not supposed to work into a Select query.

If/else 结构不应该用于 Select 查询。