gpt4 book ai didi

sql - 标量函数中的 CTE

转载 作者:行者123 更新时间:2023-12-04 02:19:17 30 4
gpt4 key购买 nike

我可以在标量函数中使用公用表表达式(CTE) 吗?

我试图用它来获取单个浮点值,但始终为空

这是我计算每位员工总工作时间的函数代码:

ALTER FUNCTION GetTotalWorkingHour 
(
@StartDate datetime,
@EndDate datetime,
@EmpID nvarchar(6) = null
)
RETURNS float
AS
BEGIN

DECLARE @Result float;
WITH
CTE_Start
AS
(
SELECT EmpID ,SUM(DATEDIFF(minute, (CAST(att.[date] AS datetime) + att.[Time]), @StartDate) *
CASE WHEN Funckey = 'EMPIN' THEN +1 ELSE -1 END) AS SumStart
FROM PERS_Attendance AS att
WHERE (EmpID = @EmpID OR @EmpID IS NULL) AND att.[date] < @StartDate GROUP BY EmpID
)
,CTE_End
AS
(
SELECT EmpID ,SUM(DATEDIFF(minute, (CAST(att.[date] AS datetime) + att.[Time]), @EndDate) * CASE WHEN Funckey = 'EMPIN' THEN +1 ELSE -1 END) AS SumEnd
FROM PERS_Attendance AS att
WHERE (EmpID = @EmpID OR @EmpID IS NULL) AND att.[date] < @EndDate GROUP BY EmpID
)

SELECT @Result =
(CTE_Start.SumStart - ISNULL(CTE_End.SumEND, 0) / 60.0) --AS SumHours
FROM
CTE_End
LEFT JOIN CTE_Start ON CTE_Start.EmpID = CTE_End.EmpID
RETURN @Result

END
GO

上面的代码以正确的方式运行,如果我在单个查询中使用它(不在函数中),会给我预期的结果,那么有什么问题呢?

最佳答案

我找到了解决方案,这是一个小错误,我必须添加:

SELECT @Result = 
(SumEnd - ISNULL(SumStart, 0)) / 60.0 --AS SumHours
FROM
CTE_End
LEFT JOIN CTE_Start ON CTE_Start.EmpID = CTE_End.EmpID
RETURN @Result

代替:
SELECT @Result = 
(CTE_Start.SumStart - ISNULL(CTE_End.SumEND, 0) / 60.0) --AS SumHours
FROM
CTE_End
LEFT JOIN CTE_Start ON CTE_Start.EmpID = CTE_End.EmpID
RETURN @Result

关于sql - 标量函数中的 CTE,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/32140722/

30 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com