SQL 如何调用用户定义的函数以与 select、group by、order by 一起使用？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/829089/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I call a User Defined Function to use with select, group by, order by?
提问by Penguen
I have Table1 and I need to get it to look like Table2:
我有 Table1，我需要让它看起来像 Table2：
Table1
表格1
VisitingCount | Date
-----------------------
1 | 15:09
3 | 15:10
7 | 15:15
1 | 15:39
2 | 15:40
3 | 15:47
Table2
表2
VisitingCount | Date
-----------------------------
11 | 15:00-15:30
6 | 15:30-16:00
I wrote a sql user-defined functions like this:
我写了一个像这样的sql用户定义函数：
create FUNCTION [dbo].[fn_GetActivityLogsArranger] (@time AS nvarchar(max))
RETURNS nvarchar(max)
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
When calling the UDF in my sql query, I achieve the correct result:
在我的 sql 查询中调用 UDF 时，我得到了正确的结果：
select
Count(Page) as VisitingCount,
dbo.fn_GetActivityLogsArranger(CONVERT(VARCHAR(5),Date, 108))
as [Time]
from
scr_SecuristLog
where
Date between '2009-04-30' and '2009-05-02' AND
[user] in
(
select USERNAME
from scr_CustomerAuthorities
where customerID = Convert(varchar,4) and ID = Convert(varchar,43)
)
group by
dbo.fn_GetActivityLogsArranger(CONVERT(VARCHAR(5),Date, 108))
order by
dbo.fn_GetActivityLogsArranger(CONVERT(VARCHAR(5),Date, 108)) asc
But I don't like this method; My dream code would look like this:
但我不喜欢这种方法；我梦想中的代码看起来像这样：
select
Count(Page) as VisitingCount,
dbo.fn_GetActivityLogsArranger(CONVERT(VARCHAR(5),Date, 108))
as [TIME]
from
scr_SecuristLog
where
Date between '2009-04-30' and '2009-05-02' and
user] in
(
select USERNAME
from scr_CustomerAuthorities
where customerID = Convert(varchar,4) and ID = Convert(varchar,43)
)
group by [TIME]
order by [TIME] asc
回答by Robin Day
You can join to your table like a view and have your function call there. That way you can call the group by and order by on the column from the view.
您可以像视图一样加入您的表，并在那里调用您的函数。这样您就可以从视图中对列调用 group by 和 order by。
select
Count(Page) as VisitingCount,
[Time]
from
(
SELECT
Page,
Date,
[user],
dbo.fn_GetActivityLogsArranger(CONVERT(VARCHAR(5),Date, 108)) as [Time]
FROM
scr_SecuristLog
) scr_SecuristLog2
where
Date between '2009-04-30' and '2009-05-02'
and
[user] in
(
select
USERNAME
from
scr_CustomerAuthorities
where
customerID=Convert(varchar,4)
and
ID=Convert(varchar,43)
)
group by
[Time]
order by
[Time] asc
