use text
go
if exists(select * from sysobjects
where name = 'sells')
begin drop table sells
end
go
create table sells
(
year varchar(8) ,
quarter char(2),
sale decimal(10,3)
);
insert into sells values (2005,1,50.5);
insert into sells values (2005,2,75.78);
insert into sells values (2005,3,68.8);
insert into sells values (2005,4,120.85);
insert into sells values (2006,1,110.5);
insert into sells values (2006,2,100.87);
insert into sells values (2006,3,75.556);
insert into sells values (2006,4,150);
insert into sells values (2007,1,25);
insert into sells values (2007,2,25);
insert into sells values (2007,3,25);
insert into sells values (2007,4,25);
----------------------------------我的答案---------------------------------------------
if exists (select name from sysobjects where name='proc_result')
drop proc proc_result
go
create proc proc_result
as
begin
select distinct t1.year+'年' as '年份',
(select sale from sells t2 where quarter=1 and t1.year=t2.year) as '第一季度',t8.百分比,
(select sale from sells t2 where quarter=2 and t1.year=t2.year) as '第二季度',t9.百分比,
(select sale from sells t2 where quarter=3 and t1.year=t2.year) as '第三季度',t10.百分比,
(select sale from sells t2 where quarter=4 and t1.year=t2.year) as '第四季度',t11.百分比,
(select sum(sale) from sells t2 where t1.year=t2.year ) as '总计'
from sells t1
,(select distinct left((t5.sale/t6.sale)*100,6) as'百分比',t5.year from (select distinct(select sale from sells t2 where quarter=1 and t1.year=t2.year) as 'sale',year from sells t1)t5,(select distinct sale =(select sum(sale) from sells t2 where t1.year=t2.year),year from sells t1 )t6 where t5.year=t6.year)t8
,(select distinct left((t5.sale/t6.sale)*100,6) as'百分比',t5.year from (select distinct(select sale from sells t2 where quarter=2 and t1.year=t2.year) as 'sale',year from sells t1)t5,(select distinct sale =(select sum(sale) from sells t2 where t1.year=t2.year),year from sells t1 )t6 where t5.year=t6.year)t9
,(select distinct left((t5.sale/t6.sale)*100,6) as'百分比',t5.year from (select distinct(select sale from sells t2 where quarter=3 and t1.year=t2.year) as 'sale',year from sells t1)t5,(select distinct sale =(select sum(sale) from sells t2 where t1.year=t2.year),year from sells t1 )t6 where t5.year=t6.year)t10
,(select distinct left((t5.sale/t6.sale)*100,6) as'百分比',t5.year from (select distinct(select sale from sells t2 where quarter=4 and t1.year=t2.year) as 'sale',year from sells t1)t5,(select distinct sale =(select sum(sale) from sells t2 where t1.year=t2.year),year from sells t1 )t6 where t5.year=t6.year)t11
where t1.year=t8.year and t1.year=t9.year and t1.year=t10.year and t1.year=t11.year
order by t1.year+'年'
end
go
exec proc_result
忘了说明要求,最后结果就是我输出的结果,可是效率很慢啊