MySQL选择产品的所有组件

Question

我在一家汽车公司工作。我们的产品有一张桌子，上面有产品的组成部分。你知道吗

这是我们的一个产品的树视图。产品的零件通过装配操作制成E40。

这是MySQL数据库的表视图：

我们想对产品的所有组成部分得出一个结果。像这样：

'E40-1', 'E40-2', 'E40-3', 'E40-4', 'E40-5', 'E40-6', 'E40-6-1', 'E40-6-2'

所以结果的长度必须是8。你知道吗

我尝试过一些MySQL查询，但都不起作用。你知道吗

我尝试的查询：

select alt_bilesen from urunler_seviyeler where parcano in (select parcano from urunler_seviyeler where parcano="E40")

这只给了我们产品的六部分。无E40-6-1和E40-6-2。 我也试过：

select alt_bilesen from urunler_seviyeler where parcano in (select alt_bilesen from urunler_seviyeler where parcano="E40")

这只给了我们产品最低的部分。E40-6-1和E40-6-2。不是其他部分。你知道吗

顺便说一下，我们可以用Python。如果我们想使用Python，我想我们必须用for循环、while循环等来调用它。你能给我一个建议吗？非常感谢。你知道吗

Answer 1

这将比您的示例中深入3个级别，但也适用于级别较少的产品，如您的示例中所示。如果需要，可以添加到查询中（如果任何产品的级别超过4级）：

小提琴演示：http://sqlfiddle.com/#!2/681368/1/0

select t.parcano,
       trim(both ', ' from
            concat(group_concat(distinct t.alt_bilesen order by
                                t.alt_bilesen separator ', '),
                   ', ',
                   ifnull(concat(group_concat(distinct l1.alt_bilesen order by
                                              l1.alt_bilesen separator ', '),
                                 ', '),
                          ''),
                   ', ',
                   ifnull(concat(group_concat(distinct l2.alt_bilesen order by
                                              l2.alt_bilesen separator ', '),
                                 ', '),
                          ''),
                   ', ',
                   ifnull(concat(group_concat(distinct l3.alt_bilesen order by
                                              l3.alt_bilesen separator ', '),
                                 ', '),
                          ''),
                   ', ',
                   ifnull(concat(group_concat(distinct l4.alt_bilesen order by
                                              l4.alt_bilesen separator ', '),
                                 ', '),
                          '')

                   )) as parts
  from urunler_seviyeler t
  left join urunler_seviyeler x
    on x.alt_bilesen = t.parcano
  left join urunler_seviyeler l1
    on t.alt_bilesen = l1.parcano
  left join urunler_seviyeler l2
    on l1.alt_bilesen = l2.parcano
  left join urunler_seviyeler l3
    on l2.alt_bilesen = l3.parcano
  left join urunler_seviyeler l4
    on l3.alt_bilesen = l4.parcano
 where x.parcano is null
 group by t.parcano

要为每个零件生成一行，而不是聚合列表（这是您在问题中作为预期输出输入的内容），可以创建一个视图来取消数据规范化：

create denorm_vw as
select t.parcano,
       t.alt_bilesen as l0,
       l1.alt_bilesen as l1,
       l2.alt_bilesen as l2,
       l3.alt_bilesen as l3,
       l4.alt_bilesen as l4
  from urunler_seviyeler t
  left join urunler_seviyeler x
    on x.alt_bilesen = t.parcano
  left join urunler_seviyeler l1
    on t.alt_bilesen = l1.parcano
  left join urunler_seviyeler l2
    on l1.alt_bilesen = l2.parcano
  left join urunler_seviyeler l3
    on l2.alt_bilesen = l3.parcano
  left join urunler_seviyeler l4
    on l3.alt_bilesen = l4.parcano
 where x.parcano is null;

然后运行：

select parcano, l0 from denorm_vw union 
select parcano, l1 from denorm_vw where l1 is not null union 
select parcano, l2 from denorm_vw where l2 is not null union 
select parcano, l3 from denorm_vw where l3 is not null union 
select parcano, l4 from denorm_vw where l4 is not null

小提琴：http://sqlfiddle.com/#!2/bd5a4/4/0

Answer 2

我建议使用零件号本身：

select alt_bilesen
from urunler_seviyeler
where CONCAT(parcano, '-') like 'E40-%'

CONCAT()防止E400与E40混淆。你知道吗

否则，您需要开始使用joins。如果子部分可以嵌套得很深，那么您就有问题了。MySQL不支持查询分层或嵌套的数据结构。你知道吗

Answer 3

MySQL不支持“分层”查询。可以使用多个查询在有限的级别上模拟该功能。查询的结果可以与UNION ALL操作结合使用。你知道吗

根据需要返回行的实际条件，获取按特定顺序返回的行可能会有问题。你知道吗

第一级：

SELECT t1.alt_bilesen
  FROM urunler_seviyeler t1 
 WHERE t1.parcano = 'E40'

第二级：

SELECT t2.alt_bilesen
  FROM urunler_seviyeler t1
  JOIN urunler_seviyeler t2
    ON t2.parcano = t1.alt_bilesen
 WHERE t1.parcano = 'E40'

第三级：

SELECT t3.alt_bilesen
  FROM urunler_seviyeler t1
  JOIN urunler_seviyeler t2 ON t2.parcano = t1.alt_bilesen
  JOIN urunler_seviyeler t3 ON t3.parcano = t2.alt_bilesen
 WHERE t1.parcano = 'E40'

第四级：

SELECT t4.alt_bilesen
  FROM urunler_seviyeler t1
  JOIN urunler_seviyeler t2 ON t2.parcano = t1.alt_bilesen
  JOIN urunler_seviyeler t3 ON t3.parcano = t2.alt_bilesen
  JOIN urunler_seviyeler t4 ON t4.parcano = t3.alt_bilesen
 WHERE t1.parcano = 'E40'

可以将查询与UNION ALLset运算符组合

( SELECT t1.alt_bilesen
    FROM urunler_seviyeler t1 
   WHERE t1.parcano = 'E40'
)
UNION ALL
( SELECT t2.alt_bilesen
    FROM urunler_seviyeler t1
    JOIN urunler_seviyeler t2
      ON t2.parcano = t1.alt_bilesen
   WHERE t1.parcano = 'E40'
)
UNION ALL
( SELECT t3.alt_bilesen
   FROM urunler_seviyeler t1
   JOIN urunler_seviyeler t2 ON t2.parcano = t1.alt_bilesen
   JOIN urunler_seviyeler t3 ON t3.parcano = t2.alt_bilesen
  WHERE t1.parcano = 'E40'
) 
UNION ALL
( SELECT t4.alt_bilesen
    FROM urunler_seviyeler t1
    JOIN urunler_seviyeler t2 ON t2.parcano = t1.alt_bilesen
    JOIN urunler_seviyeler t3 ON t3.parcano = t2.alt_bilesen
    JOIN urunler_seviyeler t4 ON t4.parcano = t3.alt_bilesen
   WHERE t1.parcano = 'E40'
)
ORDER BY 1

---

通过在每个查询中包含一个文本值，可以包含一个额外的“level”列。若要按返回列以外的内容进行排序，您需要在每个查询中包含一些附加表达式。。。你知道吗