sql server 子查询的性能问题请高手帮忙

来源：百度知道 编辑：UC知道 时间：2024/06/15 01:32:58
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_resul

用交叉表会比较快
SELECT Year+'年' as 年份,
SUM(CASE Quarter WHEN 1 THEN sale ELSE 0 END) AS 第一季度,
Cast(SUM(CASE Quarter WHEN 1 THEN sale ELSE 0 END) / SUM(sale) * 100 as decimal(18,2)) as 百分比,
SUM(CASE Quarter WHEN 2 THEN sale ELSE 0 END) AS 第二季度,
Cast(SUM(CASE Quarter WHEN 2 THEN sale ELSE 0 END) / SUM(sale) * 100 as decimal(18,2)) as 百分比,
SUM(CASE Quarter WHEN 3 THEN sale ELSE 0 END) AS 第三季度,
Cast(SUM(CASE Quarter WHEN 3 THEN sale ELSE 0 END) / SUM(sale) * 100 as decimal(18,2)) as 百分比,
SUM(CASE Quarter WHEN 4 THEN sale ELSE 0 END) AS 第四季度,
Cast(SUM(CASE Quarter WHEN 4 THEN sale ELSE 0 END) / SUM(sale) * 100 as decimal(18,2)) as 百分比,
SUM(sale) as 总计
FROM sells
GROUP BY Year