如何按类型选择子类别和父类别

Question

如何选择类型=1的子类别和父类别？

例如，：

电视(类型= 0) ->发光二极管(类型= 1) -> QLED (类型= 1) -> 32 LED (类型= 0)

电视(类型= 0) ->发光二极管(类型= 1) -> QLED (类型= 1) -> 24 LED (类型= 1)

电话(类型= 1) -> LG (类型= 1) -> A100 (类型= 1)

电话(类型= 1) -> LG (类型= 1) -> C300 (类型= 1)

我想得到像一样的结果

LED -> QLED

LED -> -> 24 LED

电话-> LG -> A100

电话-> LG -> C300

如果父/子不是类型=1，则返回结果不应该存在，但如果类型=1，则结果应该存在它们的子级。

SQL Fiddle

        CREATE TABLE `menu` (
      `id` int(11) NOT NULL,
      `name` varchar(255) NOT NULL,
      `main_cat` int(11) NOT NULL,
      `room` int(11) NOT NULL COMMENT ' 0 = False, 1 = True',
      `type` int(11) NOT NULL COMMENT ' 0 = False, 1 = True',
      `sort_order` int(11) NOT NULL
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8;


    INSERT INTO `menu` (`id`, `name`, `main_cat`, `room`, `type`, `sort_order`) VALUES
    (21, 'TV', 0, 1, 0,  1),
    (22, 'LED', 21,  0, 1, 1),
    (23, 'QLED', 21, 0, 1, 0),
    (24, '24inc', 23, 0, 1, 0),
    (25, '32inc', 23, 1, 0, 0),
    (26, 'Phone', 0, 0, 1, 2),
    (27, 'LG', 26, 0, 1, 2),
    (28, 'A100', 27, 0, 1, 1),
    (29, 'C300', 27, 0, 1, 1),
    (55, 'PC', 0, 1, 1, 3),
    (56, 'HP', 55, 0, 1, 2);


    ALTER TABLE `menu`
      ADD PRIMARY KEY (`id`);

        SELECT 
      m1.id AS main_id, m1.name AS main_name, 
      m2.id AS sub_id, m2.name AS sub_name, 
      m3.id AS subsub_id, m3.name AS susub_name 
    FROM menu m1
      LEFT JOIN menu m2 ON m2.main_cat = m1.id
      LEFT JOIN menu m3 ON m3.main_cat = m2.id
    WHERE m1.main_cat = 0

Answer 1

只需在内部SQL中添加where子句：

 SELECT 
      m1.id AS main_id, m1.name AS main_name, 
      m2.id AS sub_id, m2.name AS sub_name, 
      m3.id AS subsub_id, m3.name AS susub_name 
    FROM menu m1
      LEFT JOIN (SELECT * FROM menu where type=1) m2 ON m2.main_cat = m1.id
      LEFT JOIN (SELECT * FROM menu where type=1) m3 ON m3.main_cat = m2.id
    WHERE m1.main_cat = 0

或者用我的方法，如果我仔细理解

 SELECT * FROM (
 SELECT m1.name as name1, m2.name as name2 FROM menu m1
 LEFT JOIN menu m2
 on m1.id = m2.main_cat
 where m2.type = 1
) A 
left join (
 SELECT m1.name as name1, m2.name as name2 FROM menu m1
 LEFT JOIN menu m2
 on m1.id = m2.main_cat
 where m2.type = 1
) B
on A.name2 = b.name1

或者以更好的方式使用临时表

 CREATE TEMPORARY TABLE tmpTable SELECT m1.name as name1, m2.name as name2 FROM menu m1
 LEFT JOIN menu m2
 on m1.id = m2.main_cat
 where m2.type = 1;
 
 SELECT A.name1, A.name2,B.name2 FROM tmpTable A 
left join tmpTable B
on A.name2 = b.name1

EDIT==================为行添加类型限定符：

SELECT A.name1, A.name2, B.name2 FROM (
 SELECT m1.name as name1, m2.name as name2 FROM menu m1
 LEFT JOIN menu m2
 on m1.id = m2.main_cat
 where m1.type = 1 and m2.type = 1
) A 
left join (
 SELECT m1.name as name1, m2.name as name2 FROM menu m1
 LEFT JOIN menu m2
 on m1.id = m2.main_cat
) B
on A.name2 = b.name1