如何评估查询中的多次数据? [英] How can evaluate more than once data from a query?
本文介绍了如何评估查询中的多次数据?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
CREATE TABLE #Temp (VisitingCount int, [Time] int )
DECLARE @DateNow DATETIME,@i int,@Time int
set @DateNow='00:00'
set @i=1;
while(@i<48)
begin
set @DateNow = DATEADD(minute, 30, @DateNow)
set @Time = (datepart(hour,@DateNow)*60+datepart(minute,@DateNow))/30
insert into #Temp(VisitingCount,[Time]) values(0,@Time )
set @i=@i+1
end
select Sum(VisitingCount)as VisitingCount, [Time]
from #Temp group by [Time]
Union All
select count(page) as VisitingCount,
(datepart(hour,Date)*60+datepart(minute,Date))/30 as [Time]
from scr_SecuristLog
where Date between '2009-05-04 10:30' and '2009-05-04 12:30'
GROUP BY (datepart(hour,Date)*60+datepart(minute,Date))/30--scr
order by 2 asc
这个查询为我创建了这个表,但如果你看下面的22"重复两次.在这种情况下.我应该降低 0 值.我需要
This query creates me this table but if you look below "22" is repeating two times. in this situation. i should drop 0 value. i need
我需要这张桌子
推荐答案
再创建一层嵌套:
select Sum(VisitingCount)as VisitingCount, [Time]
from (
select Sum(VisitingCount)as VisitingCount, [Time]
from #Temp group by [Time]
Union All
select count(page) as VisitingCount,
(datepart(hour,Date)*60+datepart(minute,Date))/30 as [Time]
from scr_SecuristLog
where Date between '2009-05-04 10:30' and '2009-05-04 12:30'
GROUP BY (datepart(hour,Date)*60+datepart(minute,Date))/30--scr
) X
group by [Time]
order by 2 asc
这篇关于如何评估查询中的多次数据?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文