请教:查询一个BOM中最底层的料号及其汇总数量的SQL怎么编写?

tsinvan · 发表于 2009/1/19 15:32:23

马上注册，结交更多好友，享用更多功能，让你轻松玩转社区。如果您注册时有任何问题请联系客服QQ： 83569622 　。

您需要登录才可以下载或查看，没有帐号？注册

x

本帖最后由 tsinvan 于 2009-1-20 16:38 编辑

BOM结构如下:
Item A  Quantity: 1PCS--Item B  Quantity 1PCS
                              --Item D  Quantity 1PCS
                              --Item C  Quantity 2PCS--Item B Quantity 1PCS
                                                                  --Item D Quantity 1PCS
                                                                  --Item E Quantity 2PCS--Item B  Quantity 5PCS
                                                                                                      --Item D  Quantity 1PCS

与BOM对应的表名为bom(item01,qty01, item02,qty02),数据存储如下:
A 1 B 1
A 1 C 2
A 1 D 1
C 1 B 1
C 1 D 1
C 1 E 2
E 1 B 5
E 1 D 3

我想通过SQL代码实现如下结果:
Item             Quantity
A                   1
B                   23
D                   15

SQL代码如何编写?  还请大侠出手帮忙.
本人在此谢过了.

chinaguy · 发表于 2009/1/19 21:29:23

一级一级向下查,合并,最后在分组求和
最好能把原数据一个例子贴出来

hunter741 · 发表于 2009/4/11 11:18:36

--取巧之作
select top 1 'a','1' from bom
union
select top 1 'b','23' from bom
union
select top 1 'd','15' from bom

hunter741 · 发表于 2009/4/11 11:19:07

--言归正传
SELECT TOP 1 item01, qty01
FROM bom
WHERE (item01 = 'A')

union all
select 'B',(t1.qty02 * t2.qty02 * t3.qty02 + T4.qty02 * T5.qty02 + T6.qty02) as qty01 from
(select qty02 from bom where item01 = 'E' and item02 = 'B') as T1,
(select qty02 from bom where item01 = 'C' and item02 = 'E') as T2,
(select qty02 from bom where item01 = 'A' and item02 = 'C') as T3,

(select qty02 from bom where item01 = 'C' and item02 = 'B') as T4,
(select qty02 from bom where item01 = 'A' and item02 = 'C') as T5,
(select qty02 from bom where item01 = 'A' and item02 = 'B') as T6

union all
select 'D',(t1.qty02 * t2.qty02 * t3.qty02 + T4.qty02 * T5.qty02 + T6.qty02) as qty01 from
(select qty02 from bom where item01 = 'E' and item02 = 'D') as T1,
(select qty02 from bom where item01 = 'C' and item02 = 'E') as T2,
(select qty02 from bom where item01 = 'A' and item02 = 'C') as T3,

(select qty02 from bom where item01 = 'C' and item02 = 'D') as T4,
(select qty02 from bom where item01 = 'A' and item02 = 'C') as T5,

(select qty02 from bom where item01 = 'A' and item02 = 'D') as T6

帐号		自动登录	找回密码
密码			注册

社区人物壹佰网重点话题	分享知识原创连载活动正式启动已有673人参加查看全部会员靓照旅行分享文学连载	壹佰读书会开始招收成员了
招聘求职壹佰网信息化选型平台	倾听用户的心声纵横四海谈IT如何提升企业管理管理小说连载《苏州桥》	如何获取努力值
原创连载－我来管管看 ERP分享+	推荐连载：[已结帖][分享知识原创连载]财务供应链项目新手实施手记：记录自己正	入门学习-致所有初学者

请教:查询一个BOM中最底层的料号及其汇总数量的SQL怎么编写?

马上注册，结交更多好友，享用更多功能，让你轻松玩转社区。如果您注册时有任何问题请联系客服QQ： 83569622 。

浏览过的版块

马上注册，结交更多好友，享用更多功能，让你轻松玩转社区。如果您注册时有任何问题请联系客服QQ： 83569622 　。