请教高手们一个SQL语句
table test
name time mumber
1 2006-03-01 3
1 2006-03-02 2
1 2006-03-03 5
1 2006-03-04 7
1 2007-03-01 8
1 2007-03-02 1
1 2007-03-03 2
1 2007-03-04 9
2 2006-03-01 4
2 2006-03-02 5
2 2006-03-03 6
2 2007-03-01 7
2 2007-03-02 8
2 2007-03-03 5
3 2006-03-01 3
3 2006-03-02 2
3 2006-03-03 5
3 2006-03-04 7
3 2007-03-01 8
3 2007-03-02 1
3 2007-03-03 2
3 2007-03-04 9
4 2006-03-01 4
4 2006-03-02 5
4 2006-03-03 6
4 2007-03-01 7
4 2007-03-02 8
4 2007-03-03 5
用户输入时间段2007-03-01到2007-03-03
name为1和2
要求结果：
name number TB
1 11 10.0%
2 20 66.7%
其中TB等于用户输入的时间段中number的和减去去年同期number的和乘以100％也就是算个同期比
望高手们不吝赐教小弟在这里先谢谢各位了
[解决办法]
if object_id( 'pubs..tb ') is not null
drop table tb
go
create table tb(name varchar(1),time datetime,mumber int)
insert into tb(name,time,mumber) values( '1 ', '2006-03-01 ', 3)
insert into tb(name,time,mumber) values( '1 ', '2006-03-02 ', 2)
insert into tb(name,time,mumber) values( '1 ', '2006-03-03 ', 5)
insert into tb(name,time,mumber) values( '1 ', '2006-03-04 ', 7)
insert into tb(name,time,mumber) values( '1 ', '2007-03-01 ', 8)
insert into tb(name,time,mumber) values( '1 ', '2007-03-02 ', 1)
insert into tb(name,time,mumber) values( '1 ', '2007-03-03 ', 2)
insert into tb(name,time,mumber) values( '1 ', '2007-03-04 ', 9)
insert into tb(name,time,mumber) values( '2 ', '2006-03-01 ', 4)
insert into tb(name,time,mumber) values( '2 ', '2006-03-02 ', 5)
insert into tb(name,time,mumber) values( '2 ', '2006-03-03 ', 6)
insert into tb(name,time,mumber) values( '2 ', '2007-03-01 ', 7)
insert into tb(name,time,mumber) values( '2 ', '2007-03-02 ', 8)
insert into tb(name,time,mumber) values( '2 ', '2007-03-03 ', 5)
go
declare @dt1 as datetime
declare @dt2 as datetime
set @dt1 = '2007-03-01 '
set @dt2 = '2007-03-03 '
select m.name , m.mumber , cast(cast((m.mumber*1.0 - n.mumber)/n.mumber*100 as decimal(18,1)) as varchar) + '% ' as TB from
(select name,sum(mumber) mumber from tb where time > = @dt1 and time <= @dt2 group by name) m,
(select name,sum(mumber) mumber from tb where time > = dateadd(year,-1 ,@dt1) and time <= dateadd(year,-1,@dt2) group by name) n
where m.name = n.name
drop table tb
/*
name mumber TB
---- ----------- -------------------------------
1 11 10.0%
2 20 33.3%
（所影响的行数为 2 行）
*/
[解决办法]
select a.num ,convert(decimal(18,2),(a.num) )/b.num from
( select name , sum(a.mumber) num from t a
where datediff(day,a.t1, '2007/3/1 ') <=0 and datediff(day,a.t1, '2007/3/3 ')> =0
group by name
) a,
(
select name , sum(a.mumber) num from t a where datediff(day,a.t1,dateadd(year,-1, '2007/3/1 ')) <=0 and datediff(day,a.t1,dateadd(year,-1, '2007/3/3 '))> =0
group by name
) b
where a.name =b.name
