在SQL中使用WITH子句的指南

小编典典

在SQL中使用WITH子句的指南

sql

我知道如何将WITH子句用于递归查询（!!），但是在理解其一般用法/功能时遇到了问题。

例如，以下查询更新一条记录，该记录的ID是通过使用子查询按时间戳返回第一条记录的ID来确定的：

update global.prospect psp
set    status=status||'*'
where  psp.psp_id=(
           select  p2.psp_id
           from    global.prospect p2
           where   p2.status='new' or p2.status='reset'
           order   by p2.request_ts
           limit   1 )
returning psp.*;

这是使用WITH包装器而不是相对丑陋的子查询的不错选择吗？如果是这样，为什么？

阅读 186

2021-04-15

共1个答案

小编典典

如果可以对涉及的表进行 并发写访问 ，则上述以下查询中存在争用条件。考虑：

Postgres UPDATE-LIMIT 1

您的示例可以使用CTE（公用表表达式），但是它不会给您任何子查询无法做到的事情：

WITH x AS (
   SELECT  psp_id
   FROM    global.prospect
   WHERE   status IN ('new', 'reset')
   ORDER   BY request_ts
   LIMIT   1
   )
UPDATE global.prospect psp
SET    status = status || '*'
FROM   x
WHERE  psp.psp_id = x.psp_id
RETURNING psp.*;

顺便说一句，返回的行将是 更新的 版本。

如果您想将返回的行插入到另一个表中，那么WITH子句就变得很重要：

WITH x AS (
   SELECT  psp_id
   FROM    global.prospect
   WHERE   status IN ('new', 'reset')
   ORDER   BY request_ts
   LIMIT   1
   ), y AS (
   UPDATE global.prospect psp
   SET    status = status || '*'
   FROM   x
   WHERE  psp.psp_id = x.psp_id
   RETURNING psp.*
   )
INSERT INTO z
SELECT *
FROM   y

PostgreSQL
9.1或更高版本可以使用CTE进行数据修改查询。

2021-04-15