3、postgresql中递归\CTE\WITH\INSERT\COPY

with查询(common table expressions)

目的:简化sql减少嵌套,可理解为在查询中定义了临时表,递归查询

with查询
postgres=# with test as (select generate_series(1,5)) select * from test;
 generate_series 
-----------------
               1
               2
               3
               4
               5
(5 rows)

在这里插入图片描述

CTE递归查询

创建表并插入测试数据:表 funds 来存储基金和它们持有的子基金信息

假设我们有3个基金,其中基金2和基金3是基金1的子基金,基金3又持有基金2:

CREATE TABLE funds (
    fund_id SERIAL PRIMARY KEY,
    fund_name VARCHAR(255),
    parent_fund_id INTEGER REFERENCES funds(fund_id)
);
INSERT INTO funds (fund_name, parent_fund_id) VALUES
('基金A', NULL), -- 基金A是顶级基金
('基金B', 1),    -- 基金B是基金A的子基金
('基金C', 1);    -- 基金C也是基金A的子基金

UPDATE funds SET parent_fund_id = 3 WHERE fund_id = 2;-- 基金C持有基金B

pos
PostgreSQL中,递归CTE(Common Table Expression)是一种强大的工具,用于处理递归查询。递归CTE允许你定义一个初始查询(锚点)和一个递归查询(递归部分),然后将它们组合在一起以生成最终结果。以下是如何在PostgreSQL中使用递归CTE的步骤和示例: ### 语法 递归CTE的基本语法如下: ```sql WITH RECURSIVE cte_name (column1, column2, ...) AS ( -- 锚点查询 SELECT ... UNION [ALL] -- 递归查询 SELECT ... FROM cte_name WHERE ... ) SELECT * FROM cte_name; ``` ### 示例 假设我们有一个表示公司组织结构的表 `employees`,其中包含员工的 `id`、`name` 和 `manager_id`。我们希望查询某个员工及其所有下属员工。 ```sql CREATE TABLE employees ( id SERIAL PRIMARY KEY, name VARCHAR(100), manager_id INT ); INSERT INTO employees (name, manager_id) VALUES ('Alice', NULL), ('Bob', 1), ('Charlie', 1), ('David', 2), ('Eve', 2), ('Frank', 3); ``` 要查询 `Alice` 及其所有下属员工,可以使用以下递归CTE: ```sql WITH RECURSIVE subordinates AS ( -- 锚点查询:选择初始员工 SELECT id, name, manager_id FROM employees WHERE name = 'Alice' UNION ALL -- 递归查询:选择当前员工的所有下属 SELECT e.id, e.name, e.manager_id FROM employees e INNER JOIN subordinates s ON e.manager_id = s.id ) SELECT * FROM subordinates; ``` ### 解释 1. **锚点查询**:选择 `name` 为 `Alice` 的员工作为递归的起点。 2. **递归查询**:通过 `INNER JOIN` 将 `employees` 表与 `subordinates` CTE 连接,选择所有 `manager_id` 等于 `subordinates` 中 `id` 的员工。 3. **最终查询**:从 `subordinates` CTE 中选择所有结果。 ### 结果 执行上述查询将返回 `Alice` 及其所有下属员工: ``` id | name | manager_id ----+---------+------------ 1 | Alice | 2 | Bob | 1 3 | Charlie | 1 4 | David | 2 5 | Eve | 2 6 | Frank | 3 ``` ###
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值