表数据

查询结果
{ "电器": [{ "1": [{ "parent_id": "1", "parent_name": "家用电器", "son_id": "3",
"son_name": "大家电" } { "parent_id": "1", "parent_name": "家用电器", "son_id": "4",
"son_name": "生活电器" }] } { "2": [{ "parent_id": "2", "parent_name": "电脑办公",
"son_id": "9", "son_name": "电脑整机" } { "parent_id": "2", "parent_name": "电脑办公",
"son_id": "10", "son_name": "电脑配件" }] } { "3": [{ "parent_id": "3",
"parent_name": "大家电", "son_id": "5", "son_name": "平板电视" } { "parent_id": "3",
"parent_name": "大家电", "son_id": "6", "son_name": "空调" }] } { "4": [{
"parent_id": "4", "parent_name": "生活电器", "son_id": "7", "son_name": "电风扇" } {
"parent_id": "4", "parent_name": "生活电器", "son_id": "8", "son_name": "饮水机" }] }
{ "9": [{ "parent_id": "9", "parent_name": "电脑整机", "son_id": "11", "son_name":
"笔记本" } { "parent_id": "9", "parent_name": "电脑整机", "son_id": "12", "son_name":
"超极本" } { "parent_id": "9", "parent_name": "电脑整机", "son_id": "13", "son_name":
"游戏本" }] } { "10": [{ "parent_id": "10", "parent_name": "电脑配件", "son_id": "14",
"son_name": "CPU" } { "parent_id": "10", "parent_name": "电脑配件", "son_id": "15",
"son_name": "主机" }] }] }
创建数据
CREATE TABLE `table_goods_type` ( `type_id` smallint(5) unsigned NOT NULL
AUTO_INCREMENT, `type_name` varchar(20) NOT NULL, `parent_id` smallint(5)
unsigned NOT NULL DEFAULT '0', PRIMARY KEY (`type_id`) ) ENGINE=InnoDB
AUTO_INCREMENT=16 DEFAULT CHARSET=utf8; insert into
`table_goods_type`(`type_id`,`type_name`,`parent_id`) values (1,'家用电器',0),
(2,'电脑办公',0), (3,'大家电',1), (4,'生活电器',1), (5,'平板电视',3), (6,'空调',3), (7,'电风扇',4),
(8,'饮水机',4), (9,'电脑整机',2), (10,'电脑配件',2), (11,'笔记本',9), (12,'超极本',9),
(13,'游戏本',9), (14,'CPU',10), (15,'主机',10);
查询方法
SELECT CONCAT("{\"电器\":[",GROUP_CONCAT(a.msg),"]}") FROM (SELECT a.parent_id,
CONCAT("{\"",parent_id,"\":[",GROUP_CONCAT(a.msg),"]}") msg FROM ( SELECT
DISTINCT parent.type_id parent_id, GROUP_CONCAT( "{"
"\"parent_id\":\"",parent.type_id,"\",",
"\"parent_name\":\"",parent.type_name,"\",", "\"son_id\":\"",son.type_id,"\",",
"\"son_name\":\"",son.type_name,"\"","}") msg FROM table_goods_type son LEFT
JOIN table_goods_type parent ON son.parent_id = parent.type_id WHERE
parent.type_id IS NOT NULL GROUP BY son.type_id )a GROUP BY a.parent_id )a
 

技术
©2019-2020 Toolsou All rights reserved,
Qt学习2——.pro文件和.h文件介绍LinkedHashMap基本用法&使用实现简单缓存pytorch之ResNet18(对cifar10数据进行分类准确度达到94%)华为Mate 40 Pro+ 5G曝光:徕卡电影镜头、陶瓷机身统计字母个数(java语言实现)JavaScript中的 Call 和 Apply1190 反转每对括号间的子串 leetcode记一次EventBus内存泄露导致的项目问题浅谈uni-app页面传值问题 Chrome OS,对程序员和Windows意味着什么?,互联网营销