我怎么能不加入这些#temp(默认数据)和另一个表? [英] How can i left out join these #temp (default data) and another table?
本文介绍了我怎么能不加入这些#temp(默认数据)和另一个表?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我尝试使用查询(如下)生成一个表(查看表 1).
i try to generate a table (look TABLE 1) with the query(below).
CREATE TABLE #temp(
[VisitingCount] int,
[Time] nvarchar(50) )
DECLARE @DateNow DATETIME,@i int
SET @DateNow='00:00'
set @i=1;
while(@i<1440)
begin
set @DateNow=DATEADD(minute, 1, @DateNow)
insert into #temp ([VisitingCount], [Time]) values(0, right(left(convert(nvarchar, @DateNow, 121), 16), 5))
set @i=@i+1
end
select Count(VisitingCount) as VisitingCount,[Time]
from
#temp as Alltimes
left outer join
( SELECT Page,Date,[user],
dbo.fn_GetActivityLogArranger2(Date,'hour') as [Time]
FROM scr_SecuristLog
) scr_SecuristLog
on Alltimes.[Time] = scr_SecuristLog.[Time]
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
drop table #temp
我需要这个结果(如下).这是关于系统入口日志的代码,但是在12:30和13:30之间有一个休息午餐.所以在 12:30 和 13:30 之间禁止进入我们的系统.如果你绘制 log Graghic.由于午餐间隙,您可以看到M"字符
i need this result(below). This codes about System entrance logs, but there is a break lunch between 12:30 and 13:30. So No entrance our system between 12:30 and 13:30. if you draw log Graghic. You can see "M" character because of breaklunch gap
我的梦想表 1 结果:
My Dream TABLE 1 Result:
推荐答案
是你的where子句,过滤了一些时间范围.您需要为内部选择设置该子句:
It is your where clause, what filters some time ranges out. You need to set that clause for inner select:
select Count(VisitingCount) as VisitingCount,[Time]
from
#temp as Alltimes
left outer join
( SELECT Page,Date,[user],
dbo.fn_GetActivityLogArranger2(Date,'hour') 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)
)
) scr_SecuristLog
on Alltimes.[Time] = scr_SecuristLog.[Time]
group by [Time] order by [Time] asc
这篇关于我怎么能不加入这些#temp(默认数据)和另一个表?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文