使用Case语句时出现语法错误(语句前有Between)

Question

我在Select statement 的where中使用CASE语句

SELECT * 
FROM [4c1917a7-2bab-4dbe-bb22-812a25a8930f] AS TP 
INNER JOIN TSaisie AS TS ON TP.[TInd_FK_ID] = TS.[TSai_FK_TInd_ID]
WHERE 
TS.[TSai_Date] BETWEEN 
CASE WHEN (SELECT SeasonDate2 FROM fn_GetSeasonDates(@Datetest)) < (SELECT SeasonDate1 FROM fn_GetSeasonDates(@Datetest))
THEN
(SELECT SeasonDate2 FROM fn_GetSeasonDates(@Datetest)) AND (SELECT SeasonDate1 FROM fn_GetSeasonDates(@Datetest))
ELSE
(SELECT SeasonDate1 FROM fn_GetSeasonDates(@Datetest)) AND (SELECT SeasonDate2 FROM fn_GetSeasonDates(@Datetest))
END
AND [TSai_FK_TPro_ID] = 354
GROUP BY TInd_BagueID, TP.[DateSaisie]

这在关键字"AND"one_answers"ELSE"附近引发语法错误

有人能告诉我这个代码出了什么问题吗？

Answer 1

这里遗漏了AND关键字的位置。它应该是类似的东西

SELECT * 
FROM [4c1917a7-2bab-4dbe-bb22-812a25a8930f] AS TP 
INNER JOIN TSaisie AS TS ON TP.[TInd_FK_ID] = TS.[TSai_FK_TInd_ID]
WHERE 
TS.[TSai_Date] BETWEEN 
CASE WHEN (SELECT SeasonDate2 FROM fn_GetSeasonDates(@Datetest)) < (SELECT SeasonDate1 FROM fn_GetSeasonDates(@Datetest))
THEN
(SELECT SeasonDate2 FROM fn_GetSeasonDates(@Datetest)) 
ELSE
(SELECT SeasonDate1 FROM fn_GetSeasonDates(@Datetest))
END
AND 
CASE WHEN (SELECT SeasonDate2 FROM fn_GetSeasonDates(@Datetest)) < (SELECT SeasonDate1 FROM fn_GetSeasonDates(@Datetest))
THEN
(SELECT SeasonDate1 FROM fn_GetSeasonDates(@Datetest)) 
ELSE
(SELECT SeasonDate2 FROM fn_GetSeasonDates(@Datetest)) 
END
AND [TSai_FK_TPro_ID] = 354
GROUP BY TInd_BagueID, TP.[DateSaisie]

但我真的建议先使用一些局部变量来获取日期，然后在查询中使用它。比如

DECLARE @SeasonDate1 date, @SeasonDate2 date
SELECT @SeasonDate1 = SeasonDate1,
@SeasonDate2 = SeasonDate1
FROM fn_GetSeasonDates(@Datetest)
SELECT *
FROM Sales.SalesOrderDetail
WHERE ModifiedDate 
BETWEEN CASE
WHEN @SeasonDate1 < @SeasonDate2 THEN @SeasonDate1
ELSE @SeasonDate2
END AND CASE
WHEN @SeasonDate1 < @SeasonDate2 THEN @SeasonDate2
ELSE @SeasonDate1
END

Answer 2

它目前没有意义。在THEN和ELSE子句中，有两个表达式，它们之间有一个and。

我怀疑AND之后的SELECT子句以及AND本身不应该存在。THEN和ELSE之后的表达式应该只计算为一个值。

我认为你的CASE声明应该是：

CASE
WHEN
(SELECT SeasonDate2
FROM fn_GetSeasonDates(@Datetest)) <
(SELECT SeasonDate1
FROM fn_GetSeasonDates(@Datetest)) THEN
(SELECT SeasonDate2
FROM fn_GetSeasonDates(@Datetest))
ELSE
(SELECT SeasonDate1
FROM fn_GetSeasonDates(@Datetest))
END