关于#sql#的问题：请问一个SQL写法，SQL如何根据原始表单号拆分出需求样式
请问一个SQL写法，如图，SQL如何根据原始表单号拆分出需求样式？
报歉，没描述清楚
拆分固定以1 为单位，拆分 * 每笔标准 =每笔重量
原始表该记录笔数2.3就拆分为3条，最后一条 为 0.3*40.575=12.1725
这样？如果有每笔重量，那就直接乘，没有的话就利用总重量除以笔数求出来也是一样
SELECT dd.单号 , ROWNUM 顺序, case
when (dd.笔数-ROWNUM)>0 then 1 else dd.笔数-ROWNUM+1 end AS 拆分,
case
when (dd.笔数-ROWNUM)>0 then 1 else dd.笔数-ROWNUM+1 end *dd.每笔标重 每笔重量
FROM dd where name='1'
CONNECT BY ROWNUM <=
(select ceil(dd.笔数) from dd where 单号='M001');
引用ChatGPT部分内容参考建议：
-- 创建原始表
CREATE TABLE original_table (
order_no VARCHAR(10),
quantity FLOAT,
standard FLOAT,
total_weight FLOAT
);
-- 插入原始数据
INSERT INTO original_table (order_no, quantity, standard, total_weight)
VALUES ('M001', 2.3, 40.575, 93.3225);
-- 创建目标表
CREATE TABLE target_table (
order_no VARCHAR(10),
sequence INT,
split FLOAT,
weight FLOAT
);
-- 使用循环语句和数学计算函数拆分数据并插入目标表
DECLARE @order_no VARCHAR(10);
DECLARE @quantity FLOAT;
DECLARE @standard FLOAT;
DECLARE @total_weight FLOAT;
DECLARE @i INT = 1;
DECLARE @split FLOAT;
DECLARE @weight FLOAT;
DECLARE cursor_name CURSOR FOR
SELECT order_no, quantity, standard, total_weight
FROM original_table;
OPEN cursor_name;
FETCH NEXT FROM cursor_name INTO @order_no, @quantity, @standard, @total_weight;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @split = FLOOR(@quantity);
SET @weight = @standard * @split;
WHILE @split < @quantity
BEGIN
INSERT INTO target_table (order_no, sequence, split, weight)
VALUES (@order_no, @i, 1, @standard);
SET @i = @i + 1;
SET @split = @split + 1;
SET @weight = @standard;
END
INSERT INTO target_table (order_no, sequence, split, weight)
VALUES (@order_no, @i, @quantity - FLOOR(@quantity), @standard * (@quantity - FLOOR(@quantity)));
SET @i = 1;
FETCH NEXT FROM cursor_name INTO @order_no, @quantity, @standard, @total_weight;
END
CLOSE cursor_name;
DEALLOCATE cursor_name;
-- 查询目标表
SELECT * FROM target_table;
该代码使用了游标（CURSOR）来遍历原始表中的每一条记录，并使用循环语句和数学计算函数来拆分数据并插入目标表中。最后，查询目标表即可得到拆分后的数据
- 这篇博客: 【SQL开发实战技巧】系列（三十一）：数仓报表场景☞分层查询如何只查询树形结构某一个分支？如何剪掉一个分支？中的 二、如何正确的剪去一个分支 部分也许能够解决你的问题, 你可以仔细阅读以下内容或跳转源博客中阅读:
接上面的案例:
SELECT lpad('->', (level - 1) * 2, '->') || empno AS 员工编码, ename AS 姓名, mgr AS 主管编码, (PRIOR ename) AS 主管姓名, level as 级别, decode(level, 1, 1) as 根节点, decode(connect_by_isleaf, 1, 1) as 叶子节点, CASE WHEN (connect_by_isleaf = 0 AND LEVEL > 1) THEN 1 END AS 分支节点, deptno as 部门编码, sys_connect_by_path(ename, '->') as enames FROM emp START WITH mgr is null CONNECT BY (PRIOR empno) = mgr order siblings by empno; 员工编码 姓名 主管编码 主管姓名 级别 根节点 叶子节点 分支节点 部门编码 ENAMES -------------------------------------------------------------------------------- ---------- ----- ---------- ---------- ---------- ---------- ---------- ---- -------------------------------------------------------------------------------- 7839 KING 1 1 10 ->KING ->7566 JONES 7839 KING 2 1 20 ->KING->JONES ->->7788 SCOTT 7566 JONES 3 1 20 ->KING->JONES->SCOTT ->->->7876 ADAMS 7788 SCOTT 4 1 20 ->KING->JONES->SCOTT->ADAMS ->->7902 FORD 7566 JONES 3 1 20 ->KING->JONES->FORD ->->->7369 SMITH 7902 FORD 4 1 20 ->KING->JONES->FORD->SMITH ->7698 BLAKE 7839 KING 2 1 30 ->KING->BLAKE ->->7499 ALLEN 7698 BLAKE 3 1 30 ->KING->BLAKE->ALLEN ->->7521 WARD 7698 BLAKE 3 1 30 ->KING->BLAKE->WARD ->->7654 MARTIN 7698 BLAKE 3 1 30 ->KING->BLAKE->MARTIN ->->7844 TURNER 7698 BLAKE 3 1 30 ->KING->BLAKE->TURNER ->->7900 JAMES 7698 BLAKE 3 1 30 ->KING->BLAKE->JAMES ->7782 CLARK 7839 KING 2 1 10 ->KING->CLARK ->->7934 MILLER 7782 CLARK 3 1 10 ->KING->CLARK->MILLER 14 rows selected现在有个新的需求：要求剪去7698开始的这个分支。
同样，剪去分支也不能在WHERE中加条件，因为树形查询递归是根据条件(PRIOR empno)=mgr进行的，所以在下列语句加条件就可以。SELECT lpad('->', (level - 1) * 2, '->') || empno AS 员工编码, ename AS 姓名, mgr AS 主管编码, (PRIOR ename) AS 主管姓名, level as 级别, decode(level, 1, 1) as 根节点, decode(connect_by_isleaf, 1, 1) as 叶子节点, CASE WHEN (connect_by_isleaf = 0 AND LEVEL > 1) THEN 1 END AS 分支节点, deptno as 部门编码, sys_connect_by_path(ename, '->') as enames FROM emp START WITH mgr is null CONNECT BY (PRIOR empno) = mgr and empno !=7698 order siblings by empno; 员工编码 姓名 主管编码 主管姓名 级别 根节点 叶子节点 分支节点 部门编码 ENAMES -------------------------------------------------------------------------------- ---------- ----- ---------- ---------- ---------- ---------- ---------- ---- -------------------------------------------------------------------------------- 7839 KING 1 1 10 ->KING ->7566 JONES 7839 KING 2 1 20 ->KING->JONES ->->7788 SCOTT 7566 JONES 3 1 20 ->KING->JONES->SCOTT ->->->7876 ADAMS 7788 SCOTT 4 1 20 ->KING->JONES->SCOTT->ADAMS ->->7902 FORD 7566 JONES 3 1 20 ->KING->JONES->FORD ->->->7369 SMITH 7902 FORD 4 1 20 ->KING->JONES->FORD->SMITH ->7782 CLARK 7839 KING 2 1 10 ->KING->CLARK ->->7934 MILLER 7782 CLARK 3 1 10 ->KING->CLARK->MILLER 8 rows selected