mysql 城市二级联动_MYSQL二级联动

表数据

查询结果

{

"电器": [{

"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