您的位置: 首页  >  技术问答  >  如何调用用户定义的函数以与 select、group by、order by 一起使用?

如何调用用户定义的函数以与 select、group by、order by 一起使用?

分类: 技术问答 • 2022-06-09 23:05:18
问题描述：

我有 Table1，我需要让它看起来像 Table2:

I have Table1 and I need to get it to look like Table2:

VisitingCount |  Date
-----------------------
      1       |  15:09
      3       |  15:10
      7       |  15:15
      1       |  15:39
      2       |  15:40
      3       |  15:47

表 2

VisitingCount |  Date
-----------------------------
     11       |  15:00-15:30
     6        |  15:30-16:00

我写了一个sql用户定义函数是这样的:

I wrote a sql user-defined functions like this:

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

在我的sql查询中调用UDF时，得到了正确的结果:

When calling the UDF in my sql query, I achieve the correct result:

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

答

您可以像视图一样加入您的表，并在那里调用您的函数.这样您就可以从视图中对列调用 group by 和 order by.

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.

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

相关推荐