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`='电视';