您可以使用UNION ALL从一行创建两行。例如：

Select c."saleAmount", c."buyAmount", o."id", c.type
From 
(
  Select
    "saleAmount",
    Case When "buyAmount" > 0 Then 0 Else "buyAmount" End As "buyAmount",
    'sale' as type
  From "Charges"
  Where "saleAmount" > 0
  Union All
  Select
    Case When "saleAmount" > 0 Then 0 Else "saleAmount" End As "saleAmount",
    "buyAmount",
    'buy' as type
  From "Charges"
  Where "buyAmount" > 0
) c
Left Join "Operations" o On o."id" = c."operationsId"
Order By o."id";

顺便说一下，Operations表的连接似乎是多余的。Charges有一个operationsId，然后它链接到具有相同ID的Operations行，或者它没有operationsId，然后它不链接到Operations行。那么，为什么不显示“Charges”.“operationsId”，而不是连接到Operations表来显示相同的ID呢？

您可以联接到 values 表构造函数，并使用case表达式确定有多少行符合联接条件：

select t.* 
from t
join (
  values(1),(2)
)x(r) on r <= case 
    when Coalesce(saleamount, 0) > 0
     and Coalesce(buyAmount, 0) > 0
    then 2 else 1 end;

我想你可以用union all。

select
  c.saleAmount,
  0 buyAmount,
  o.id,
  'sale'
from Charges c
  Left Join "Operations" o On o."id" = c."operationsId"
where isnull(c.saleAmount) > 0
union all
select
  0 saleAmount,
  c.buyAmount,
  o.id,
  'buy'
from Charges c
  Left Join "Operations" o On o."id" = c."operationsId"
where isnull(c.buyAmount,0) > 0

在Postgres中，可以使用values和一个横向连接将两列反透视到行，然后在where子句中过滤掉不需要的行：

select c.*, t.type
from charges c
cross join lateral ( 
    values (c.saleamount, 'sale'), (c.buyamount, 'buy')
) t(amount, type)
where t.amount > 0

原始查询中left join的用途并不明显，所以我将其放在一边--但是如果需要，您可以很容易地将其添加到查询中。