如何调用用户定义函数以与select,group by,order by一起使用?
发布于 2021-05-10 20:44:28
我有Table1,我需要让它看起来像Table2:
表格1
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用户定义函数:
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时,我获得了正确的结果:
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
但是我不喜欢这种方法。我梦dream以求的代码如下所示:
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
关注者
0
被浏览
48
1 个回答
-
您可以像视图一样加入表并在其中调用函数。这样,您可以在视图的列上调用分组依据和排序依据。
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
推荐阅读
-
一起使用ORDER BY和GROUP BY
2021-02-02 关注 0 浏览105 1答案
-
如何在PostgreSQL中将SELECT DISTINCT与RANDOM()函数一起使用?
2021-05-10 关注 0 浏览81 1答案
-
Swift /如何将dispatch_group与多个调用的Web服务一起使用?
2021-01-31 关注 0 浏览78 1答案
-
如何通过Ajax请求将Select2与JSON一起使用?
2021-01-31 关注 0 浏览88 1答案
-
JPA JPQL:使用COUNT,GROUP BY和ORDER BY选择SELECT NEW
2021-01-29 关注 0 浏览161 1答案
-
将curl POST与bash脚本函数中定义的变量一起使用
2021-01-31 关注 0 浏览118 1答案
-
将 curl POST 与 bash 脚本函数中定义的变量一起使用
2022-07-28 关注 0 浏览14 1答案
-
如何加载JSON数据以将其与select2插件一起使用
2021-01-31 关注 0 浏览81 1答案
-
是否可以将GROUP BY与绑定变量一起使用?
2021-05-20 关注 0 浏览109 1答案
-
在C中将stdin与select()一起使用
2021-02-01 关注 0 浏览93 1答案