时间戳

首页 > 解决方案 > 如何按类型选择子类别和父类别

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

问题描述

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

例如：

电视（类型 = 0）-> LED（类型 = 1）-> QLED（类型 = 1）-> 32inc（类型 = 0）

电视（类型 = 0）-> LED（类型 = 1）-> QLED（类型 = 1）-> 24inc（类型 = 1）

电话（类型 = 1）-> LG（类型 = 1）-> A100（类型 = 1）

电话（类型 = 1）-> LG（类型 = 1）-> C300（类型 = 1）

我希望得到类似的结果

LED -> QLED

LED -> QLED -> 24inc

手机 -> LG -> A100

手机 -> LG -> C300

如果父/子不是 type = 1，则返回结果中不应存在，但如果 type = 1，则其子应存在于结果中

SQL小提琴

        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

标签： mysql

解决方案

只需在内部 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

编辑=================== 为行添加类型限定符：

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

推荐阅读