问题背景:近期我碰到一个比较有趣的问题，就是我需要将t_supply_auth表（图一）的每一条记录根据项目id拆分为多条记录展示，结果如图二展示
图一
图二
完成以上要求的整体sql如下：
首先分析下递归公用表表达式（CTE）：
with recursive numbers as ( select 1 as n union all select n + 1 from numbers where n < 1000 )
详细解释
-
with recursive关键字：with关键字用于定义公用表表达式（CTE）。recursive关键字表示这个 CTE 是递归的，它会自我引用直到满足终止条件。
-
numbers as (...)：numbers是这个递归 CTE 的名称，可以在后续查询中引用。as后面跟的是 CTE 的定义。
-
初始查询
select 1 as n：- 这是递归 CTE 的锚部分（anchor part），只在第一次运行。
- 它返回一个包含单个值
1的行，并且将其别名为n。
-
递归查询
union all select n + 1 from numbers where n < 1000：union all将锚部分的结果与递归部分的结果合并。select n + 1 from numbers从 CTEnumbers中选择当前的值n并加 1。where n < 1000是递归的终止条件，当n达到 1000 时递归停止。
工作过程
-
首先，锚部分执行，生成一个包含值
1的行。 -
然后，递归部分使用锚部分生成的结果进行下一次递归：
- 从
numbers中选择n，其初始值为1，并生成n + 1（即2）。 - 继续执行递归查询，生成
3，4，直到999。
- 从
-
递归继续进行，直到
n达到1000时终止，因为where n < 1000这个条件不再满足。
最终结果
这个递归 CTE 将生成一个包含从 1 到 1000 的整数序列：
substring_index函数
1. 概述
substring_index函数用于按照指定分隔符截取字符串
2. 格式
substring_index(str, delimiter, number)
返回从字符串str的第number个出现的分隔符delimiter之前的子串;
如果number是正数，那么就是从左往右数，返回第number个分隔符的左边的全部内容;
相反，如果number是负数，那么就是从右边开始数，第number个分隔符右边的所有内容
注意:如果number超过了实际分隔符的个数，则返回实际个数的字符串
3. 参数说明
str: 待截取的字符串,string类型
delimiter: 分隔符,string类型
number: 指定分隔符位置,int类型
4. 返回值
返回STRING类型字符串,如果任一输入参数为NULL，则返回NULL
所以substring_index(substring_index(ta.auth_project_id,',',numbers.n),',',-1)的含义是：先将数据库字段根据逗号分隔从左往右数截取第n个逗号左边的部分，然后再将截取后的字符串根据逗号分隔截取右边的所有部分。这样的话每次都能将第一层函数嵌套截取的字符串的最后一个子串截取出来。就达到了我们的目的。
整体sql说明：
1. CTE（公用表表达式）定义
- numbers CTE: 创建了一个递归公用表表达式，用于生成从1到1000的数字序列。这个序列在后续查询中用来分割字符串字段，如auth_project_id和auth_project_name。
- project_parts CTE:
- 数据来源: 从t_supply_auth表中选取基础数据，并与numbers、t_supply、sys_user和sys_dept表进行关联，以获取供应商信息、用户信息及部门信息。
- 字段处理:
- 对auth_id_cation（授权ID）和contact_phone（联系人电话）进行了脱敏处理，只显示前三位和后四位，中间用星号隐藏。
- 使用substring_index函数配合numbers表，将包含逗号分隔的字符串（如项目ID和名称）分割成单独的部分。
- 左连接: 通过左连接sys_user表和sys_dept表，获取用户和部门信息，且这些表的连接基于特定条件（如用户状态和部门ID）。
2. 查询条件与动态SQL
- 基本条件: 确保supplyId、authProjectId和authProjectName不为NULL。
- 动态SQL条件:
- 根据传入请求对象req中的属性（如authName、contactPhone、authIdCation、providerName、deptId和projectId），动态添加到WHERE子句中，实现灵活的查询过滤。
- 特别地，如果hasPower参数为false，则额外检查当前用户是否存在于project_user表中，以验证用户是否有权限查看指定的项目授权记录。
3. 排序与输出
- 排序: 最终结果按authTime（授权时间）降序排列，确保最新的授权记录优先展示。
- 结果映射: 查询结果被映射到Java实体类com.minp.system.domain.vo.api.supply.ProjectAuthorizationListResp，以便于在业务逻辑层直接使用。
