我在计算sql查询时遇到了问题。。。
我在用两张table工作。第一个是套件结构(k),其中通常给定的套件具有多个组件(例如,套件k1具有组件k1\u a和k1\u b,套件k2具有组件k2\u a、k2\u b、k2\u c等):
+-----+------+
| Kit | Comp |
+-----+------+
| K1 | K1_A |
| K1 | K1_B |
| K2 | K2_A |
| K2 | K2_B |
| K2 | K2_C |
| K3 | K4 |
| K4 | K4_A |
| … | … |
+-----+------+
(k简化显示,实际表有许多附加列和套件/组件,未订购)
注:表格由原来更新。为了澄清,这里需要注意的一点是,套件通常是多级的,例如,k3(套件)包含组件k4(comp),而组件k4(comp)又与组件k4\u a在k.kit中,等等。
第二个表将所有项目属性(i)(成本、现有库存等)作为套件和组件的行项目:
+------+------------+------------+
| Item | Attr1 | Attr2 |
+------+------------+------------+
| K1 | (for K1) | (for K1) |
| K1_A | (for K1_A) | (for K1_A) |
| K1_B | (for K1_B) | (for K1_B) |
| K2 | (for K2) | (for K2) |
| K2_A | (for K2_A) | (for K2_A) |
| K2_B | (for K2_B) | (for K2_B) |
| K2_C | (for K2_C) | (for K2_C) |
| K3 | (for K3) | (for K3) |
| K4 | (for K4) | (for K4) |
| K4_A | (for K4_A) | (for K4_A) |
| … | … | … |
+------+------------+------------+
(为了便于参考,我同样以简化形式表示)
我正在尝试连接这些表,以便输出kit结构,显示与kit组件相关的相应属性,即以下所需结果:
+-----+------+------+------------+------------+
| Kit | Comp | Item | Attr1 | Attr2 |
+-----+------+------+------------+------------+
| K1 | K1_A | K1_A | (for K1_A) | (for K1_A) |
| K1 | K1_B | K1_B | (for K1_B) | (for K1_B) |
| K2 | K2_A | K2_A | (for K2_A) | (for K2_A) |
| K2 | K2_B | K2_B | (for K2_B) | (for K2_B) |
| K2 | K2_C | K2_C | (for K2_C) | (for K2_C) |
| K3 | K4 | K4 | (for K4) | (for K4) |
| K4 | K4_A | K4_A | (for K4_A) | (for K4_A) |
| … | … | … | … | … |
+-----+------+------+------------+------------+
(注意:我只是寻找相对于套件的一个级别(即,套件lvl0到comp lvl1),而不是完整的递归bom深度)
我尝试的sql查询如下:
SELECT K.Kit, K.Comp, I.Item, I.Atrr1, I.Attr2
FROM K
JOIN I
ON K.Kit = I.Item
但是,这是不起作用的,因为它是通过套件的(重复的)属性而不是组件连接的,即以下不正确的输出:
+-----+------+------+----------+----------+
| Kit | Comp | Item | Attr1 | Attr2 |
+-----+------+------+----------+----------+
| K1 | K1_A | K1 | (for K1) | (for K1) |
| K1 | K1_B | K1 | (for K1) | (for K1) |
| K2 | K2_A | K2 | (for K2) | (for K2) |
| K2 | K2_B | K2 | (for K2) | (for K2) |
| K2 | K2_C | K2 | (for K2) | (for K2) |
| K3 | K4 | K3 | (for K3) | (for K3) |
| K4 | K4_A | K4 | (for K4) | (for K4) |
| … | … | … | … | … |
+-----+------+------+----------+----------+
任何建议都将不胜感激。谢谢你的时间!
更新:添加了格式化表和mysql标记
1条答案
按热度按时间6pp0gazn1#
看来你只是想加入公司?