Table data

Query results
{ " an electric appliance ": [{ "1": [{ "parent_id": "1", "parent_name": " Household Electric Appliances ", "son_id": "3",
"son_name": " Big household appliances " } { "parent_id": "1", "parent_name": " Household Electric Appliances ", "son_id": "4",
"son_name": " Living appliances " }] } { "2": [{ "parent_id": "2", "parent_name": " Computer office ",
"son_id": "9", "son_name": " Computer machine " } { "parent_id": "2", "parent_name": " Computer office ",
"son_id": "10", "son_name": " Computer accessories " }] } { "3": [{ "parent_id": "3",
"parent_name": " Big household appliances ", "son_id": "5", "son_name": " Flat panel TV " } { "parent_id": "3",
"parent_name": " Big household appliances ", "son_id": "6", "son_name": " air conditioner " }] } { "4": [{
"parent_id": "4", "parent_name": " Living appliances ", "son_id": "7", "son_name": " electric fan " } {
"parent_id": "4", "parent_name": " Living appliances ", "son_id": "8", "son_name": " Water dispenser " }] }
{ "9": [{ "parent_id": "9", "parent_name": " Computer machine ", "son_id": "11", "son_name":
" notebook " } { "parent_id": "9", "parent_name": " Computer machine ", "son_id": "12", "son_name":
" Ultrabooks " } { "parent_id": "9", "parent_name": " Computer machine ", "son_id": "13", "son_name":
" Game book " }] } { "10": [{ "parent_id": "10", "parent_name": " Computer accessories ", "son_id": "14",
"son_name": "CPU" } { "parent_id": "10", "parent_name": " Computer accessories ", "son_id": "15",
"son_name": " host " }] }] }
Create data
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,' Household Electric Appliances ',0),
(2,' Computer office ',0), (3,' Big household appliances ',1), (4,' Living appliances ',1), (5,' Flat panel TV ',3), (6,' air conditioner ',3), (7,' electric fan ',4),
(8,' Water dispenser ',4), (9,' Computer machine ',2), (10,' Computer accessories ',2), (11,' notebook ',9), (12,' Ultrabooks ',9),
(13,' Game book ',9), (14,'CPU',10), (15,' host ',10);
Query method
SELECT CONCAT("{\" an electric appliance \":[",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
 

Technology
©2019-2020 Toolsou All rights reserved,
Non preemptive static priority scheduling algorithm for operating system (C language )Go Language learning notes (GUI programming )XCTF Attack and defense world web Advanced practice _ 2_lottery What's the difference between computer major and training background ?python realization vlookup_ Dry goods I : Why python It's inside vlookup Bubble sort primary springboot2 Separation of front and rear platforms ,token Put in header Pit for verification Python Case conversion of letters ( Two methods )javascript event ( Detailed explanation of zero basis )Unity2019 UIElement note ( ten ) Simple exercise 2