本文介绍了如何调用用户定义的函数以与 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更多推荐
如何调用用户定义的函数以与 select、group by、order by 一起使用?
发布评论