考虑使用下面的代码.类型enum_buysell仅包含2个值：买入和卖出.在某些情况下我需要得到相反的值,但代码看起来很难看,imho.有没有办法优化它？我想过根本不使用enum,例如把它改为布尔值,但这个想法并不完美,因为它使数据本身不那么明显.
select
datetime,case
when account_id_active = p_account_id and direction = 'buy' then 'buy'::enum_buysell
when account_id_active = p_account_id and direction = 'sell' then 'sell'::enum_buysell
when account_id_passive = p_account_id and direction = 'buy' then 'sell'::enum_buysell
when account_id_passive = p_account_id and direction = 'sell' then 'buy'::enum_buysell
end as direction,price,volume
from
deals
where
account_id_active = p_account_id or
account_id_passive = p_account_id
order by
datetime desc
limit
10;
由于在Postgresql中没有获取枚举的下一个值的功能,您应该自己定义它.
create function next_buysell (e enum_buysell)
returns enum_buysell
as $$
begin
return (case when e='buy'::enum_buysell then 'sell'::enum_buysell
else 'buy'::enum_buysell
end);
end
$$language plpgsql;
@H_301_9@现在,您可以像这样使用它：
postgres=# select next_buysell('sell'::enum_buysell);
next_buysell
--------------
buy
(1 row)
postgres=# select next_buysell('buy'::enum_buysell);
next_buysell
--------------
sell
(1 row)
@H_301_9@并且您的CASE声明变为：
case when account_id_active = p_account_id then direction when account_id_passive = p_account_id then next_buysell(direction) end as direction
