sql查询帮助-kit结构连接到属性

x6yk4ghg  于 2021-06-20  发布在  Mysql
关注(0)|答案(1)|浏览(272)

我在计算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标记

6pp0gazn

6pp0gazn1#

看来你只是想加入公司?

SELECT K.Kit, K.Comp, I.Item, I.Atrr1, I.Attr2
FROM K
JOIN I
ON K.comp = I.Item

相关问题