mysql如何按特定id排序
set foreign_key_checks=0;-- ------------------------------ table structure for `p`-- ----------------------------drop table if exists `p`;create table `p` ( `id` int(11) not null auto_increment, `name` varchar(255) default null, `categories_id` int(11) default null, primary key (`id`)) engine=myisam auto_increment=7 default charset=utf8;-- ------------------------------ records of p-- ----------------------------insert into `p` values ('1', 'jimmy', '2');insert into `p` values ('2', 'tina', '2');insert into `p` values ('3', 'dd', '2');insert into `p` values ('4', 'hello', '2');insert into `p` values ('5', 'world', '2');insert into `p` values ('6', 'slucky', '2');set foreign_key_checks=0;-- ------------------------------ table structure for `p_sort`-- ----------------------------drop table if exists `p_sort`;create table `p_sort` ( `categories_id` int(10) not null default '0', `best_sort_person_id` varchar(100) default null, primary key (`categories_id`)) engine=myisam default charset=utf8;-- ------------------------------ records of p_sort-- ----------------------------insert into `p_sort` values ('2', '2,5,1');
回复讨论(解决方案) select * from p, p_sort order by find_in_set(p.id, p_sort.best_sort_person_id)>0 desc, find_in_set(p.id, p_sort.best_sort_person_id) asc, id
find_in_set(p.id, p_sort.best_sort_person_id)>0 desc 用于将id=2,5,1的排在前面
find_in_set(p.id, p_sort.best_sort_person_id) asc 用于将id=2,5,1的按出现次序排列
谢谢xuzuning老师，狂亲
select p.* from p inner join p_sort s on p.categories_id=s.categories_id order by if(find_in_set(id,s.best_sort_person_id)>0,find_in_set(id,s.best_sort_person_id),id) asc ;