gpt4 book ai didi

sql - Firebird SQL : query slow due to coalesce or can it be rewritten

转载 作者:行者123 更新时间:2023-12-02 07:24:52 26 4
gpt4 key购买 nike

我在使用一个经常使用的查询时遇到了一些性能问题。

SELECT
v.id,
coalesce((SELECT sum(amount) FROM artjournal WHERE variant_ref=v.id AND storage_ref=1 AND atype_ref in (1,3,4)), 0) "fv",
coalesce((SELECT sum(amount) FROM artjournal WHERE variant_ref=v.id AND storage_ref=1 AND atype_ref=2), 0) "ivo",
coalesce((SELECT sum(amount) FROM artjournal WHERE variant_ref=v.id AND storage_ref=1 AND atype_ref=5), 0) "iio",
coalesce((SELECT sum(amount * mvalue) FROM artjournal WHERE variant_ref=v.id AND storage_ref=1), 0) "vw"
FROM productvariant v

由于 artjournal 是一个大表,每天都有数千条新记录,因此性能变得很糟糕。

我在所有 ID 字段上都有索引。

有没有办法重写这个语句来加快速度?或者我可以使用不同的方式从 artjournal 表中检索数据并在结果为 null 时返回 0 吗?

谢谢你的想法,

克里斯蒂安

最佳答案

看起来你想要一个过滤聚合:

SELECT v.id,
sum(case when a.atype_ref in (1,3,4) then a.amount else 0 end) as "fv",
sum(case when a.atype_ref = 2 then a.amount else 0 end) as "ivo",
sum(case when a.atype_ref = 5 then a.amount else 0 end) as "iio",
sum(a.amount * a.mvalue) as "vw"
FROM productvariant v
LEFT JOIN artjournal a ON a.variant_ref = v.id
WHERE storage_ref = 1
GROUP BY v.id;

关于sql - Firebird SQL : query slow due to coalesce or can it be rewritten,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/34015007/

26 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com