SQL根据另一列减去数据

Question

我有一个SQL表，其中包含以下列和数据:

<表类> ITEM_ID CUST_ID 数量类型PARENT_IDtbody><<tr>1234道明>生空1334道明>精制121534300精制1217414500生空18411500精制1723413500精制空24416000生空25411460精制24

一个conditional sum就是你所需要的，以下是你想要的结果:

select cust_Id,
Sum(case when type='refined' then amount else 0 end) Refined_Amount,
Sum(case when type='RAW' and PARENT_ID is null then amount end) 
- Sum(case when type='refined' and parent_id is not null then amount else 0 end) Unrefined_Amount
from t
group by cust_id

使用条件聚合:

SELECT CUST_ID,
SUM(CASE WHEN TYPE = 'REFINED' THEN AMOUNT ELSE 0 END) REFINED_AMOUNT,
SUM(CASE 
WHEN TYPE = 'RAW' AND PARENT_ID IS NULL THEN 1 
WHEN TYPE = 'REFINED' AND PARENT_ID IS NOT NULL THEN -1
ELSE 0 
END * AMOUNT
)  UNREFINED_AMOUNT
FROM tablename
GROUP BY CUST_ID