postgres + with...as()

postgres + with...as()"/>

postgres + with...as()

1. 简介
   “WITH AS” 是 SQL 语言中的一个语法结构，它允许你在查询中创建一个临时的命名查询（也称为 “公共表表达式” 或 “CTE”），并可以在后续的查询中引用它。这个语法结构通常用于简化复杂查询，提高代码的可读性和可维护性。
   需要注意"WITH AS" 创建的临时表只在当前会话中有效，好处是不需要永久行临时表的权限

2. 多个with as

WITH tmp_p AS (select ca.province_id, ca.province_name from crm_areas ca where ca.city_id is null and ca.county_id is null
), 
tmp_up as (
SELECT province_id, count(id) cid
FROM crm_users
GROUP BY province_id
)
SELECT tmp_p.*
FROM tmp_p where tmp_p.province_id not in (select province_id from tmp_up);

2. 结合update

WITH tmp_p AS (select ca.province_id, ca.province_name from crm_areas ca where ca.city_id is null and ca.county_id is null
)
update crm_users set province = tmp_p.province_name from tmp_p
where crm_users.province = '' and crm_users.province_id = tmp_p.province_id;