MYSQL二级联动

表数据

 

目标结果（以sort分组）

[
    {
        "0": [
            {
                "id": 3, 
                "icon": "https://blog.csdn.net/qq_39706570", 
                "name": "美食专题", 
                "sort": 0
            }, 
            {
                "id": 4, 
                "icon": "https://blog.csdn.net/qq_39706570", 
                "name": "数码专题", 
                "sort": 0
            }, 
            {
                "id": 5, 
                "icon": "https://blog.csdn.net/qq_39706570", 
                "name": "服饰专题", 
                "sort": 0
            }
        ]
    }, 
    {
        "1": [
            {
                "id": 2, 
                "icon": "https://blog.csdn.net/qq_39706570", 
                "name": "10月优选-豪华汽车", 
                "sort": 1
            }, 
            {
                "id": 6, 
                "icon": "https://blog.csdn.net/qq_39706570", 
                "name": "旅游专题", 
                "sort": 1
            }
        ]
    }, 
    {
        "2": [
            {
                "id": 11, 
                "icon": "https://blog.csdn.net/qq_39706570", 
                "name": "1.mallplus架构篇", 
                "sort": 2
            }
        ]
    }, 
    {
        "3": [
            {
                "id": 12, 
                "icon": "https://blog.csdn.net/qq_39706570", 
                "name": "3.mallplus业务篇", 
                "sort": 3
            }, 
            {
                "id": 13, 
                "icon": "https://blog.csdn.net/qq_39706570", 
                "name": "3.mallplus业务篇", 
                "sort": 3
            }, 
            {
                "id": 15, 
                "icon": "https://blog.csdn.net/qq_39706570", 
                "name": "3.mallplus业务篇", 
                "sort": 3
            }, 
            {
                "id": 16, 
                "icon": "https://blog.csdn.net/qq_39706570", 
                "name": "3.mallplus业务篇", 
                "sort": 3
            }
        ]
    }, 
    {
        "5": [
            {
                "id": 10, 
                "icon": "https://blog.csdn.net/qq_39706570", 
                "name": "1.mallplus序章", 
                "sort": 5
            }, 
            {
                "id": 14, 
                "icon": "https://blog.csdn.net/qq_39706570", 
                "name": "1.mallplus序章", 
                "sort": 5
            }, 
            {
                "id": 17, 
                "icon": "https://blog.csdn.net/qq_39706570", 
                "name": "1.mallplus序章", 
                "sort": 5
            }
        ]
    }
]

创建数据

CREATE TABLE `cms_subject_category` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL COMMENT '标题',
  `icon` varchar(500) DEFAULT NULL COMMENT '分类图标',
  `sort` int(11) DEFAULT NULL COMMENT '排序',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='专题分类表';
insert  into `cms_subject_category`(`id`,`name`,`icon`,`sort`) values 
(2,'10月优选-豪华汽车','https://blog.csdn.net/qq_39706570',1),
(3,'美食专题','https://blog.csdn.net/qq_39706570',0),
(4,'数码专题','https://blog.csdn.net/qq_39706570',0),
(5,'服饰专题','https://blog.csdn.net/qq_39706570',0),
(6,'旅游专题','https://blog.csdn.net/qq_39706570',1),
(10,'1.mallplus序章','https://blog.csdn.net/qq_39706570',5),
(11,'1.mallplus架构篇','https://blog.csdn.net/qq_39706570',2),
(12,'3.mallplus业务篇','https://blog.csdn.net/qq_39706570',3),
(13,'3.mallplus业务篇','https://blog.csdn.net/qq_39706570',3),
(14,'1.mallplus序章','https://blog.csdn.net/qq_39706570',5),
(15,'3.mallplus业务篇','https://blog.csdn.net/qq_39706570',3),
(16,'3.mallplus业务篇','https://blog.csdn.net/qq_39706570',3),
(17,'1.mallplus序章','https://blog.csdn.net/qq_39706570',5);

查询方法

SELECT CONCAT('[',GROUP_CONCAT('{"',a.sort,'":' ,msg,"}"),']') FROM 
(
SELECT sort,CONCAT('[',GROUP_CONCAT(
JSON_OBJECT('sort',sort,'id',id,'name',NAME,'icon',icon)),']') msg FROM cms_subject_category
 GROUP BY sort
)a 

注意：以上SQL查询方法是使用了

GROUP_CONCAT()

1、该函数有长度限制，一般默认长度为1024，可以使用这篇博客（https://blog.csdn.net/qq_39706570/article/details/103999965）轻松修改长度范围。