Question

利用邮政总局 16.2 我有两个表格:

表格

create table task_pool (
  task_id serial primary key
, order_id integer
, user_id integer
, expire_time timestamp
, status integer default 0
);

抽样数据:

task_id	order_id	user_id	expire_time	status
50	80	1	2024-03-00 11:52:44	-1(expired)
51	81	1	2024-03-00 16:00:44	-1 (expired)
52	80	2	2024-04-19 12:31:22	0 (active)
53	81	4	2024-03-00 16:52:44	-1(expired)
54	81	6	2024-03-00 16:51:44	-1(expired)

http://www.un.org/Depts/DGACM/index_french.htm

create table reactivate_task(
, order_id integer
, successfully_added
, total_amount_requested
, amount_to_reactivate integer
, 
);

抽样数据:

order_id	successfully_added	total_amount_requested	amount_to_reactivate
80	0	3	3
81	0	3	2

试图做的是,在<条码>中的每一行中,更新<条码>x的旧过期任务和相关<条码>_id和<条码>x,即<条码>。

因此,在上文各例表格中,参照<代码>reactive_task的表格,3个过期的任务,顺序为80个,应“重新启动”。而2个最古老的“信使”应“重新启动”。

表格<代码>task_joint有预期成果:

task_id	order_id	user_id	expire_time	status
50	80	1	2024-03-21 12:00:00	0(active) --status set to 0 and expire_time to current_timestamp
51	81	1	2024-03-21 12:00:00	0(active) ----status set to 0 and expire_time to current_timestamp
52	80	2	2024-04-19 12:31:22	0 (active) --NOT updated (even though amount_to_reactive was 3 for order_id 80, there was only ONE expired task (task_id 50) that was expired.
53	81	4	2024-03-19 16:52:44	-1(expired) --NOT updated (amount_to_reactive was only 2 for order 81, so only the 2 rows with oldest expire_time were updated (task_id 50 & 54)
54	81	6	2024-03-21 12:00:00	0 (active) ----status set to 0 and expire_time to current_timestamp

其中一个例子是:

UPDATE task_pool 
SET status=0, 
    expire_time = current_timestamp + (2 * interval  1 day )
WHERE task_id IN (
    SELECT task_id
    FROM task_pool
    WHERE order_id = *--order_id from CURRENT ROW OF REACTIVATE_TASK--*
          AND status=-1 
    ORDER BY expire_time
    LIMIT *--amount_to_reactivate from current row of reactivate_task--*

但是,在提出这一询问时,我不得不在以下各行各行之间轮流:<代码>reactive_task,在邮政局之外(如果使用电离和电离站,则使用results.rows [上的路段)。

是否有办法在邮局中做这项工作,而不是在每一行间进行循环?

Answer 1

UPDATE task_pool t
SET    status = 0
     , expire_time = current_timestamp + (interval  2 days )
FROM   reactivate_task r
CROSS  JOIN LATERAL (
   SELECT t1.task_id
   FROM   task_pool t1
   WHERE  t1.order_id = r.order_id
   AND    t1.status = -1
   ORDER  BY t1.expire_time
   LIMIT  r.amount_to_reactivate
   ) t1
WHERE t.task_id = t1.task_id;

https://dbfiddle.uk/c0Oyh2XH”rel=“nofollow noreferer”>fiddle

Basics for What is the difference between a LATERAL JOIN and a subquery in PostgreSQL?

上的附加关系更新条款后来不能加入目标表,因此,在这种特殊情况下,你必须使用同一表格的二例。相关:

Is there a way to set multiple column values on a single spatial query?

<代码>CROSS JOIN 删除了<代码>restart_task上的浏览量,在其中找不到可启动的合格浏览量。

如果<代码>amount_to_reactive<>/code>是无效的(可能永远不会如此),实际上就没有限制。

如果(order_id,/19/_time)>task_joint is notUNIquest,除非在上添加更多的表述,否则将任意断绝联系。页: 1

友情链接