mysql 奇怪的排名问题！！！期待高手解答^_^


create table `robot_game_user` (
`id` double ,
`user_sid` varchar (60),
`car_name` varchar (120),
`race_state` varchar (30),
`race_name` varchar (150),
`total_distance` double ,
`modify_time` datetime ,
`user_mobile` varchar (60)
); 
insert into `robot_game_user` (`id`, `user_sid`, `car_name`, `race_state`, `race_name`, `total_distance`, `modify_time`, `user_mobile`) values('20','111','奥拓汽车','10','北京五环越野赛','600','2011-03-23 10:33:32',NULL);
insert into `robot_game_user` (`id`, `user_sid`, `car_name`, `race_state`, `race_name`, `total_distance`, `modify_time`, `user_mobile`) values('21','629117682','奥拓汽车','10','北京五环越野赛','500','2011-03-23 11:00:10',NULL);
insert into `robot_game_user` (`id`, `user_sid`, `car_name`, `race_state`, `race_name`, `total_distance`, `modify_time`, `user_mobile`) values('22','586751610','奥拓汽车','10','北京五环越野赛','300','2011-03-23 11:18:39',NULL);
insert into `robot_game_user` (`id`, `user_sid`, `car_name`, `race_state`, `race_name`, `total_distance`, `modify_time`, `user_mobile`) values('23','565556484','奥拓汽车','10','北京五环越野赛','600','2011-03-23 15:35:35',NULL);
insert into `robot_game_user` (`id`, `user_sid`, `car_name`, `race_state`, `race_name`, `total_distance`, `modify_time`, `user_mobile`) values('24','629072509','奥拓汽车','10','北京五环越野赛','100','2011-03-23 16:07:49',NULL);
insert into `robot_game_user` (`id`, `user_sid`, `car_name`, `race_state`, `race_name`, `total_distance`, `modify_time`, `user_mobile`) values('25','749359178','奥拓汽车','10','北京五环越野赛','2200','2011-03-24 10:47:18',NULL);
insert into `robot_game_user` (`id`, `user_sid`, `car_name`, `race_state`, `race_name`, `total_distance`, `modify_time`, `user_mobile`) values('26','588589895','奥拓汽车','10','北京五环越野赛','400','2011-03-23 16:54:17',NULL);
insert into `robot_game_user` (`id`, `user_sid`, `car_name`, `race_state`, `race_name`, `total_distance`, `modify_time`, `user_mobile`) values('27','5885898952','奥拓汽车','10','北京五环越野赛','400','2011-03-23 17:02:39',NULL);
insert into `robot_game_user` (`id`, `user_sid`, `car_name`, `race_state`, `race_name`, `total_distance`, `modify_time`, `user_mobile`) values('28','563791506','奥拓汽车','11','','1100','2011-03-28 11:31:27',NULL);
insert into `robot_game_user` (`id`, `user_sid`, `car_name`, `race_state`, `race_name`, `total_distance`, `modify_time`, `user_mobile`) values('29','123','奥拓汽车','11','','500','2011-03-28 11:01:32',NULL);
insert into `robot_game_user` (`id`, `user_sid`, `car_name`, `race_state`, `race_name`, `total_distance`, `modify_time`, `user_mobile`) values('30','123213','奥拓汽车','11','','500','2011-03-28 11:39:06',NULL);
insert into `robot_game_user` (`id`, `user_sid`, `car_name`, `race_state`, `race_name`, `total_distance`, `modify_time`, `user_mobile`) values('31','321321','奥拓汽车','11','','800','2011-03-28 11:49:50',NULL);
insert into `robot_game_user` (`id`, `user_sid`, `car_name`, `race_state`, `race_name`, `total_distance`, `modify_time`, `user_mobile`) values('32','321123','奥拓汽车','11','北京六环环越野赛(测试)','200','2011-03-28 11:57:10',NULL);根据状态(race_state)、total_distance 和时间来确定名次
sql语句为：select rgu.race_state,rgu.car_name,rgu.user_sid, 
rgu.total_distance, (select count(id)+1 from robot_game_user 
where race_state = rgu.race_state and total_distance>rgu.total_distance 
or(total_distance=rgu.total_distance and modify_time<rgu.modify_time) ) as 
race_order,rgu.race_name,rgu.modify_time as race_time from robot_game_user rgu 
where rgu.race_state in('10','11')以上得到的结果有一个很奇怪的bug 不清楚怎么导致的，望高手能已解答！！！"10","奥拓汽车","111","600",        "2"        ,"北京五环越野赛","2011-03-23 10:33:32"
"10","奥拓汽车","629117682","500",  "4",         "北京五环越野赛","2011-03-23 11:00:10"
"10","奥拓汽车","586751610","300",  "7",         "北京五环越野赛","2011-03-23 11:18:39"
"10","奥拓汽车","565556484","600",  "3",        "北京五环越野赛","2011-03-23 15:35:35"
"10","奥拓汽车","629072509","100",  "8",         "北京五环越野赛","2011-03-23 16:07:49"
"10","奥拓汽车","749359178","2200", "1",         "北京五环越野赛","2011-03-24 10:47:18"
"10","奥拓汽车","588589895","400",  "5",          "北京五环越野赛","2011-03-23 16:54:17"
"10","奥拓汽车","5885898952","400", "6",         "北京五环越野赛","2011-03-23 17:02:39""11","奥拓汽车","563791506","1100", "1",          "","2011-03-28 11:31:27"
"11","奥拓汽车","123","500",        "4",          "","2011-03-28 11:01:32"
"11","奥拓汽车","123213","500",     "5"           ,"","2011-03-28 11:39:06"
"11","奥拓汽车","321321","800",     "2",           "","2011-03-28 11:49:50"
"11","奥拓汽车","321123","200",     "5",         "北京六环环越野赛(测试)","2011-03-28 11:57:10"以上状态为10的排序是没有问题的，但是同样的sql在状态为11的时候就出现了问题！！！期待高手解答！！！

解决方案 »

  1.   

    SELECT rgu.race_state,rgu.car_name,rgu.user_sid, 
    rgu.total_distance, (SELECT COUNT(id)+1 FROM robot_game_user 
    WHERE race_state = rgu.race_state AND total_distance>rgu.total_distance 
     OR (race_state = rgu.race_state AND total_distance=rgu.total_distance AND modify_time<rgu.modify_time)) AS 
    race_order,rgu.race_name,rgu.modify_time AS race_time FROM robot_game_user rgu 
    WHERE rgu.race_state IN('10','11');
      

  2.   

    or
    SELECT rgu.race_state,rgu.car_name,rgu.user_sid, 
    rgu.total_distance, (SELECT COUNT(id)+1 FROM robot_game_user 
    WHERE race_state = rgu.race_state AND 
    (total_distance>rgu.total_distance OR (total_distance=rgu.total_distance AND modify_time<rgu.modify_time))) AS 
    race_order,rgu.race_name,rgu.modify_time AS race_time FROM robot_game_user rgu 
    WHERE rgu.race_state IN('10','11');