在SQL中使用WITH子句的指南 [英] Guidance on using the WITH clause in SQL

问题描述

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

I understand how to use the WITH clause for recursive queries (!!), but I'm having problems understanding its general use / power.

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

For example the following query updates one record whose id is determined by using a subquery returning the id of the first record by timestamp:

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 包装而不是相对丑陋的子查询？如果是，为什么？

Would this be a good candidate for using a WITH wrapper instead of the relatively ugly sub-query? If so, why?

推荐答案

如果可以对相关表进行并发写访问，以上查询中的竞争条件。考虑：

If there can be concurrent write access to involved tables, there are race conditions in the above following queries. Consider:

• Postgres更新…限制1

• Postgres UPDATE … LIMIT 1

您的示例可以使用CTE（公用表表达式），但是它不会给您任何子查询无法完成的操作：

Your example can use a CTE (common table expression), but it will give you nothing a subquery couldn't do:

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.*;

BTW，返回的行将是 updated 版本。

BTW, the returned row will be the updated version.

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

If you wanted to insert the returned row into another table, that's where a WITH clause becomes essential:

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进行数据修改查询。

阅读精美手册中的更多内容。

Data modifying queries using CTE are possible with PostgreSQL 9.1 or later.
Read more in the excellent manual.

这篇关于在SQL中使用WITH子句的指南的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！