求一个复杂的百分比的SQL语句写法，最好能写成存储过程

zpy_1984 2008-12-04 09:29:21

有一张表Trate
code | term1 | term2 value
--------------------------------
0101 | 1 | 2 | 1.0
0101 | 2 | 2 | 1.5
0102 | 1 | 1 | 2.2
0102 | 2 | 2 | 1.6
0201 | 2 | 2 | 3.2
0201 | 2 | 1 | 1.2
0202 | 1 | 1 | 0.8
0202 | 1 | 2 | 2.5

我要的得到这两张表：
1.按Code前两位分组，即Group By SubString(Code,1,2)的value平均值，value 占总value的百分比，我的做法是
select SubString(Code,1,2),sum(Value)/Count(SubString(Code,1,2)),sum(value)/(select sum(value) from Trate) from Trate [where [Term1 = **] [and Term2 = **]] Group By SubString(Code,1,2)
其中[]代表条件是可选的，即有可能有条件，也有可能没有条件
2.按Code分组的value平均值，value占总value的百分比，value占大类（Code前两位相同归为一类)中百分比，我的做法是
select Code，sum(Value)/Count(code),sum(value)/(select sum(value) from Trate),sum(value)/(select sum(value) from Trate where subString(Code,1,2)=a.substring(code,1,2)) from Trate a [where [Term1 = **] [and Term2 = **]] group by code

请问有什么其他的好的方法，我这样弄数据多时，查询很慢

...全文

82 2 打赏收藏转发到动态举报

写回复

用AI写文章

2 条回复

切换为时间正序

请发表友善的回复…

发表回复

-晴天 2008-12-04

打赏
举报

1.对code创建索引.
2.分成多条语句,或者设置为函数.先执行:
declare @sum decimal(10,2)
select @sum=sum(value) from trate
然后再把 @sum代入去查询:
select left(code,2),sum(value)/count(*) as Avgvalue,sum(value)/@sum as parentvalue from trate group by left(code,2)

-狙击手- 2008-12-04

打赏
举报

1.按Code前两位分组，即Group By SubString(Code,1,2)的value平均值，value 占总value的百分比，我的做法是
select SubString(Code,1,2),sum(Value)/Count(SubString(Code,1,2)),sum(value)/(select sum(value) from Trate) from Trate [where [Term1 = **] [and Term2 = **]] Group By SubString(Code,1,2)

---

declare @i numeric(12,2)

select @i = sum(value) from Trate

select
SubString(Code,1,2),
sum(Value)/Count(1),
sum(value)/@i
from Trate
[where [Term1 = **] [and Term2 = **]]
Group By SubString(Code,1,2)