查询:实现逻辑的更好方法

Question

我需要更好的性能逻辑。

我需要从同一个表和相同的条件下获取离岸时间和现场时间的总和，除了离岸时间，我只会在一列收费为 1 时才选择

select sum(onsitetime)
+ (
select sum (offshoretime) 
from table a
where a.col1 = 2 and a.col2 = 4
and a.chargeable = 1
)
from table a
where a.col1=2 and a.col2=4

Answer 1

将单个查询与条件聚合一起使用：

SELECT
SUM(onsitetime) AS sum1,
SUM(CASE WHEN chargeable = 1 THEN offshoretime ELSE 0 END) AS sum2
FROM table_a
WHERE col1 = 2 AND col2 = 4;

此查询可能受益于以下索引：

CREATE INDEX ON table_a (col1, col2, chargeable);