如何在SQL中使用“when - between”语句？

Question

这个查询在when-between语句中给我带来了语法错误。我该怎么解决？

alter FUNCTION [dbo].[fn_GetActivityLogsArranger]
(
@time AS nvarchar(max)
)

RETURNS  nvarchar(max)
AS
BEGIN
declare @Return varchar(30)

select @Return = case @time
when between '15:00' and '15:30' then '15:00-15:30'
when between '15:30' and '16:00' then '15:30-16:00'
when between '16:00' and '16:30' then '16:00-16:30'
when between '16:00' and '16:30' then '16:00-16:30' 
when between '16:30' and '17:00' then '16:30-17:00' 
when between '17:00' and '17:30' then '17:00-17:30' 
when between '17:30' and '18:00' then '17:30-18:00'
else 'Unknown'
 Return @Return
end

- Penguen

Answer 1

alter FUNCTION [dbo].[fn_GetActivityLogsArranger]
(
    @time AS varchar(30)
)
RETURNS  
varchar(30)AS
BEGIN
declare @Return varchar(30)
select @Return = case 
when @time between '15:00' and '15:30' then '15:00-15:30'
when @time between '15:30' and '16:00' then '15:30-16:00'
when @time between '16:00' and '16:30' then '16:00-16:30'
when @time between '16:00' and '16:30' then '16:00-16:30' 
when @time between '16:30' and '17:00' then '16:30-17:00' 
when @time between '17:00' and '17:30' then '17:00-17:30'
when @time between '17:30' and '18:00' then '17:30-18:00'
else 'Unknown' 
end
Return @Return
end

- Mitch Wheat

---

非常感谢。你是正确的。请看另一个问题。https://dev59.com/mnRA5IYBdhLWcg3wyBD1 - Penguen

Answer 2

你不能使用那种格式的case语法。你需要编写一个带有检查功能的case：

select @Return = case 
when @time between '15:00' and '15:30' then '15:00-15:30'
when @time between '15:30' and '16:00' then '15:30-16:00'
when @time between '16:00' and '16:30' then '16:00-16:30'
when @time between '16:00' and '16:30' then '16:00-16:30' 
when @time between '16:30' and '17:00' then '16:30-17:00' 
when @time between '17:00' and '17:30' then '17:00-17:30' 
when @time between '17:30' and '18:00' then '17:30-18:00'
else 'Unknown' END

Return @Return

此外，您在case语句的末尾缺少一个END（请参见上面大写的END）。

- Jonathan

Answer 3

语法 CASE: CASE WHEN boolean 表达式 THEN 结果表达式 [ ...n ] [ ELSE 其他结果表达式 ] END

- SQLMaster

Answer 4

首先，你需要在每个 when 语句中传递你的 @variable 变量。

select @Return = case 
when @time between ('15:00' and '15:30') then '15:00-15:30'
when @time between ('15:30' and '16:00') then '15:30-16:00'
when @time between ('16:00' and '16:30') then '16:00-16:30'
when @time between ('16:00' and '16:30') then '16:00-16:30' 
when @time between ('16:30' and '17:00') then '16:30-17:00' 
when @time between ('17:00' and '17:30') then '17:00-17:30' 
when @time between ('17:30' and '18:00') then '17:30-18:00'
else 'Unknown'

- Eoin Campbell

Answer 5

你需要在每个 WHEN 子句中都有变量，例如：

case 
    when @time between '15:00' and '15:30' then '15:00-15:30'
    when @time between '15:30' and '16:00' then '15:30-16:00'

- Robin Day

Answer 6

你不应该使用这个函数，而应该有一个时间段的表格或表值函数，这样你就可以对它进行纯连接。关于示例，请参见我的另一篇文章。连接操作将比在行集上调用函数的方法要快得多。

- ahains