关于BOM表的数据结构和sql查询

simia

目前要做个系统，里面有个功能是根据BOM表计算成品用料，因为数据量比较大所以数据源放在了sqlserver数据库里，但是在设计数据结构和sql查询的时候有点不知道怎么下手了。

目前设想的基础表有三张

商品表
商品ID，商品名称，配方ID
1           A商品        1
2           B商品        2
3           C商品        3
4           D商品        3

配方表
配方ID，配方名称
1           A配方        
2           B配方        
3           C配方        

原料表
原料ID，原料名称
1           a原料
2           b原料
3           c原料
4           d原料

BOM表
配方ID, 用料ID，用料占比，用料分类
1          1           0.2            原料
1          2           0.8            原料
2          4           0.5            原料
2          1           0.5            配方
3          3           0.2            原料
3          2           0.3            配方
3          1           0.5            配方

大体类似这种结构，一个成品可能全由原料构成，也可能由其他已有配方组成，其中每个配方有可能还由其他配方组成，用料ID可能是对应原料ID，也可能对应配方ID，根据用料分类字段判断。如果这个嵌套数不确定的话，怎么用sql语句查出来最底层原料的组成？求大神指点！

zpy2

https://club.excelhome.net/thread-1549965-1-1.html?_dsign=315b1ee1
递归查询

simia

就是我需要的！感谢老师！！

盼盼198310

这类问题放到access中解决会有意想不到的效果

melville

把商品表，配方表，原料表都建在同一个表，用物料分类来区分，那样bom表就只要父子结构，那样会不会更简单一点。附件是BOM表组成和展开函数，楼主可以参考。

opiona

这个BOM设计比较有意思
一个最终产品 有N个配方, 就是N种BOM
半成品也是N种BOM
问题: 我们可以指定最终产品使用哪个配方
但是怎么指定半成品用哪一个配方呢??

如果都是单一配方, 则可用递归
但是多种配方, 怎么保证尽量使用有库存的原料呢?
是不是应该指定配方的优先级, 保证利益最大化!?

simia

opiona 发表于 2022-9-17 22:12
这个BOM设计比较有意思
一个最终产品 有N个配方, 就是N种BOM
半成品也是N种BOM

老师说的的确是，不过这个问题应该是我设计的不规范导致的，实际业务目前没有出现一种半成品对应N种配方，如果这种情况，那就应该是不同的半成品，然后使用不同的半成品生产出来的也应该是不同的产品，因为成本肯定是不一样的了。最后我修改了结构bug以后用的递归，果然宇宙的尽头是数学T T

simia

melville 发表于 2022-9-17 16:50
把商品表，配方表，原料表都建在同一个表，用物料分类来区分，那样bom表就只要父子结构，那样会不会更简单 ...

太感谢了！不好意思得到版主链接之后我就埋头研究去了，早点看见老师发的我就不用自己研究的那么痛苦了，感谢感谢！！

simia

opiona 发表于 2022-9-17 22:12
这个BOM设计比较有意思
一个最终产品 有N个配方, 就是N种BOM
半成品也是N种BOM

老师说的的确是，不过这个问题应该是我设计的不规范导致的，实际业务目前没有出现一种半成品对应N种配方，如果这种情况，那就应该是不同的半成品，然后使用不同的半成品生产出来的也应该是不同的产品，因为成本肯定是不一样的了。最后我修改了bug以后用的递归，果然宇宙的尽头是数学T T

opiona

BOM+库存  可以参考:https://club.excelhome.net/thread-1585630-1-1.html