刚刚发了个贴子:http://topic.csdn.net/u/20071112/09/35834028-f4ec-410e-a27f-08837acada51.html
感谢大家的帮忙,帮我解决了
可是由于我没把问题说清,所以现在重新开一贴(请大家先看看上面那个贴子,不然我怕我说的不清楚,大家看不明白)现有一表如下
意见 日期 种类
asdf 2007-10-11 0:15:15 1
fdasd 2007-10-11 0:12:15 1
asasd 2007-10-12 10:15:15 2
fasdf 2007-10-13 02:15:32 2
asds 2007-10-15 03:12:23 3
asfd 2007-10-17 04:32:12 3
要得出的结果如下(起始时间2007-10-09 结束时间2007-10-20)
2007-10-09 2007-10-10 2007-10-11 2007-10-12 2007-10-13 2007-10-14 2007-10-15 ...
(种类1意见数) 0 0 2 0 0 0 0 ...
(种类2意见数) 0 0 0 1 1 0 0 ...
(种类3意见数) 0 0 0 0 0 0 1 ...
感谢大家的帮忙,帮我解决了
可是由于我没把问题说清,所以现在重新开一贴(请大家先看看上面那个贴子,不然我怕我说的不清楚,大家看不明白)现有一表如下
意见 日期 种类
asdf 2007-10-11 0:15:15 1
fdasd 2007-10-11 0:12:15 1
asasd 2007-10-12 10:15:15 2
fasdf 2007-10-13 02:15:32 2
asds 2007-10-15 03:12:23 3
asfd 2007-10-17 04:32:12 3
要得出的结果如下(起始时间2007-10-09 结束时间2007-10-20)
2007-10-09 2007-10-10 2007-10-11 2007-10-12 2007-10-13 2007-10-14 2007-10-15 ...
(种类1意见数) 0 0 2 0 0 0 0 ...
(种类2意见数) 0 0 0 1 1 0 0 ...
(种类3意见数) 0 0 0 0 0 0 1 ...
insert into tb values('asdf' ,'2007-10-11 00:15:15',1)
insert into tb values('fdasd','2007-10-11 00:12:15',1)
insert into tb values('asasd','2007-10-12 10:15:15',2)
insert into tb values('fasdf','2007-10-13 02:15:32',2)
insert into tb values('asds' ,'2007-10-15 03:12:23',3)
insert into tb values('asfd' ,'2007-10-17 04:32:12',3)
go
select t.dt 日期,t.种类,isnull(m.cnt,0) 意见数 from
(
select t1.dt , t2.种类 from
(
select '2007-10-09' as dt 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'
) t1 cross join (select distinct 种类 from tb) t2
) t
left join
(
select convert(varchar(10),日期,120) dt ,种类, count(*) cnt from tb group by convert(varchar(10),日期,120),种类
) m
on t.dt = m.dtdrop table tb/*
日期 种类 意见数
---------- ----------- -----------
2007-10-09 1 0
2007-10-09 2 0
2007-10-09 3 0
2007-10-10 1 0
2007-10-10 2 0
2007-10-10 3 0
2007-10-11 1 2
2007-10-11 2 2
2007-10-11 3 2
2007-10-12 1 1
2007-10-12 2 1
2007-10-12 3 1
2007-10-13 1 1
2007-10-13 2 1
2007-10-13 3 1
2007-10-14 1 0
2007-10-14 2 0
2007-10-14 3 0
2007-10-15 1 1
2007-10-15 2 1
2007-10-15 3 1
2007-10-16 1 0
2007-10-16 2 0
2007-10-16 3 0
2007-10-17 1 1
2007-10-17 2 1
2007-10-17 3 1
2007-10-18 1 0
2007-10-18 2 0
2007-10-18 3 0
2007-10-19 1 0
2007-10-19 2 0
2007-10-19 3 0
2007-10-20 1 0
2007-10-20 2 0
2007-10-20 3 0(所影响的行数为 36 行)
*/
insert into tb values('asdf' ,'2007-10-11 00:15:15',1)
insert into tb values('fdasd','2007-10-11 00:12:15',1)
insert into tb values('asasd','2007-10-12 10:15:15',2)
insert into tb values('fasdf','2007-10-13 02:15:32',2)
insert into tb values('asds' ,'2007-10-15 03:12:23',3)
insert into tb values('asfd' ,'2007-10-17 04:32:12',3)
goselect 种类 ,
max(case 日期 when '2007-10-09' then 意见数 else 0 end) '2007-10-09',
max(case 日期 when '2007-10-10' then 意见数 else 0 end) '2007-10-10',
max(case 日期 when '2007-10-11' then 意见数 else 0 end) '2007-10-11',
max(case 日期 when '2007-10-12' then 意见数 else 0 end) '2007-10-12',
max(case 日期 when '2007-10-13' then 意见数 else 0 end) '2007-10-13',
max(case 日期 when '2007-10-14' then 意见数 else 0 end) '2007-10-14',
max(case 日期 when '2007-10-15' then 意见数 else 0 end) '2007-10-15',
max(case 日期 when '2007-10-16' then 意见数 else 0 end) '2007-10-16',
max(case 日期 when '2007-10-17' then 意见数 else 0 end) '2007-10-17',
max(case 日期 when '2007-10-18' then 意见数 else 0 end) '2007-10-18',
max(case 日期 when '2007-10-19' then 意见数 else 0 end) '2007-10-19',
max(case 日期 when '2007-10-20' then 意见数 else 0 end) '2007-10-20'
from
(
select t.dt 日期,t.种类,isnull(m.cnt,0) 意见数 from
(
select t1.dt , t2.种类 from
(
select '2007-10-09' as dt 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'
) t1 cross join (select distinct 种类 from tb) t2
) t
left join
(
select convert(varchar(10),日期,120) dt ,种类, count(*) cnt from tb group by convert(varchar(10),日期,120),种类
) m
on t.dt = m.dt
) o
group by 种类
drop table tb/*
种类 2007-10-09 2007-10-10 2007-10-11 2007-10-12 2007-10-13 2007-10-14 2007-10-15 2007-10-16 2007-10-17 2007-10-18 2007-10-19 2007-10-20
----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
1 0 0 2 1 1 0 1 0 1 0 0 0
2 0 0 2 1 1 0 1 0 1 0 0 0
3 0 0 2 1 1 0 1 0 1 0 0 0(所影响的行数为 3 行)
*/
不用像这样
max(case 日期 when '2007-10-09' then 意见数 else 0 end) '2007-10-09',
max(case 日期 when '2007-10-10' then 意见数 else 0 end) '2007-10-10',
max(case 日期 when '2007-10-11' then 意见数 else 0 end) '2007-10-11',
max(case 日期 when '2007-10-12' then 意见数 else 0 end) '2007-10-12',
max(case 日期 when '2007-10-13' then 意见数 else 0 end) '2007-10-13',
max(case 日期 when '2007-10-14' then 意见数 else 0 end) '2007-10-14',
max(case 日期 when '2007-10-15' then 意见数 else 0 end) '2007-10-15',
max(case 日期 when '2007-10-16' then 意见数 else 0 end) '2007-10-16',
max(case 日期 when '2007-10-17' then 意见数 else 0 end) '2007-10-17',
max(case 日期 when '2007-10-18' then 意见数 else 0 end) '2007-10-18',
max(case 日期 when '2007-10-19' then 意见数 else 0 end) '2007-10-19',
max(case 日期 when '2007-10-20' then 意见数 else 0 end) '2007-10-20'
一条条的写,行吗?
意见 varchar(200),
日期 datetime,
种类 int)insert into tb values('asdf','2007-10-11 0:15:15',1)
insert into tb values('fdasd','2007-10-11 0:12:15',1)
insert into tb values('asasd','2007-10-12 10:15:15',2)
insert into tb values('fasdf','2007-10-13 02:12:32',2)
insert into tb values('asds','2007-10-15 03:12:23',3)
insert into tb values('asfd','2007-10-17 04:32:12',3)declare @sql varchar(8000)
set @sql='select 种类'
select @sql=@sql+',sum(case when convert(varchar(10),日期,120)='''+convert(varchar(10),日期,120)+''' then 1 else 0 end) ['+convert(varchar(10),日期,120)+']'
from (select distinct convert(varchar(10),日期,120) 日期 from tb) aset @sql=@sql+' from tb where 日期 between ''2007-10-9'' and ''2007-10-20'' group by 种类'exec (@sql)drop table tb
create table tb(意见 varchar(10),日期 datetime,种类 int)
insert into tb values('asdf' ,'2007-10-11 00:15:15',1)
insert into tb values('fdasd','2007-10-11 00:12:15',1)
insert into tb values('asasd','2007-10-12 10:15:15',2)
insert into tb values('fasdf','2007-10-13 02:15:32',2)
insert into tb values('asds' ,'2007-10-15 03:12:23',3)
insert into tb values('asfd' ,'2007-10-17 04:32:12',3)alter proc asdfasdf
@stardate datetime,
@enddate datetime
as
declare @t table(日期 datetime)
while(@stardate<=@enddate)
begin
insert into @t select @stardate
set @stardate=dateadd(dd,1,@stardate)
end
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[lo]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[lo]
create table lo(日期 datetime,意见数 int,种类 int)
insert into lo select a.日期,count(b.意见)意见数,种类 from @t a left join tb b on datediff(dd,a.日期,b.日期)=0 group by a.日期,种类 order by a.日期
declare @sql varchar(8000)
set @sql='select ''种类''+cast(种类 as varchar)+''意见数'''
select @sql=@sql+',['+convert(varchar(10),日期,120)+']=max(case 日期 when '''+ convert(varchar(10),日期,120) +''' then 意见数 else 0 end)' from lo
set @sql=@sql+' from lo where 种类 is not null group by 种类'
exec(@sql)exec asdfasdf '2007-10-09','2007-10-20'