SQL常用算法-递归表达式

当前位置：点晴教程→知识管理交流 →『技术文档交流』

admin

2024年3月30日 12:51 本文热度 471

语法函数

with as

with as 在之前的章节中已经用到，但是当时只是用于创建临时表使用，更大的作用在于组合成为递归表达式，分解层级。

常见场景

一、制造业中，BOM是所有成本分析的底层，BOM表通常由自身ID，父级ID等元素构成的层级表，通常我们需要获得所有的末级元素，用于计算整体的材料费用等

二、对于权限分配表，部门主数据也为带有层级关系的层级表，通常我们需要对已知的部门查询他的最上级部门，以此确认权限

在上面两种常见当中，数据结构都是以自身/上级的ID层级关系体现，无论是通过顶层查询底层还是通过底层查询顶层，使用with as递归表达式都能轻松获取我们所需要的数据。

示例

1.已知用户部门，查询他的最上级部门

WITH RECURSIVE DepartmentHierarchy AS (
    SELECT
        dept_id,
        parent_dept_id,
        0 AS level
    FROM
        departments
    WHERE
        dept_id = :input_dept_id  -- 指定要查询的部门ID
    UNION ALL
    SELECT
        d.dept_id,
        d.parent_dept_id,
        dh.level + 1
    FROM
        departments d
    INNER JOIN
        DepartmentHierarchy dh ON d.dept_id = dh.parent_dept_id
)
SELECT
    dept_id,
    parent_dept_id
FROM
    DepartmentHierarchy
WHERE
    level = (SELECT MAX(level) FROM DepartmentHierarchy);

2.已知产品编码，获得所有最下级材料

WITH RECURSIVE ProductMaterials AS (

    SELECT

        product_id,

        material_id,

        1 AS level

    FROM

        bom_table

    WHERE

        parent_material_id IS NULL  -- 找到最上级产品
 
     UNION ALL
 
     SELECT

        b.product_id,

        b.material_id,

        pm.level + 1

    FROM

        bom_table b

    INNER JOIN

        ProductMaterials pm ON b.parent_material_id = pm.material_id)

SELECT DISTINCT

    product_id,

    material_id

FROM

    ProductMaterials

WHERE

    (product_id, level) IN (

        SELECT

            product_id,

            MAX(level) AS max_level

        FROM

            ProductMaterials

        GROUP BY

            product_id

);

该文章在 2024/3/30 12:51:21 编辑过

关键字查询

常用

sql

正在查询...

点晴ERP是一款针对中小制造业的专业生产管理软件系统,系统成熟度和易用性得到了国内大量中小企业的青睐。

点晴PMS码头管理系统主要针对港口码头集装箱与散货日常运作、调度、堆场、车队、财务费用、相关报表等业务管理，结合码头的业务特点，围绕调度、堆场作业而开发的。集技术的先进性、管理的有效性于一体，是物流码头及其他港口类企业的高效ERP管理信息系统。

点晴WMS仓储管理系统提供了货物产品管理,销售管理,采购管理,仓储管理,仓库管理,保质期管理,货位管理,库位管理,生产管理,WMS管理系统,标签打印,条形码,二维码管理,批号管理软件。

点晴免费OA是一款软件和通用服务都免费，不限功能、不限时间、不限用户的免费OA协同办公管理系统。