创建一个栏目表,有顶级栏目和子栏目
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;