mysql查询无限极分类排序
表是这样设计的
2，4,5字段请无视
现在要查询出来这样格式的数据
一级分类
二级分类a
三级分类a
三级分类b
二级分类b
三级分类a
三级分类b
我使用的sql为 select * from vn_erp_healthprice order by sorts
sorts字段为varchar类型
查询的结果如下
排序结果满足业务要求,但为什么1-11排在1-2前面
还有 根据字符串排序这是个什么原理来着 好久没用过这种分类了 一时想不起来了
求大神赐教
表sql如下
/*navicat mysql data transfersource server : localhostsource server version : 50621source host : 127.0.0.1:3306source database : testtarget server type : mysqltarget server version : 50621file encoding : 65001date: 2015-07-15 16:38:49*/set foreign_key_checks=0;-- ------------------------------ table structure for `vn_erp_healthprice`-- ----------------------------drop table if exists `vn_erp_healthprice`;create table `vn_erp_healthprice` ( `id` int(10) not null auto_increment comment '主键', `lyid` varchar(10) not null default '' comment '维修项id', `name` varchar(100) not null default '' comment '维修项名称', `price` varchar(10) not null default '' comment '工时单价', `hours` varchar(10) not null default '' comment '工时', `parent_id` int(11) not null default '0', `sorts` varchar(20) not null default '' comment '排序', primary key (`id`), key `hid` (`lyid`)) engine=innodb auto_increment=27 default charset=utf8;-- ------------------------------ records of vn_erp_healthprice-- ----------------------------insert into `vn_erp_healthprice` values ('1', '', '保养(一级分类)', '', '', '0', '1');insert into `vn_erp_healthprice` values ('2', '', '更换(二级分类)', '', '', '1', '1-2');insert into `vn_erp_healthprice` values ('3', '1', '更换机油', '', '', '2', '1-2-3');insert into `vn_erp_healthprice` values ('4', '2', '更换机油滤清器', '', '', '2', '1-2-4');insert into `vn_erp_healthprice` values ('5', '3', '更换空气滤清器', '', '', '2', '1-2-5');insert into `vn_erp_healthprice` values ('6', '4', '更换燃油滤清器', '', '', '2', '1-2-6');insert into `vn_erp_healthprice` values ('7', '5', '更换全部火花塞', '', '', '2', '1-2-7');insert into `vn_erp_healthprice` values ('8', '6', '检查助力转向油', '', '', '11', '1-2-8');insert into `vn_erp_healthprice` values ('9', '7', '更换整车制动液', '', '', '2', '1-2-9');insert into `vn_erp_healthprice` values ('10', '8', '检查自动变速箱油', '', '', '11', '1-11-10');insert into `vn_erp_healthprice` values ('11', '', '检查(二级分类)', '', '', '1', '1-11');insert into `vn_erp_healthprice` values ('12', '9', '检查或者更换手动变速箱油', '', '', '11', '1-11-12');insert into `vn_erp_healthprice` values ('13', '10', '检查发动机正时皮带', '', '', '11', '1-11-13');insert into `vn_erp_healthprice` values ('14', '11', '更换空调滤清器', '', '', '2', '1-2-14');insert into `vn_erp_healthprice` values ('15', '', '定期(二级分类)', '', '', '1', '1-15');insert into `vn_erp_healthprice` values ('16', '12', '定期添加冷媒', '', '', '15', '1-15-16');insert into `vn_erp_healthprice` values ('17', '13', '检查空调管路', '', '', '11', '1-11-17');insert into `vn_erp_healthprice` values ('18', '14', '更换防冻冷却液', '', '', '2', '1-2-18');insert into `vn_erp_healthprice` values ('19', '15', '检查厚度、调整，必要时更换前刹车片', '', '', '11', '1-11-19');insert into `vn_erp_healthprice` values ('20', '16', '检查厚度、调整，必要时更换前刹车盘', '', '', '11', '1-11-20');insert into `vn_erp_healthprice` values ('21', '17', '检查厚度、调整，必要时更换后刹车片（蹄）', '', '', '11', '1-11-21');insert into `vn_erp_healthprice` values ('22', '18', '检查厚度、调整，必要时更换后刹车盘（鼓）', '', '', '11', '1-11-21');insert into `vn_erp_healthprice` values ('23', '', '调整(二级分类)', '', '', '1', '1-22');insert into `vn_erp_healthprice` values ('24', '19', '调整雨刮（雨刷）', '', '', '23', '1-22-23');insert into `vn_erp_healthprice` values ('25', '20', '检查电瓶', '', '', '11', '1-11-24');insert into `vn_erp_healthprice` values ('26', '21', '检查减震器', '', '', '11', '1-11-25');