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

本文介绍了如何调用用户定义的函数以与 select、group by、order by 一起使用?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有 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