mysql 笔记14 ---自连接(了解即可)

创建一个栏目表，有顶级栏目和子栏目
CREATE TABLE `cates` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `pid` int(11) NOT NULL DEFAULT '0' COMMENT '上级id',
  `title` varchar(60) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '标题',
  `desc` text COLLATE utf8mb4_unicode_ci COMMENT '栏目描述说明',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

数据有这些

自连接
SELECT a.title as '顶级栏目',b.title as '子栏目' FROM `cates` as a, `cates` as b WHERE a.id=b.pid;