数据表:
收款 时间
20 2006-11-25 21:06:42
20 2006-11-27 13:17:46
300 2006-11-28 4:42:46
50 2006-11-28 9:08:29
70 2006-12-1 9:28:55
20 2006-12-1 9:33:33
25 2006-12-28 9:44:08如何建立一个查询,列出一个时间段内(比如从2006-11-1 到2007-1-1)的所有日期,并显示对应日期的营业额(收款总和)?注意:其中某些天可能不营业,另最好能用于access下即得到如下形式日期 营业额
2006-11-1 300
2006-11-2 --
2006-11-3 500
...
2006-12-12 800
.....
收款 时间
20 2006-11-25 21:06:42
20 2006-11-27 13:17:46
300 2006-11-28 4:42:46
50 2006-11-28 9:08:29
70 2006-12-1 9:28:55
20 2006-12-1 9:33:33
25 2006-12-28 9:44:08如何建立一个查询,列出一个时间段内(比如从2006-11-1 到2007-1-1)的所有日期,并显示对应日期的营业额(收款总和)?注意:其中某些天可能不营业,另最好能用于access下即得到如下形式日期 营业额
2006-11-1 300
2006-11-2 --
2006-11-3 500
...
2006-12-12 800
.....
insert into @t select 20 ,'2006-11-25 21:06:42'
insert into @t select 20 ,'2006-11-27 13:17:46'
insert into @t select 300,'2006-11-28 04:42:46'
insert into @t select 50 ,'2006-11-28 09:08:29'
insert into @t select 70 ,'2006-12-01 09:28:55'
insert into @t select 20 ,'2006-12-01 09:33:33'
insert into @t select 25 ,'2006-12-28 09:44:08'set rowcount 100
select identity(int,0,1) as id,getdate() as date into # from sysobjects
set rowcount 0update # set date=dateadd(day,-id,date)select
a.date,sum(b.收款)
from
# a
left join
@t b
on
datediff(day,a.date,b.时间)=0
where
a.date between '2006-11-01' and '2006-12-21'
group by a.datedrop table #
insert into @t select 20 ,'2006-11-25 21:06:42'
insert into @t select 20 ,'2006-11-27 13:17:46'
insert into @t select 300,'2006-11-28 04:42:46'
insert into @t select 50 ,'2006-11-28 09:08:29'
insert into @t select 70 ,'2006-12-01 09:28:55'
insert into @t select 20 ,'2006-12-01 09:33:33'
insert into @t select 25 ,'2006-12-28 09:44:08'
declare @myint int
declare @d table(日期 smalldatetime)
set @myint=datediff(day,'2006-11-01' ,'2006-12-21')
while (@myint>=0)
begin
insert into @d select dateadd(day,-@myint,'2006-12-21')
set @myint=@myint-1
endSELECT isnull(sum(收款),0) AS 收款 ,CONVERT(VARCHAR(30),日期,111) AS 日期 FROM @d
left join @t
on datediff(day,时间 ,日期)=0
Group by 日期
insert into @t select 20 ,'2006-11-25 21:06:42'
insert into @t select 20 ,'2006-11-27 13:17:46'
insert into @t select 300,'2006-11-28 04:42:46'
insert into @t select 50 ,'2006-11-28 09:08:29'
insert into @t select 70 ,'2006-12-01 09:28:55'
insert into @t select 20 ,'2006-12-01 09:33:33'
insert into @t select 25 ,'2006-12-28 09:44:08'
select sum(收款),convert(varchar(10),时间,120)
from @t
group by convert(varchar(10), 时间,120)