WITH 子句（ 公用表表达式 CTE）

SQL

WITH a as (SELECT id, row_number() OVER() rn FROM camera)
UPDATE camera SET id = (SELECT rn FROM a WHERE camera.id = a.id);

什么是WITH子句

如果你想多次使用某个表，可以通过使用 WITH 子句（公用表表达式 CTE） 来提前定义表，然后在主查询中多次引用它。这样不仅可以避免重复的子查询，还可以使查询更加清晰和高效。

WITH roles AS (
	SELECT t_role.* FROM t_user, t_role
		WHERE t_user.id = 1
			AND t_role.id = ANY(t_user.roles)
)
SELECT 1 as pass FROM (
	SELECT roles.role_name = 'Administrator' OR roles.role_name = 'Normal' as pass FROM roles
) as a, (
	SELECT roles.interface_permissions -> 'a' -> 'b' = 'true'::jsonb as pass FROM roles
) as b
WHERE
a.pass AND b.pass