表A:
name value order
------ ------- -------
member 1 0
member 4 1
member 2 2
member 10 3
member 9 4表B:
uid name
----- ------
1 aaa
2 bbb
3 zzz
4 ccc
9 ddd
10 eee
11 fff
12 ggg
13 hhh表A的 value 与 表B的 uid 关系。
在查询时想得到如下的结果为: uid name
----- ------
1 aaa
4 ccc
2 bbb
10 eee
9 ddd
3 zzz
11 fff
12 ggg
13 hhh
非常感谢!!
order by ifnull(a.`order`,99999999),b.uid;
+------+------+
| uid | name |
+------+------+
| 1 | aaa |
| 4 | ccc |
| 2 | bbb |
| 10 | eee |
| 9 | ddd |
| 3 | zzz |
| 11 | fff |
| 12 | ggg |
| 13 | hhh |
+------+------+
9 rows in set (0.00 sec)
from b left join a on b.uid=a.value
order by a.order,b.uid
from b left join a on b.uid=a.value
order by case when a.name is null then 0 else 1 end,a.order,b.uid
order by if(b.order is null,a.uid,-999999+b.order)