PostgreSql的CTE + UPDATE + DELETE不是预期的结果，为什么？
在PostgreSQL中，CTE（通用表达式）是一种强大的功能，它允许我们在查询中创建临时表，并在后续的查询中使用。同时，UPDATE和DELETE语句用于更新和删除数据库中的数据。然而，当将CTE与UPDATE或DELETE语句结合使用时，可能会导致一些意外的结果，这是因为CTE的工作方式与UPDATE和DELETE语句之间的交互具有一些细微差别。CTE的工作方式CTE是在查询中定义的临时表，它可以在同一个查询中的多个地方被引用。CTE使用WITH子句来定义，并且可以被视为一个包含多个列的虚拟表。CTE可以在查询中的任何位置使用，并且可以被其他CTE引用。这使得查询更加简洁和可读。CTE与UPDATE语句的交互当将CTE与UPDATE语句结合使用时，CTE的结果集会被用作UPDATE语句的目标表。这意味着UPDATE语句将会更新CTE查询的结果集中的数据，而不是原始表中的数据。这可能会导致一些意外的结果，特别是当CTE查询的结果集与原始表中的数据存在重叠时。CTE与DELETE语句的交互类似于UPDATE语句，当将CTE与DELETE语句结合使用时，CTE的结果集也会被用作DELETE语句的目标表。这意味着DELETE语句将会删除CTE查询的结果集中的数据，而不是原始表中的数据。同样地，这可能会导致一些意外的结果，特别是当CTE查询的结果集与原始表中的数据存在重叠时。案例代码为了更好地理解CTE与UPDATE和DELETE语句之间的交互，我们来看一个简单的案例。假设我们有一个名为"employees"的表，其中包含员工的ID、姓名和薪水信息。我们想要将薪水高于平均薪水的员工的薪水减少10%。我们可以使用CTE来计算平均薪水，并将结果与原始表进行更新。sql-- 创建employees表CREATE TABLE employees ( id serial PRIMARY KEY, name VARCHAR(100) NOT NULL, salary numeric NOT NULL);-- 插入示例数据INSERT INTO employees (name, salary)VALUES ('John', 5000), ('Jane', 6000), ('Alice', 7000), ('Bob', 4500);-- 使用CTE和UPDATE语句来更新薪水WITH avg_salary AS ( SELECT AVG(salary) AS avg_salary FROM employees)UPDATE employeesSET salary = salary * 0.9WHERE salary > (SELECT avg_salary FROM avg_salary);-- 查看更新后的数据SELECT * FROM employees;在这个例子中，我们首先创建了一个名为"employees"的表，并插入了一些示例数据。然后，我们使用CTE来计算平均薪水，并将结果存储在名为"avg_salary"的临时表中。最后，我们使用UPDATE语句将薪水高于平均薪水的员工的薪水减少10%。预期结果与实际结果的差异然而，当我们运行上述代码时，我们可能会得到一个不是预期的结果。这是因为CTE的结果集是根据当前查询时刻的数据计算的，而UPDATE和DELETE语句在CTE查询之前执行。因此，如果在CTE查询之后，但在UPDATE或DELETE语句执行之前，有其他会影响结果集的更新或删除操作，那么最终的结果可能会与预期不符。为了避免这种情况，我们可以考虑使用子查询或临时表来代替CTE，以确保我们得到预期的结果。这样，我们可以在UPDATE或DELETE语句之前获取最新的数据，并将其用作目标表。尽管PostgreSQL的CTE功能非常强大和灵活，但在与UPDATE和DELETE语句结合使用时，可能会导致一些意外的结果。这是因为CTE的结果集与原始表之间存在一些细微差别，特别是在更新或删除操作之前有其他会影响结果集的更新或删除操作时。因此，在使用CTE、UPDATE和DELETE语句时，我们应该仔细考虑数据的变化和结果的预期，以确保我们得到正确的结果。