有没有一种更干净或更简单的方法可以在SQL中实现这一点

Question

假设我有一个oracle表，它表示具有以下定义的简单银行操作：

create table mytable(id_operation varchar(11),
operation_type varchar2(11),
amount number);

该表包含以下数据：

ID_OPERATION    OPERATION_TYPE  AMOUNT
1                credit          200
2                credit          150

对于每个操作，我也想在查询中选择它的借记版本，如下所示：

ID_OPERATION    OPERATION_TYPE  AMOUNT
1C              credit           200
1D              debit           -200
2C              credit           150
2D              debit           -150

以下是我尝试过的：

select id_operation||'C' id_operation ,operation_type , amount
from mytable
union
select id_operation||'D' , 'debit', - amount
from mytable

Answer 1

您可以使用cross join:

select t.id_operation || x.suffix as id_operation,
coalesce(x.operation_type, t.operation_type) as operation_type,
(t.amount * x.mult) as amount
from mytable t cross join
(select 'C' as suffix, null as operation_type, 1 as mult from dual union all
select 'D' as suffix, 'debit' as operation_type, -1 as mult from dual
) x

Answer 2

您正在使用UNION，这是实现它的最干净的方法。

此外，您可以按如下方式使用CONNECT BY：

SELECT
ID_OPERATION || DECODE(LEVEL, 1, 'C', 'D') AS ID_OPERATION,
DECODE(LEVEL, 1, OPERATION_TYPE, 'debit') AS OPERATION_TYPE,
DECODE(LEVEL, 1, AMOUNT, - AMOUNT) AS AMOUNT
FROM MYTABLE
CONNECT BY LEVEL <= 2