查询问题,在线结分

[fang1572]

declare @b table(意见 varchar(20),日期 smalldatetime)
insert @b select 'asdf','2007-10-11 0:15:15'   
union all select 'fdasd','2007-10-11 0:12:15'   
union all select 'asasd','2007-10-12 10:15:15'   
union all select 'asds', '2007-10-15 03:12:23'   
union all select 'asfd', '2007-10-17 04:32:12'   

declare @a table(id int identity(0,1) ,s smalldatetime,e smalldatetime)
declare @s smalldatetime,@e smalldatetime
select @s='2007-10-09',@e='2007-10-20'
insert @a select top 31 null,null from syscolumns

select convert(varchar(10),aa.ss,120) 日期,count(意见) 意见数 from
(select id,dateadd(day,id,@s) SS from @a where dateadd(day,id,@s)<=@e)aa
left join @b bb
on  datediff(day,aa.ss,bb.日期)=0
group by aa.ss

--result
/*
日期         意见数         
---------- ----------- 
2007-10-09 0
2007-10-10 0
2007-10-11 2
2007-10-12 1
2007-10-13 0
2007-10-14 0
2007-10-15 1
2007-10-16 0
2007-10-17 1
2007-10-18 0
2007-10-19 0
2007-10-20 0

（所影响的行数为 12 行）
*/

以上是我前两天发贴,在树上的鸟儿的帮助下得到的一组程序,原贴:http://topic.csdn.net/u/20071112/09/35834028-f4ec-410e-a27f-08837acada51.html
由于我现在的要求有所变动:在表@b里新加了一列,状态列,共有四个状态,分别为0,1,2,3
希望能得出结果是每一天分四个状态,每个状态有一个意见数,类似于下面的结果吧

日期          状态          意见数         
---------- -------   ----------- 
2007-10-09   0           1
2007-10-09   1           0
2007-10-09   2           2  
2007-10-09   3           1
2007-10-10   0           5
2007-10-10   1           3
2007-10-10   2           0  
2007-10-10   3           4
.
.
.

（所影响的行数为 12*4 行）
*/

各位帮帮忙啊,

[Limpire]

declare @b table(意见 varchar(20),日期 smalldatetime,状态 int)
insert @b select 'asdf','2007-10-11 0:15:15',0
union all select 'fdasd','2007-10-11 0:12:15' ,1  
union all select 'asasd','2007-10-12 10:15:15'  ,3 
union all select 'asds', '2007-10-15 03:12:23'   ,3
union all select 'asfd', '2007-10-17 04:32:12'   ,2

declare @a table(id int identity(0,1),s smalldatetime,e smalldatetime)
declare @s smalldatetime,@e smalldatetime
select @s='2007-10-09',@e='2007-10-20'
insert @a select top 31 null,null from syscolumns

select convert(varchar(10),aa.ss,120) 日期,aa.状态,count(意见) 意见数 from
	(
		select a.*,b.* from
		(select id,dateadd(day,id,@s) SS from @a where dateadd(day,id,@s)<=@e)a
		cross join (select 状态=0 union all select 1 union all select 2 union all select 3) b
	) aa
left join @b bb
on datediff(day,aa.ss,bb.日期)=0 and aa.状态=bb.状态
group by aa.ss,aa.状态
order by aa.日期,aa.状态

[renzhe02]

是这样的吗?
select convert(varchar(10),aa.ss,120) 日期,状态,count(意见) 意见数 from
(select id,dateadd(day,id,@s) SS from @a where dateadd(day,id,@s)<=@e)aa
left join @b bb
on  datediff(day,aa.ss,bb.日期)=0
group by aa.ss,bb.状态,

[dawugui]

create table tb(意见 varchar(10),日期 datetime)
insert into tb values('asdf' ,'2007-10-11 00:15:15')   
insert into tb values('fdasd','2007-10-11 00:12:15')   
insert into tb values('asasd','2007-10-12 10:15:15')   
insert into tb values('fasdf','2007-10-13 02:15:32')   
insert into tb values('asds' ,'2007-10-15 03:12:23')   
insert into tb values('asfd' ,'2007-10-17 04:32:12')
go
--方法一:
select t.日期,n.状态,isnull(m.cnt,0) 意见数 from
(
  select '2007-10-09' 日期 union
  select '2007-10-10' union
  select '2007-10-11' union
  select '2007-10-12' union
  select '2007-10-13' union
  select '2007-10-14' union
  select '2007-10-15' union
  select '2007-10-16' union
  select '2007-10-17' union
  select '2007-10-18' union
  select '2007-10-19' union
  select '2007-10-20' 
) t cross join
(
  select '0' as 状态 union
  select '1' union
  select '2' union
  select '3' 
) n
left join 
( 
  select convert(varchar(10),日期,120) dt , count(*) cnt from tb group by convert(varchar(10),日期,120)
) m
on t.日期 = m.dt
order by  t.日期,n.状态
drop table tb

/*
日期         状态   意见数         
---------- ---- ----------- 
2007-10-09 0    0
2007-10-09 1    0
2007-10-09 2    0
2007-10-09 3    0
2007-10-10 0    0
2007-10-10 1    0
2007-10-10 2    0
2007-10-10 3    0
2007-10-11 0    2
2007-10-11 1    2
2007-10-11 2    2
2007-10-11 3    2
2007-10-12 0    1
2007-10-12 1    1
2007-10-12 2    1
2007-10-12 3    1
2007-10-13 0    1
2007-10-13 1    1
2007-10-13 2    1
2007-10-13 3    1
2007-10-14 0    0
2007-10-14 1    0
2007-10-14 2    0
2007-10-14 3    0
2007-10-15 0    1
2007-10-15 1    1
2007-10-15 2    1
2007-10-15 3    1
2007-10-16 0    0
2007-10-16 1    0
2007-10-16 2    0
2007-10-16 3    0
2007-10-17 0    1
2007-10-17 1    1
2007-10-17 2    1
2007-10-17 3    1
2007-10-18 0    0
2007-10-18 1    0
2007-10-18 2    0
2007-10-18 3    0
2007-10-19 0    0
2007-10-19 1    0
2007-10-19 2    0
2007-10-19 3    0
2007-10-20 0    0
2007-10-20 1    0
2007-10-20 2    0
2007-10-20 3    0

（所影响的行数为 48 行）
*/