在同一行的同表中减去where条件#SQL

Question

使用SQL查询在同一个表中减去：

Table1

grade   record  total_amount
------------------------------
DSP     receipt     17258
DSP     sales       16313
OPC43   receipt     95442
OPC43   sales       92856
OPC53   receipt    371752
OPC53   sales      368985
PPC     receipt    156023
PPC     sales      152803
vajram  receipt     36100
vajram  sales       36100

所需答案

 945    DSP
2586    OPC43
2767    OPC53
3220    PPC
   0    vajram

Answer 1

每个年级只有两项吗？总是只有一个receipt和sales？如果是这样的话，下面这样的东西就能起作用了

SELECT receipt.total_amount - sales.total_amount, grade 
FROM 
   (SELECT * FROM yourtable WHERE record = 'receipt') receipt
   INNER JOIN (SELECT * FROM yourtable WHERE record = 'sales') sales
       ON receipt.grade = sales.grade

Answer 2

我会尝试这样的方法：

select grade, sum(case when record = 'receipt' then total_amount else 0-total_amount end)
from table
group by grade