数据表如下
想得到上个月每天的数据量和的走向timestamp/datetime data
2008-01-01 16:30 123
2008-01-02 17:30 123
2008-01-04 6:30 123
2008-01-04 6:30 123
如果使用
select date(datetime) as date,sum(data) as count
from tab_log
where year(datetime)=year(current timestamp - 1 month) and month(datetime)=month(current timestamp - 1 months)
Group by date(datetime)
order by date结果
date count
2008-01-01 123
2008-01-02 123
2008-01-04 246这样得到结果格式是正确的,
但是如果某天没有记录,那么这一天在结果里就没有了.2008-01-03
我想让这样的没有记录的天显示为0date count
2008-01-01 123
2008-01-02 123
2008-01-03 0
2008-01-04 246
想得到上个月每天的数据量和的走向timestamp/datetime data
2008-01-01 16:30 123
2008-01-02 17:30 123
2008-01-04 6:30 123
2008-01-04 6:30 123
如果使用
select date(datetime) as date,sum(data) as count
from tab_log
where year(datetime)=year(current timestamp - 1 month) and month(datetime)=month(current timestamp - 1 months)
Group by date(datetime)
order by date结果
date count
2008-01-01 123
2008-01-02 123
2008-01-04 246这样得到结果格式是正确的,
但是如果某天没有记录,那么这一天在结果里就没有了.2008-01-03
我想让这样的没有记录的天显示为0date count
2008-01-01 123
2008-01-02 123
2008-01-03 0
2008-01-04 246
from tab_log
where year(datetime)=year(current timestamp - 1 month) and month(datetime)=month(current timestamp - 1 months)
Group by date(datetime)
order by date
go--timestamp/datetime data insert tab_log select '2008-01-01 16:30',123
insert tab_log select '2008-01-02 17:30',123
insert tab_log select '2008-01-04 6:30',123
insert tab_log select '2008-01-04 6:30',123
goselect [timestamp/datetime],sum(data)
from tab_log group by [timestamp/datetime]drop table tab_log
insert into tt select '2008-01-01 16:30',123
insert into tt select '2008-01-02 17:30',123
insert into tt select '2008-01-04 6:30',123
insert into tt select '2008-01-04 6:30',123declare @dt datetime
set @dt='2008-01-01'
select top 365 id=identity(int,1,1),date=@dt into # from sysobjects a,sysobjects bupdate # set date=dateadd(dd,id-1,date)select convert(varchar(10),a.date,120) as '日期',sum(isnull(data,0)) as '总和'
from # a left join tt b
on convert(varchar(10),a.date,120)=convert(varchar(10),b.date,120)
where a.date between '2008-01-01 00:00:00.000' and '2008-01-31 00:00:00.000'
group by convert(varchar(10),a.date,120)
日期 总和
2008-01-01 123
2008-01-02 123
2008-01-03 0
2008-01-04 246
2008-01-05 0
2008-01-06 0
2008-01-07 0
2008-01-08 0
2008-01-09 0
2008-01-10 0
2008-01-11 0
2008-01-12 0
2008-01-13 0
2008-01-14 0
2008-01-15 0
2008-01-16 0
2008-01-17 0
2008-01-18 0
2008-01-19 0
2008-01-20 0
2008-01-21 0
2008-01-22 0
2008-01-23 0
2008-01-24 0
2008-01-25 0
2008-01-26 0
2008-01-27 0
2008-01-28 0
2008-01-29 0
2008-01-30 0
2008-01-31 0
我这里是DB2,还没有isNull
但是有COALESCE,正在研究。
因为DB2那里没有人气,所以转过来的。回头我会在转过去。
谢谢。
能否受累解释一下?
(select '2008-01-01' rq union '2008-01-02' ... '2008-01-31') t1
left join
(你的查询) t2
on t1.rq = t2.rq
create table tt(date datetime,data int)
insert into tt select '2008-01-01 16:30',123
insert into tt select '2008-01-02 17:30',123
insert into tt select '2008-01-04 6:30',123
insert into tt select '2008-01-04 6:30',123
create proc ooo
@year int,
@month int --生成某月的数据
as
if @month<1 or @month >12
begin
raiserror('输入月份无效',16,1)
return
end
else
begin
create table #temp1(date datetime)
declare @count int--得出该月有多少天
declare @num int
set @num=1
set @count=datediff(dd,convert(varchar(10),cast(@year as varchar)+'-'+cast(@month as varchar)+'-01',120),convert(varchar(10),cast(@year as varchar)+'-'+cast((@month+1) as varchar)+'-01',120))
while(@num<=@count)
begin
insert into #temp1 select convert(varchar(10),cast(@year as varchar)+'-'+cast(@month as varchar)+'-'+cast(@num as varchar),120)
set @num=@num+1
end
select a.date,COALESCE(sum(b.data),0)[count ] from #temp1 a left join tt b
on convert(varchar(10),b.date,120)=a.date group by a.date order by a.date
end
exec ooo 2008,1
再麻烦各位看看有没有问题.不过还要把这个翻译到DB2里面,又头疼了.....
create table #t1(date varchar(8))
declare @days int --得出上个月有多少天
declare @num int --循环用
set @num=0
set @days=day(GETDATE()-day(getdate()))declare @start datetime --得到上个月的第一天如1号
declare @end datetime --得到上个月的最后一天如31号
set @start=dateadd(dd,1-day(getdate()-day(getdate())),getdate()-day(getdate()))
set @end=GETDATE()-day(getdate())while(@num<@days) --把上个月的日期依次写入临时表
begin
insert into #t1 select convert(varchar(8),dateadd(dd,@num,@start),112)
set @num=@num+1
end
select a.date,coalesce(sum(b.accesscount),0)[count] from #t1 a left join cctiwvisit b
on convert(varchar(8),b.accessdate,112)=a.date --计算结果,不过[count]我还不明白什么意思
group by a.date
order by a.date
结果
20071213 0
20071214 674
20071215 71
20071216 0
20071217 486
20071218 617
20071219 532
20071220 589
20071221 626
20071222 268
20071223 170
20071224 545
20071225 383
20071226 384
20071227 668
20071228 635
20071229 313
20071230 0
20071231 0
为什么我试了你的代码后,#temp1一直都在tempdb中阿.