SQL查询效率

Question

我有一个有效的查询，但不确定它是否有效。

表格列:

1. p_type:可以是 STRING sell、cancelsell、bank、cancelbank
2. 编号
3. 感觉
4. 单位

要执行的操作:

1. 每个流程类型的 SUM(单位)sell、cancelsell、bank、cancelbank。
2. SUM(销售单位)- SUM(取消销售单位)，SUM(银行单位)- SUM(取消银行单位)

注意:我还在每个选择中检查 id 和 fid，尽管我需要检查所有选择的相同值。

现有查询:

    select sell-cancelsell scount, bank-cancelbank bcount from

    (select sum(a.unit) as sell from table1 a where   
    a.p_type = 'Sell' and a.id=1 and a.fid=2 ),

    (select sum(c.unit) as cancelsell from table1  c where      
    c.p_type = 'CancelSell' and c.id=1 and c.fid=2),

    (select sum(b.unit) as bank from table1  b where     
    b.p_type = 'Bank' and b.id=1 and b.fid=2),

    (select sum(d.unit) as cancelbank from table1  d where  
    d.p_type = 'CancelBank' and d.id=1 and d.fid=2)

够好吗？如果有人能提出一种提高效率的方法，那就太好了。

Answer 1

你可以这样做

select 
sum(Case when a.p_type = 'sell' then a.unit else null end) as sellUnit, 
sum(Case when a.p_type = 'CancelSell' then a.unit else null end) as CancelSellUnit,
sum(Case when a.p_type = 'Bank' then a.unit else null end) as BankUnit ,
sum(Case when a.p_type = 'CancelBank' then a.unit else null end) as CancelBankUnit  
from table1 a where and a.id=1 and a.fid=2