我们公司上了一套系统,我查看了一下原开发人员在做报表时写的SQL代码,如下所示:
select distinct t3.code,t3.name,t3.material_spec,t3.default_unit,
sum(t2.quantity) over (partition by t3.code) sum_qty,
sum(t2.amount) over (partition by t3.code) sum_amount,
sum(t2.tax_amount) over (partition
by t3.code) - sum(t2.amount) over (partition by t3.code) tax,
sum(t2.tax_amount) over (partition
by t3.code) total_amount
from t1,t2,t3,t4
where ...
order by t3.code
然后以下是我自己整理的代码:
select t3.code,t3.name,t3.material_spec,t3.default_unit,
sum(t2.quantity) sum_qty,
sum(t2.amount) sum_amount,
sum(t2.tax_amount) - sum(t2.amount) tax,
sum(t2.tax_amount) total_amount
from t1,t2,t3,t4
where ...
group by t3.code,t3.name,t3.material_spec,t3.default_unit
order by t3.code
二者运行的结果一要,性能我的那个要比原开发人员写的来的快。而且相差的性能很明显。用group by要快很多。我就想问一下这里的高人?用partition by是不是有别的用意?会比group by 来的更科学更好?(ps:自己查资料一直找不到答案,所以上这里来麻烦大家了)
问题点数:100、回复次数:5Top