时间戳

首页 > 解决方案 > How can I select into a temporary variable mid sql statement

postgresql - How can I select into a temporary variable mid sql statement

问题描述

I have following sql statement:

select * from funds_balance where account_id = 7 and sequence_number <= (
   select sequence_number from funds_balance where account_id = 7 and update_settlement_pool_id = 7
   union all
   select sequence_number from funds_balance_history where account_id = 7 and update_settlement_pool_id = 7
   limit 1
)
union all
select account_id, deliverable_id, cash, credit,pending,sequence_number, update_time,update_reason, update_funds_action_id, update_trade_serial, update_settlement_pool_id
from funds_balance_history where account_id = 7 and sequence_number <= (
   select sequence_number from funds_balance where account_id = 7 and update_settlement_pool_id = 7
   union all
   select sequence_number from funds_balance_history where account_id = 7 and update_settlement_pool_id = 7
   limit 1
)
order by sequence_number desc limit 2

In the above complex statement there are two identical sub-queries which I assume are executing twice.

Is there any way to only execute this sub-query once, maybe storing it some kind of temp variable to reference in the outer query?

Pseudo code below describing what I mean:

var seq_num = select sequence_number from funds_balance where account_id = 7 and update_settlement_pool_id = 7
   union all
   select sequence_number from funds_balance_history where account_id = 7 and update_settlement_pool_id = 7
   limit 1

select * from funds_balance where account_id = 7 and sequence_number <= seq_num
union all
select account_id, deliverable_id, cash, credit,pending,sequence_number, update_time,update_reason, update_funds_action_id, update_trade_serial, update_settlement_pool_id
from funds_balance_history where account_id = 7 and sequence_number <= seq_num
order by sequence_number desc limit 2

标签： postgresql

解决方案

要将您的预期行为简单地转换为 SQL 查询，您可以为此使用CTE（WITH子句）：

WITH seq_num AS (

   select sequence_number from funds_balance where account_id = 7 and update_settlement_pool_id = 7
   union all
   select sequence_number from funds_balance_history where account_id = 7 and update_settlement_pool_id = 7
   limit 1

)

select * from funds_balance where account_id = 7 
    and sequence_number <= (SELECT sequence_number FROM seq_num)
union all
select account_id, deliverable_id, cash, credit,pending,sequence_number, update_time,update_reason, update_funds_action_id, update_trade_serial, update_settlement_pool_id
from funds_balance_history where account_id = 7 
    and sequence_number <= (SELECT sequence_number FROM seq_num)

推荐阅读