[MySQL] 再帰処理

再帰処理

パンくずリストや製品と部品の所属をたどっていくときとか。
例えば、自転車＞部品＞ペダルなどと表示される。

SELECT
IFNULL(LV3.id,IFNULL(LV2.id, LV1.id)) as id,
LV3.name,
concat_ws(" > ",LV1.name , LV2.name , LV3.name ) as panName
FROM
system_sections as LV1
LEFT OUTER JOIN
system_sections as LV2 ON LV2.parent=LV1.id
LEFT OUTER JOIN
system_sections as LV3 ON LV3.parent=LV2.id
where
LV3.active <> 0
group by
id
order by
LV3.`index` ASC,
id ASC

別の方法

SELECT * FROM(
SELECT
IFNULL(t4.id, IFNULL(t3.id, IFNULL(t2.id, t1.id))) as id,
concat_ws(" > ", t1.name, t2.name, t3.name, t4.name) as name,
IF(t4.id, 4, IF(t3.id, 3, IF(t2.id, 2, 1))) as cnt,
t1.id  as code1,
t1.name as name1,
IFNULL(t2.id,  null) as code2,
IFNULL(t2.name, null) as name2,
IFNULL(t3.id,  null) as code3,
IFNULL(t3.name, null) as name3,
IFNULL(t4.id,  null) as code4,
IFNULL(t4.name, null) as name4
FROM
`beta_system_sections` AS t1
LEFT JOIN
`beta_system_sections` AS t2 ON t2.parent = t1.id
LEFT JOIN
`beta_system_sections` AS t3 ON t3.parent = t2.id
LEFT JOIN
`beta_system_sections` AS t4 ON t4.parent = t3.id
WHERE
t1.`active` = 1
AND
t1.parent = 0
ORDER BY
t4.`index` asc,
t3.`index` asc,
t2.`index` asc,
t1.`index` asc
) as TEST