各位好!
现有一统计查询不知道如何书写,请各位帮忙解答,谢谢!
表T1 如下
ID Name CreateDate
1 A 2008-07-21
2 B 2008-07-22
3 C 2008-07-24
4 D 2008-07-24
5 E 2008-07-27
6 F 2008-07-28假如我需查询的时间区间为2008-07-22 到 2008-07-30
希望能查询到如下的结果:(即当天有数据的情况,统计数据条数,否则为0)Count CreateDate
1 2008-07-22
0 2008-07-23
2 2008-07-24
0 2008-07-25
0 2008-07-26
1 2008-07-27
1 2008-07-28
0 2008-07-29
0 2008-07-30
现在问题是,如果该天有数据则能统计,否则不显示条数。
请各位帮忙想个办法!要求只用SQL语句,不用存储过程,游标等
再次感谢
现有一统计查询不知道如何书写,请各位帮忙解答,谢谢!
表T1 如下
ID Name CreateDate
1 A 2008-07-21
2 B 2008-07-22
3 C 2008-07-24
4 D 2008-07-24
5 E 2008-07-27
6 F 2008-07-28假如我需查询的时间区间为2008-07-22 到 2008-07-30
希望能查询到如下的结果:(即当天有数据的情况,统计数据条数,否则为0)Count CreateDate
1 2008-07-22
0 2008-07-23
2 2008-07-24
0 2008-07-25
0 2008-07-26
1 2008-07-27
1 2008-07-28
0 2008-07-29
0 2008-07-30
现在问题是,如果该天有数据则能统计,否则不显示条数。
请各位帮忙想个办法!要求只用SQL语句,不用存储过程,游标等
再次感谢
(
select '2008-07-22' dt union
select '2008-07-23' dt union
select '2008-07-24' dt union
select '2008-07-25' dt union
select '2008-07-26' dt union
select '2008-07-27' dt union
select '2008-07-28' dt union
select '2008-07-29' dt union
select '2008-07-30' dt
) m
left join
(select CreateDate , count(*) cnt T1 from group by CreateDate) n
on m.dt = n.CreateDate
(
select '2008-07-22' dt union
select '2008-07-23' dt union
select '2008-07-24' dt union
select '2008-07-25' dt union
select '2008-07-26' dt union
select '2008-07-27' dt union
select '2008-07-28' dt union
select '2008-07-29' dt union
select '2008-07-30' dt
) m
left join
(select CreateDate , count(*) cnt from T1 group by CreateDate) n
on m.dt = n.CreateDate
declare @endTime datetimeset @startTime = '2008-07-22'
set @endTime = '2008-07-30'create table #day
(
testDate datetime
)while @startTime<=@endTime
begin
insert into #day values(@startTime)
set @startTime=Dateadd(day,1,@startTime)
endselect convert(nvarchar(20),testDate,112),Name
from #day
left outer join T1 on T1.CreateDate = convert(nvarchar(20),#day.testDate,112)
group by convert(nvarchar(20),#day.testDate,112)drop table #day
declare @endTime datetimeset @startTime = '2008-07-22'
set @endTime = '2008-07-30'create table #day
(
testDate datetime
)while @startTime<=@endTime
begin
insert into #day values(@startTime)
set @startTime=Dateadd(day,1,@startTime)
endselect convert(nvarchar(20),testDate,112),count(Name )
from #day
left outer join T1 on T1.CreateDate = convert(nvarchar(20),#day.testDate,112)
group by convert(nvarchar(20),#day.testDate,112)drop table #day
--------------------------
错了,没统计
drop table tb
go
create table tb (ID nvarchar(10),Name nvarchar(10),Create_date datetime)
insert tb select
'1', 'A', '2008-07-21' union all select
'2', 'B' , '2008-07-22' union all select
'3', 'C' , '2008-07-24' union all select
'4', 'D' , '2008-07-24' union all select
'5', 'E' , '2008-07-27' union all select
'6', 'F' , '2008-07-28'
if object_id('tday') is not null
drop table tday
go
declare @starttime datetime
declare @endtime datetime
set @startTime = '2008-07-22'
set @endTime = '2008-07-30'
create table tday
(
testDate datetime
)
while @startTime <=@endTime
begin
insert into tday values(@startTime)
set @startTime=Dateadd(day,1,@startTime)
end select a.testdate,isnull(b.cnt,0) from tday a left join (select create_date ,count(*) cnt from tb group by create_date)b on a.testdate=b.create_date