多级分类查询解决方案

最新推荐文章于 2023-02-08 11:42:42 发布

永寂如孤星

最新推荐文章于 2023-02-08 11:42:42 发布

阅读量3.3k

点赞数 8

文章标签： sql 多级分类查询

本文链接：https://blog.csdn.net/yjrguxing/article/details/114909041

版权

表构建

首先我们需要一张表，足以让我们无限扩展多级分类，一般情况下这张表应当是这个样子

CREATE TABLE TYPE
(
	id INT NOT NULL AUTO_INCREMENT COMMENT'主键 分类id',
	`name` VARCHAR(32) NOT NULL COMMENT'分类名称',
	`type` INT NOT NULL COMMENT'分类类型 1级分类 2级分类 3级分类......',
	father_id INT NOT NULL COMMENT'父id 依赖的父级分类的id，1级分类统一设置',
-- 以下为其他可能存在的字段
	logo VARCHAR(64)    COMMENT '图标 logo' ,
	slogan VARCHAR(64)    COMMENT '口号' ,
	cat_image VARCHAR(64)    COMMENT '分类图' ,
	bg_color VARCHAR(32)    COMMENT '背景颜色' ,
-- 等等其他字段
	PRIMARY KEY(id)
)COMMENT = '分类表' CHARSET =utf8

那么为了接下来的测试，我们可以添加一些测试数据，代码如下


INSERT INTO `type` VALUES (1, '甜点/蛋糕', 1, 0, 'img/cake.png', '每一道甜品都能打开你的味蕾', NULL, '#fe7a65');
INSERT INTO `type` VALUES (2, '饼干/膨化', 1, 0, 'img/cookies.png', '嘎嘣脆，一听到声音就开吃', NULL, '#f59cec');
INSERT INTO `type` VALUES (3, '熟食/肉类', 1, 0, 'img/meat.png', '食肉者最爱绝佳美食', NULL, '#b474fe');
INSERT INTO `type` VALUES (4, '素食/卤味', 1, 0, 'img/luwei.png', '香辣甜辣麻辣，辣了才有味', NULL, '#82ceff');
INSERT INTO `type` VALUES (5, '坚果/炒货', 1, 0, 'img/jianguo.png', '酥脆无比，休闲最佳', NULL, '#c6a868');
INSERT INTO `type` VALUES (6, '糖果/蜜饯', 1, 0, 'img/sweet.png', '甜味是爱美者的最爱',NULL, '#6bdea7');
INSERT INTO `type` VALUES (7, '巧克力', 1, 0, 'img/chocolate.png', '美容养颜，男女都爱', NULL, '#f8c375');
INSERT INTO `type` VALUES (8, '海鲜/海味', 1, 0, 'img/lobster.png', '吃货们怎么能少了海鲜呢？', NULL, '#84affe');
INSERT INTO `type` VALUES (9, '花茶/果茶', 1, 0, 'img/tea.png', '绿茶红茶怎能少得了',NULL, '#ff9229');
INSERT INTO `type` VALUES (10, '生鲜/蔬果', 1, 0, 'img/food.png', '新鲜少不了，每日蔬果生鲜', NULL, '#6cc67c');
INSERT INTO `type` VALUES (11, '蛋糕', 2, 1, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (12, '点心', 2, 1, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (13, '饼干', 2, 2, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (14, '薯片', 2, 2, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (15, '虾条', 2, 2, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (16, '肉铺', 2, 3, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (17, '肉松', 2, 3, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (18, '香肠', 2, 3, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (19, '豆干', 2, 4, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (20, '干笋', 2, 4, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (21, '鸭脖', 2, 4, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (22, '坚果', 2, 5, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (23, '锅巴', 2, 5, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (24, '糖果', 2, 6, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (25, '蜜饯', 2, 6, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (26, '巧克力', 2, 7, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (27, '果冻', 2, 7, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (29, '海鲜', 2, 8, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (30, '海味', 2, 8, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (31, '茶叶', 2, 9, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (32, '奶茶', 2, 9, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (33, '果茶', 2, 9, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (34, '蔬菜', 2, 10, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (35, '水果', 2, 10, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (36, '肉类', 2, 10, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (37, '蒸蛋糕', 3, 11, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (38, '软面包', 3, 11, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (39, '脱水蛋糕', 3, 11, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (40, '马卡龙', 3, 11, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (41, '甜甜圈', 3, 11, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (42, '三明治', 3, 11, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (43, '铜锣烧', 3, 11, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (44, '肉松饼', 3, 12, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (45, '华夫饼', 3, 12, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (46, '沙琪玛', 3, 12, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (47, '鸡蛋卷', 3, 12, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (48, '蛋饼', 3, 12, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (49, '凤梨酥', 3, 12, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (50, '手撕面包', 3, 12, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (51, '苏打饼干', 3, 13, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (52, '夹心饼干', 3, 13, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (53, '闲饼干', 3, 13, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (54, '甜饼干', 3, 13, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (55, '威化饼干', 3, 13, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (56, '酥饼干', 3, 13, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (57, '曲奇', 3, 13, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (58, '无糖饼干', 3, 13, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (59, '早餐饼干', 3, 13, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (60, '薯条', 3, 14, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (61, '薯片', 3, 14, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (62, '山药薯片', 3, 14, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (63, '网红薯片', 3, 14, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (64, '蛋黄薯片', 3, 14, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (65, '虾条', 3, 15, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (66, '怀旧虾条', 3, 15, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (67, '8090后虾条', 3, 15, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (68, '进口虾条', 3, 15, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (69, 'DIY虾条', 3, 15, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (73, '猪肉脯', 3, 16, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (74, '牛肉脯', 3, 16, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (75, '鸡肉脯', 3, 16, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (76, '散装肉脯', 3, 16, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (77, '猪肉干', 3, 16, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (78, '牛肉干', 3, 16, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (79, '牛肉粒', 3, 16, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (80, '牛肉松', 3, 17, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (81, '猪肉松', 3, 17, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (82, '儿童肉松', 3, 17, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (83, '腊味香肠', 3, 18, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (84, '广式香肠', 3, 18, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (85, '火腿肠', 3, 18, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (86, '烤肠', 3, 18, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (87, '鸡肉肠', 3, 18, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (88, '鱼肠', 3, 18, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (89, '酒味香肠', 3, 18, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (90, '香菇', 3, 19, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (91, '零食', 3, 19, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (92, '豆腐干', 3, 19, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (93, '辣条', 3, 19, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (94, '泡椒脆笋', 3, 20, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (95, '野生笋干', 3, 20, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (96, '扁尖笋', 3, 20, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (97, '农家笋', 3, 20, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (98, '卤味鸭脖', 3, 21, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (99, '麻辣鸭脖', 3, 21, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (100, '武汉鸭脖', 3, 21, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (101, '鸭锁骨', 3, 21, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (102, '鸭胗', 3, 21, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (103, '鸭翅', 3, 21, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (104, '网红坚果', 3, 22, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (105, '瓜子', 3, 22, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (106, '碧根果', 3, 22, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (107, '松子', 3, 22, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (108, '山核桃', 3, 22, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (109, '开心果', 3, 22, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (110, '腰果', 3, 22, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (111, '网红锅巴', 3, 23, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (112, '蛋黄锅巴', 3, 23, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (113, '水果糖', 3, 24, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (114, '薄荷糖', 3, 24, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (115, '夹心糖', 3, 24, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (116, '奶糖', 3, 24, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (117, '喜糖', 3, 24, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (118, '麦芽糖', 3, 24, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (119, '话梅', 3, 25, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (120, '果干', 3, 25, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (121, '草莓干', 3, 25, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (122, '芒果干', 3, 25, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (123, '菠萝干', 3, 25, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (124, '山楂糕', 3, 25, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (125, '黑巧克力', 3, 26, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (126, '白巧克力', 3, 26, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (127, '夹心巧克力', 3, 26, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (128, '榛果巧克力', 3, 26, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (129, '日本生巧', 3, 26, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (130, '果肉布丁', 3, 27, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (131, '果冻碎碎冰', 3, 27, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (132, '果汁果冻', 3, 27, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (133, '帝王蟹', 3, 29, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (134, '大龙虾', 3, 29, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (135, '鲍鱼', 3, 29, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (136, '海参', 3, 29, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (137, '鱿鱼', 3, 29, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (138, '三文鱼', 3, 29, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (139, '扇贝', 3, 29, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (140, '干贝', 3, 30, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (141, '海苔', 3, 30, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (142, '虾仁', 3, 30, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (143, '海鱼干', 3, 30, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (144, '水蜜桃茶', 3, 33, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (145, '白桃乌龙茶', 3, 33, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (146, '柠檬片', 3, 33, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (147, '白茶', 3, 31, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (148, '红茶', 3, 31, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (149, '绿茶', 3, 31, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (150, '铁观音', 3, 31, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (151, '胖大海', 3, 31, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (152, '碧螺春', 3, 31, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (153, '速冲奶茶', 3, 32, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (154, '珍珠奶茶', 3, 32, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (155, '袋装奶茶', 3, 32, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (156, '英式奶茶', 3, 32, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (157, '黄瓜', 3, 34, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (158, '芋头', 3, 34, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (159, '玉米', 3, 34, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (160, '南瓜', 3, 34, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (161, '荸荠', 3, 34, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (162, '山药', 3, 34, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (163, '秋葵', 3, 34, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (164, '红薯/紫薯', 3, 34, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (165, '土豆', 3, 34, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (166, '水蜜桃', 3, 35, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (167, '西瓜', 3, 35, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (168, '苹果', 3, 35, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (169, '凤梨', 3, 35, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (170, '草莓', 3, 35, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (171, '葡萄', 3, 35, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (172, '杨梅', 3, 35, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (173, '牛排', 3, 36, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (174, '鸡肉', 3, 36, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (175, '猪肉', 3, 36, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (176, '羊肉', 3, 36, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (177, '蹄子', 3, 36, NULL, NULL, NULL, NULL);
INSERT INTO `type` VALUES (178, '糖醋排骨', 3, 36, NULL, NULL, NULL, NULL);

此时查询此表全部数据结果如下
在这里插入图片描述

分析完成多级分类表查询需求

因为在前端多级分类一般都是以懒加载的方式加载的，所以我们不会直接将表中的所有内容一并查出。
大部分情况下我们在用户鼠标移动至一级分类时加载一级分类下的二级、三级或更多分类，在这样的需求下，我们可能会尝试以下几种查询。

查询思路渐进

在看到这个需求时，我们最初想到的大部分都应当是

SELECT 
	*
FROM 
	TYPE
	-- father_id = 用户触碰的分类的id
WHERE
	father_id = 1

结果
在这里插入图片描述

这种方式可以满足通过x级分类查询x+1级分类的需求，但是目前大部分电商网站需要的都是通过x级分类查询x+1、x+2 两级分类，此时我们再思考一下，将这个sql延续下去，就是下面这样。

-- 思路渐进
-- 上个sql 是根据x级分类 查询x级分类下的x+1级分类
-- 这个sql在上个sql的基础上 新增了另外一个type表 命名为s（子）
-- 要求子表的 父级分类id = 父表（根据x级分类id查询出的x+1级分类的表）的id 
-- 也就可以查出x级分类的 x + 1级分类和 x +1级分类了
SELECT
	*
FROM
	TYPE f, TYPE s
WHERE 
	s.`father_id` = f.`id`  AND
	f.father_id = 1

结果
在这里插入图片描述
在此sql的基础上则可以根据x级分类查询出x+1+2+3无限延伸的分类，具体做法就是再次新建一个type表进行命名多加一个where 将第三个表的father id 和第二个表的id联系到一起，这就是多级分类表查询的基础解决方案

最终查询优化

我们来看看最终得到的sql

SELECT
	*
FROM
	TYPE f, TYPE s
WHERE 
	s.`father_id` = f.`id`  AND
	f.father_id = 1

这种sql我们首先要解决多表联系问题，让这个查询从原本的普通查询变为连接查询
tip：此种连接为自连接
变化后的sql如下

SELECT
	*
FROM
	TYPE f
JOIN	
	TYPE s
ON
	s.`father_id` = f.`id`
WHERE 
	f.father_id = 1

到这一步此sql已经可以正常使用了，不过为了增加可用性，我们在连接的选择上还需要斟酌一下，大家应该都知道连接分为以下几种：
join（inner join）、left join、right join、full join
在此场景下，我们要考虑一个问题，就是父级分类下并没有子分类了，此情况下如果我们用join（inner join）则会出现查询不出左表数据的情况，所以我们需要使用left join，完善后的sql如下

SELECT
	*
FROM
	`type` f
LEFT JOIN
	`type` s
ON 
	f.`id` = s.`father_id`
WHERE f.`father_id` = 1

此时我们已经解决了大部分问题，但是还是有可以优化的地方，此种查询会出现列名重复的问题，我们需要将其写死，所以我们最终得到的SQL如下

SELECT
	f.`id` AS id,
	f.`name` AS `name`,
	f.`type` AS `type`,
	f.`father_id` AS fatherId,
	s.`id` AS subId,
	s.`name` AS subName,
	s.`type` AS subType,
	s.`father_id` AS subFatherId
FROM
	`type` f
LEFT JOIN
	`type` s
ON 
	f.`id` = s.`father_id`
WHERE f.`father_id` = 1

查询结果如下
在这里插入图片描述

Mybatis编写相应Mapper

从上表可以看出，此时的name列冗余数据较多，我们其实仅仅需要 蛋糕、电信就可以了
所以我们在VO和Mapper中都需要使用集合将这些数据整理为一对多关系

  <resultMap id="myCategoryVo" type="com.psyduck.vo.CategoryVo">
    <id column="id" property="id"/>
    <result column="name" property="name"></result>
    <result column="type" property="type"></result>
    <result column="fatherId" property="fatherId"></result>
    <collection property="subCatList" ofType="com.psyduck.vo.SubCategoryVo">
      <id column="subId" property="subId"/>
      <result column="subName" property="subName"></result>
      <result column="subType" property="subType"></result>
      <result column="subFatherId" property="subFatherId"></result>
    </collection>
  </resultMap>

public class CategoryVo {

    private Integer id;
    private String name;
    private String type;
    private Integer fatherId;

    private List<SubCategoryVo> subCatList;
}

public class SubCategoryVo {
    private Integer subId;
    private String subName;
    private String subType;
    private Integer subFatherId;
}

其他应当就没有什么需要注意的了，此时可以用swagger检测一下返回数据，返回结果如下

{
  "status": 200,
  "msg": "OK",
  "data": [
    {
      "id": 11,
      "name": "蛋糕",
      "type": "2",
      "fatherId": 1,
      "subCatList": [
        {
          "subId": 37,
          "subName": "蒸蛋糕",
          "subType": "3",
          "subFatherId": 11
        },
        {
          "subId": 38,
          "subName": "软面包",
          "subType": "3",
          "subFatherId": 11
        },
        {
          "subId": 39,
          "subName": "脱水蛋糕",
          "subType": "3",
          "subFatherId": 11
        },
        {
          "subId": 40,
          "subName": "马卡龙",
          "subType": "3",
          "subFatherId": 11
        },
        {
          "subId": 41,
          "subName": "甜甜圈",
          "subType": "3",
          "subFatherId": 11
        },
        {
          "subId": 42,
          "subName": "三明治",
          "subType": "3",
          "subFatherId": 11
        },
        {
          "subId": 43,
          "subName": "铜锣烧",
          "subType": "3",
          "subFatherId": 11
        }
      ]
    },
    {
      "id": 12,
      "name": "点心",
      "type": "2",
      "fatherId": 1,
      "subCatList": [
        {
          "subId": 44,
          "subName": "肉松饼",
          "subType": "3",
          "subFatherId": 12
        },
        {
          "subId": 45,
          "subName": "华夫饼",
          "subType": "3",
          "subFatherId": 12
        },
        {
          "subId": 46,
          "subName": "沙琪玛",
          "subType": "3",
          "subFatherId": 12
        },
        {
          "subId": 47,
          "subName": "鸡蛋卷",
          "subType": "3",
          "subFatherId": 12
        },
        {
          "subId": 48,
          "subName": "蛋饼",
          "subType": "3",
          "subFatherId": 12
        },
        {
          "subId": 49,
          "subName": "凤梨酥",
          "subType": "3",
          "subFatherId": 12
        },
        {
          "subId": 50,
          "subName": "手撕面包",
          "subType": "3",
          "subFatherId": 12
        }
      ]
    }
  ]
}

最后更新于2021年3月30日
原创不易，如果该文章对你有所帮助，望左上角点击关注~如有任何技术相关问题，可通过评论联系我讨论,我会在力所能及之内进行相应回复以及开单章解决该问题.

该文章如有任何错误请在评论中指出，感激不尽，转载请附出处！
*个人博客首页：https://blog.csdn.net/yjrguxing ——您的每个关注和评论都对我意义重大

永寂如孤星

关注

8
点赞
踩
23

收藏

觉得还不错? 一键收藏
4
评论
多级分类查询解决方案

表构建首先我们需要一张表，足以让我们无限扩展多级分类，一般情况下这张表应当是这个样子CREATE TABLE TYPE( id INT NOT NULL AUTO_INCREMENT COMMENT'主键分类id', `name` VARCHAR(32) NOT NULL COMMENT'分类名称', `type` INT NOT NULL COMMENT'分类类型 1级分类 2级分类 3级分类......', father_id INT NOT NULL COMMENT'父id 依赖的父级
复制链接

扫一扫