mysql的无限分类问题_MySQL无限分类

无限分类：不管什么分类就放在分类表中

CREATE TABLE loop_category (

id INT PRIMARY KEY AUTO_INCREMENT,

NAME VARCHAR(20),

pid INT

);

INSERT INTO loop_category (NAME, pid) VALUES ('手机', NULL), ('电视', NULL), ('空调', NULL);

INSERT INTO loop_category (NAME, pid) VALUES ('苹果手机',1), ('华为手机',1), ('小米手机',1), ('魅族手机',1), ('锤子手机',1);

INSERT INTO loop_category (NAME, pid) VALUES ('夏普电视',2), ('乐视电视',2), ('TCL电视',2), ('创维电视',2), ('海尔电视',2);

INSERT INTO loop_category (NAME, pid) VALUES ('格力空调',3), ('海尔空调',3), ('美的空调',3), ('奥克斯空调',3), ('志高空调',3);

-- 查询特殊的

-- 先查询手机的id

SELECT id FROM loop_category WHERE NAME='手机'; -- 手机的id

-- 在根据pid等于手机的id

SELECT * FROM loop_category WHERE pid = (SELECT id FROM loop_category WHERE NAME='手机'); -- 手机的分类

-- 自连接

SELECT c1.`id`, c1.`name` FROM loop_category c1 INNER JOIN loop_category c2 ON c1.pid = c2.`id` WHERE c2.`name`='电视';

标签：category,NAME,分类,pid,无限,MySQL,手机,id,loop

来源： https://blog.csdn.net/RookiexiaoMu_a/article/details/88966958