mysql子节点级联查询_MySQL-mysql 子父节点级联查询

不知道我理解的对不对，按我理解的这个两个left join就可以了吧，例子：

CREATE TABLE category(

category_id INT AUTO_INCREMENT PRIMARY KEY,

name VARCHAR(20) NOT NULL,

parent INT DEFAULT NULL

);

INSERT INTO category VALUES(1,'ELECTRONICS',NULL),(2,'TELEVISIONS',1),(3,'TUBE',2),

(4,'LCD',2),(5,'PLASMA',2),(6,'PORTABLE ELECTRONICS',1),(7,'MP3 PLAYERS',6),(8,'FLASH',7),

(9,'CD PLAYERS',6),(10,'2 WAY RADIOS',6);

SELECT * FROM category ORDER BY category_id;

+-------------+----------------------+--------+

| category_id | name | parent |

+-------------+----------------------+--------+

| 1 | ELECTRONICS | NULL |

| 2 | TELEVISIONS | 1 |

| 3 | TUBE | 2 |

| 4 | LCD | 2 |

| 5 | PLASMA | 2 |

| 6 | PORTABLE ELECTRONICS | 1 |

| 7 | MP3 PLAYERS | 6 |

| 8 | FLASH | 7 |

| 9 | CD PLAYERS | 6 |

| 10 | 2 WAY RADIOS | 6 |

+-------------+----------------------+--------+

SELECT t1.name AS parentName, t2.name as myName, t3.name as childName

FROM category AS t1

LEFT JOIN category AS t2 ON t2.parent = t1.category_id

LEFT JOIN category AS t3 ON t3.parent = t2.category_id

+----------------------+----------------------+--------------+

| parentName | myName | childName |

+----------------------+----------------------+--------------+

| ELECTRONICS | TELEVISIONS | TUBE |

| ELECTRONICS | TELEVISIONS | LCD |

| ELECTRONICS | TELEVISIONS | PLASMA |

| ELECTRONICS | PORTABLE ELECTRONICS | MP3 PLAYERS |

| ELECTRONICS | PORTABLE ELECTRONICS | CD PLAYERS |

| ELECTRONICS | PORTABLE ELECTRONICS | 2 WAY RADIOS |

| TELEVISIONS | TUBE | NULL |

| TELEVISIONS | LCD | NULL |

| TELEVISIONS | PLASMA | NULL |

| TUBE | NULL | NULL |

| LCD | NULL | NULL |

| PLASMA | NULL | NULL |

| PORTABLE ELECTRONICS | MP3 PLAYERS | FLASH |

| PORTABLE ELECTRONICS | CD PLAYERS | NULL |

| PORTABLE ELECTRONICS | 2 WAY RADIOS | NULL |

| MP3 PLAYERS | FLASH | NULL |

| FLASH | NULL | NULL |

| CD PLAYERS | NULL | NULL |

| 2 WAY RADIOS | NULL | NULL |

+----------------------+----------------------+--------------+