关于group by 统计结果显示问题

[我没有吃饭]

--SQL2000，测试数据如下
create table ta(dtime datetime, con int)
insert into ta(dtime,con)
select '2011-06-01',10 union all
select '2011-06-01',8 union all
select '2011-06-05',11 union all
select '2011-06-10',20

--以下查询不是我想要的结果
select dtime,sum(con) amount from ta
group by dtime
order by dtime
--在这个查询中，其它日期虽然没有数据，但是我也想把它们显示出来，请问该如何写

在这个查询中，其它日期虽然没有数据，但是我也想把它们显示出来，请问该如何写

[kevn]

想要的结果是什么

[AcHerat]

--SQL2000，测试数据如下
create table ta(dtime datetime, con int)
insert into ta(dtime,con)
select '2011-06-01',10 union all
select '2011-06-01',8 union all
select '2011-06-05',11 union all
select '2011-06-10',20

--以下查询不是我想要的结果
--select dtime,sum(con) amount from ta
--group by dtime
--order by dtime
--在这个查询中，其它日期虽然没有数据，但是我也想把它们显示出来，请问该如何写

declare @start datetime
declare @end datetime
select @start = min(dtime) from ta
select @end = max(dtime) from ta

select convert(varchar(10),a.dtime,120) as dtime,sum(isnull(b.con,0)) as con
from (select dateadd(dd,number,@start) as dtime
	from master..spt_values
	where type = 'p' and number between 0 and datediff(dd,@start,@end)) a
	left join ta b on convert(varchar(10),a.dtime,120) = convert(varchar(10),b.dtime,120)
group by convert(varchar(10),a.dtime,120)
order by convert(varchar(10),a.dtime,120)

drop table ta


/********************

dtime      con
---------- -----------
2011-06-01 18
2011-06-02 0
2011-06-03 0
2011-06-04 0
2011-06-05 11
2011-06-06 0
2011-06-07 0
2011-06-08 0
2011-06-09 0
2011-06-10 20

(10 行受影响)

[daifei4451]

[Quote=引用 2 楼 acherat 的回复:]
SQL code


--SQL2000，测试数据如下
create table ta(dtime datetime, con int)
insert into ta(dtime,con)
select '2011-06-01',10 union all
select '2011-06-01',8 union all
select '2011-06-05',11 union all……
[/Quote]

学习了

[cd731107]

[Quote=引用楼主 getdate123 的回复:]
SQL code

--SQL2000，测试数据如下
create table ta(dtime datetime, con int)
insert into ta(dtime,con)
select '2011-06-01',10 union all
select '2011-06-01',8 union all
select '2011-06-05',11 union all
……
[/Quote]
其它日期是指６月份所有日期，还是１号到１０号的日期

[cd731107]

--如果是指6月１日到６月３０日，则如下
create table ta(dtime datetime, con int)
insert into ta(dtime,con)
select '2011-06-01',10 union all
select '2011-06-01',8 union all
select '2011-06-05',11 union all
select '2011-06-10',20

declare @start datetime
declare @end datetime
select @start = dateadd(mm,datediff(mm,0,min(dtime)),0) from ta 
select @end =dateadd(ms,-2,DATEADD(mm,  DATEDIFF(m,0, max(dtime))+1,  0))  from ta

select convert(varchar(10),a.dtime,120) as dtime,sum(isnull(b.con,0)) as con
from (select dateadd(dd,number,@start) as dtime
    from master..spt_values
    where type = 'p' and number between 0 and datediff(dd,@start,@end)) a
    left join ta b on convert(varchar(10),a.dtime,120) = convert(varchar(10),b.dtime,120)
group by convert(varchar(10),a.dtime,120)
order by convert(varchar(10),a.dtime,120)

[kevn]

select atime,sum(amount) from (select  distinct a.atime,isnull(con,0) as amount from 
(select dateadd(dd,number,dtime) atime from ta,master..spt_values b where b.number <10 and type = 'p') a left join ta
on a.atime = ta.dtime ) b group by atime

number 自己设定

[稻草_木偶]

mark