C#或SQL 栈板拼箱算法

Question

订单数据
订单号(DocNum) 周期码(LotNum) 数量(LotQty)
2201-2023030301-0001 2305 10000
2201-2023030301-0002 2306 15000
2201-2023030301-0003 2307 6000

每层6箱，每箱500
输出：产品清单
层码LevelCode 序号 Seq 层箱 BoxNum 周期码LotNum 每箱数量 BoxQty 订单号(DocNum)
1 1 6 2305 500 2201-2023030301-0001
2 2 6 2305 500 2201-2023030301-0001
3 3 6 2305 500 2201-2023030301-0001
4 4 2 2305 500 2201-2023030301-0001
4 5 4 2306 500 2201-2023030301-0002
5 6 6 2306 500 2201-2023030301-0002
6 7 6 2306 500 2201-2023030301-0002
7 8 6 2306 500 2201-2023030301-0002
8 9 6 2306 500 2201-2023030301-0002
9 10 2 2306 500 2201-2023030301-0002
9 11 4 2307 500 2201-2023030301-0003
10 12 6 2307 500 2201-2023030301-0003
11 13 2 2307 500 2201-2023030301-0003

实体：

    public class SalesOrder 
    {
        public string DocNum { get; set; }
        public string LotNum { get; set; }
        public int LotQty { get; set; }
    }
    public class Prd
    {
        public int LevelCode { get; set; }
public int Seq { get; set; }
        public int BoxNum { get; set; }
        public string LotNum { get; set; }
        public int BoxQty { get; set; }
        public string DocNum { get; set; }

    }

Answer 1

来挑战 GPT 了

mssql 用 cte 完成

;with t as (    -- 获取原始数据
    select '2201-2023030301-001' DocNum,2305 LotNum,10000 LotQty
    union all select '2201-2023030301-002',2306,15000
    union all select '2201-2023030301-003',2307,6000
),t1 as ( -- 计算可以分成多少箱
    select *,500 as BoxQty from t
),t2 as ( -- 对各类货品的箱子进行展开，分成每箱一行数据
    select *,LotQty / BoxQty as nums 
    from t1 a
    cross apply (
        select number from master..spt_values
        where type='p' and number between 1 and LotQty / BoxQty
    ) b
),t3 as ( -- 对已经分开的箱子，都进行编号
    select DocNum,LotNum,LotQty,BoxQty
        ,row_number() over(order by getdate()) boxNum 
    from t2
),t4 as ( -- 对编号进行除6运算，表示每六个一层
    select *,convert(int,(boxNum - 1) / 6) + 1 LevelCode 
    from t3
) -- 最后，分组统计一下就可以
select DocNum,LotNum,LotQty,BoxQty,LevelCode,count(0) as BoxNum
    ,row_number() over(order by getdate()) Seq 
from t4
group by DocNum,LotNum,LotQty,BoxQty,LevelCode