PostgreSQL | CTE | 使用with子句的通用表达式

CTE（Common Table Expressions）

简单讲，CTE就是日常SQL中出现的with语句，其原理就是通过提前将数据查询出来后作为临时结果集使用，可以与SELECT \ INSERT \ UPDATE \ DELETE的SQL连用。

优点

1. 可读性强
   • CTE 允许你将复杂的查询拆分成易于理解和管理的块。这使得查询更易于阅读、理解和维护。
2. 重用性
   • CTE 可以在一个查询中多次引用，这使得可以将复杂的逻辑组件分解成可重复使用的部分。
3. 递归查询
   • CTE 允许你执行递归查询，这是一种对于层次化数据结构（如组织结构或树形结构）非常有用的功能。
4. 优化器支持
   • PostgreSQL 的查询优化器可以对 CTE 进行优化，以确保最佳执行计划。

缺点

1. 性能开销
   • 在某些情况下，使用 CTE 可能会导致性能开销。在处理大量数据时，可能会出现性能下降。
2. 可读性降低
   • 尽管 CTE 可以提高可读性，但如果不正确使用，可能会导致查询变得更难理解。特别是在多个 CTE 之间建立复杂的关系时。
3. 内存消耗
   • CTE 通常需要在内存中存储临时结果集，因此对于大型数据集可能会导致内存消耗较高。
4. 不能在索引中使用
   • 不能在 CTE 中创建索引，这可能会导致在某些情况下查询性能下降。
5. 递归查询潜在的性能问题
   • 对于大型或者深度很深的递归查询，可能会导致性能问题。

示例

1. INSERT - 插入

   WITH r AS （
   	SELECT code, name
   	FROM t1
   )
   INSERT INTO t2(code,name)
   SELECT code,name
   FROM r;
   1
   2
   3
   4
   5
   6
   7

2. UPDATE - 更新

   WITH r AS （
   	SELECT code, name
   	FROM t1
   )
   UPDATE t2 
   SET t2.name = t1.name
   FROM t1
   WHERE t1.code = t2.code;
   1
   2
   3
   4
   5
   6
   7
   8

3. SELECT - 查询

   WITH r AS （
   	SELECT code, name
   	FROM t1
   )
   SELECT t2.*
   FROM t2
   WHERE EXISTS (SELECT 1 FROM t1 where t1.code = t2.code);
   1
   2
   3
   4
   5
   6
   7

4. DELETE - 删除

   WITH r AS （
   	SELECT code, name
   	FROM t1
   )
   DELETE FROM t2
   WHERE code IN (SELECT code FROM t1);
   1
   2
   3
   4
   5
   6

5. RECURSIVE - 递归查询

   WITH RECURSIVE r AS (
       SELECT id, name, parent_id, 1 as level
       FROM organization
       WHERE parent_id IS NULL
   
       UNION ALL
   
       SELECT o.id, o.name, o.parent_id, oh.level + 1
       FROM organization o
       JOIN r oh ON o.parent_id = oh.id
   )
   SELECT id, name, level
   FROM r;
   
   1
   2
   3
   4
   5
   6
   7
   8
   9
   10
   11
   12
   13
   14

WITH在一定程度能 解决数据库查询上的一些问题，但并不是每次适合，需要对照上述的优缺点，自行判断是否需要使用。

🎉如果对你有所帮助，可以点赞、关注、收藏起来，不然下次就找不到了🎉

【点赞】⭐️⭐️⭐️⭐️⭐️
【关注】⭐️⭐️⭐️⭐️⭐️
【收藏】⭐️⭐️⭐️⭐️⭐️

Thanks for watching.
–Kenny