<p>这将比您的示例中深入3个级别,但也适用于级别较少的产品,如您的示例中所示。如果需要,可以添加到查询中(如果任何产品的级别超过4级):</p>
<p><strong>小提琴演示:</strong><a href="http://sqlfiddle.com/#!2/681368/1/0" rel="nofollow">http://sqlfiddle.com/#!2/681368/1/0</a></p>
<pre><code>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
</code></pre>
<p>要为每个零件生成一行,而不是聚合列表(这是您在问题中作为预期输出输入的内容),可以创建一个视图来取消数据规范化:</p>
<pre><code>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;
</code></pre>
<p>然后运行:</p>
<pre><code>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
</code></pre>
<p>小提琴:</strong><a href="http://sqlfiddle.com/#!2/bd5a4/4/0" rel="nofollow">http://sqlfiddle.com/#!2/bd5a4/4/0</a></p>