MySQL学习笔记（20）——自身连接（无限分类数据表设计）

自身连接

无限分类数据表设计:

CREATE TABLE tdb_goods_types
     (
     type_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
     type_name VARCHAR(20) NOT NULL,
     parent_id SMALLINT UNSIGNED NOT NULL DEFAULT 0
     );

输入数据：

 INSERT tdb_goods_types(type_name,parent_id) VALUES('家用电器',DEFAULT);
  INSERT tdb_goods_types(type_name,parent_id) VALUES('电脑、办公',DEFAULT);
  INSERT tdb_goods_types(type_name,parent_id) VALUES('大家电',1);
  INSERT tdb_goods_types(type_name,parent_id) VALUES('生活电器',1);
  INSERT tdb_goods_types(type_name,parent_id) VALUES('平板电视',3);
  INSERT tdb_goods_types(type_name,parent_id) VALUES('空调',3);
  INSERT tdb_goods_types(type_name,parent_id) VALUES('电风扇',4);
  INSERT tdb_goods_types(type_name,parent_id) VALUES('饮水机',4);
  INSERT tdb_goods_types(type_name,parent_id) VALUES('电脑整机',2);
  INSERT tdb_goods_types(type_name,parent_id) VALUES('电脑配件',2);
  INSERT tdb_goods_types(type_name,parent_id) VALUES('笔记本',9);
  INSERT tdb_goods_types(type_name,parent_id) VALUES('超级本',9);
  INSERT tdb_goods_types(type_name,parent_id) VALUES('游戏本',9);
  INSERT tdb_goods_types(type_name,parent_id) VALUES('CPU',10);
  INSERT tdb_goods_types(type_name,parent_id) VALUES('主机',10);

表结构以及数据分类结构：

自连接demo:

SELECT s.type_id AS 类别ID,s.type_name AS 名称,p.type_name AS 类别 FROM tdb_goods_types AS s
     LEFT JOIN tdb_goods_types AS p
     ON s.parent_id = p.type_id;

自连接demo2:

SELECT p.type_id,p.type_name,count(s.type_name) AS child_count FROM tdb_goods_types AS p
     LEFT JOIN tdb_goods_types AS s
     ON s.parent_id = p.type_id
     GROUP BY p.type_name
     ORDER BY p.type_id;