对选择查询中的每一行进行操作

Question

如何对SELECT中的每一行执行附加查询（UPDATE）？我必须从select中获取每一行的金额，并将其发送到用户的余额表。

示例:

status 0 - open
status 1 - processed
status 2 - closed

我的select语句:

select id, user_id, sell_amount, sell_currency_id
from (select id, user_id, sell_amount, sell_currency_id,
             sum(sell_amount) 
             over (order by buy_amount/sell_amount ASC, date_add ASC) as cumsell
      from market t
      where (status = 0 or status = 1) and type = 0
     ) t
where 0 <= cumsell and 7 > cumsell - sell_amount;

从市场表中选择结果

id;user_id;amount;status
4;1;1.00000000;0
6;2;2.60000000;0
5;3;2.00000000;0
7;4;4.00000000;0

我们得到7个金额并将其发送到用户余额表。

id;user_id;amount;status
4;1;0.00000000;2 -- took 1, sum 1, status changed to 2
6;2;0.00000000;2 -- took 2.6, sum=3.6, status changed to 2
5;3;0.00000000;2 -- took 2, sum 5.6, status changed to 2
7;4;2.60000000;1 -- took 1.4, sum 7.0, status changed to 1 (because there left 2.6 to close)

用户的余额表

user_id;balance
5;7 -- added 7 from previous operation

Postgres版本9.3

Answer 1

总的原则是使用<代码>更新...FROM通过子查询。您的示例太难了，无法转化为有用的< code>CREATE TABLE和< code>SELECT语句，因此我创建了一个快速虚拟数据集:

CREATE TABLE balances (user_id integer, balance numeric);
INSERT INTO balances (user_id, balance) VALUES (1,0), (2, 2.1), (3, 99);
CREATE TABLE transactions (user_id integer, amount numeric, applied boolean default 'f');
INSERT INTO transactions (user_id, amount) VALUES (1, 22), (1, 10), (2, -10), (4, 1000000);

如果您想将交易应用于余额，您可以执行以下操作：

   BEGIN;

   LOCK TABLE balances IN EXCLUSIVE MODE;
   LOCK TABLE transactions IN EXCLUSIVE MODE;

   UPDATE balances SET balance = balance + t.amount 
   FROM (
      SELECT t2.user_id, sum(t2.amount) AS amount 
      FROM transactions t2 
      GROUP BY t2.user_id
   ) t 
   WHERE balances.user_id = t.user_id;

   UPDATE transactions
   SET applied = true
   FROM balances b
   WHERE transactions.user_id = b.user_id;

< code>LOCK语句对于并发插入/更新的正确性非常重要。

第二个更新将事务标记为已应用;您的设计中可能不需要这样的东西。