还是请教一个交叉排列order by问题

Question

tab1
name  num  order_id（1，2，3，4，5，6，……）
张三  10        0
张三  10        1
张三  11        0
张三  11        1
张三  12        0
张三  13        1
为了让看得清楚，李四等其他记录就列一条
李四  12        1
数据量大约100万左右，这个表要怎么建立索引？然后想用一条语句取得如下效果
张三  12        0
张三  13        1
张三  11        0
张三  11        1
张三  10        0
张三  10        1
也就是order_id交叉排列，num按每个order_id从大到小排列以前csdn有朋友给出如下语句
select * from tab1 a where name='张三' order by (select count(*) from tab1 where order_id=a.order_id and num>a.num and name='张三'),order_id desc
但是经过测试，这条语句在3000数据量下选500条就会出现cpu峰值占到90%的现象，也可能是我mysql优化不够
原帖请看：http://topic.csdn.net/u/20091109/11/4da9ff55-2467-409a-ad42-e77903c7df8a.html

Answer 1

SELECT * FROM tab1 WHERE name='张三' ORDER BY num DESC,num ASC

Answer 2

这个num是不一定的，要先排order_id再排num，所以这样不行

Answer 3

mysql> select * from t_advancejar;
+------+------+----------+
| name | num  | order_id |
+------+------+----------+
| 张三 |   10 |        0 |
| 张三 |   10 |        1 |
| 张三 |   11 |        0 |
| 张三 |   11 |        1 |
| 张三 |   12 |        0 |
| 张三 |   13 |        1 |
| 李四 |   12 |        1 |
+------+------+----------+
7 rows in set (0.00 sec)mysql> select *
    -> from t_advancejar t
    -> order by name,
    ->  (select count(*) from t_advancejar where name=t.name and order_id=t.order_id and num>t.num),
    ->  order_id;
+------+------+----------+
| name | num  | order_id |
+------+------+----------+
| 李四 |   12 |        1 |
| 张三 |   12 |        0 |
| 张三 |   13 |        1 |
| 张三 |   11 |        0 |
| 张三 |   11 |        1 |
| 张三 |   10 |        0 |
| 张三 |   10 |        1 |
+------+------+----------+
7 rows in set (0.00 sec)mysql>

Answer 4

这个语句对每个记录都会运算一次  (select count(*) from t_advancejar where name=t.name and order_id=t.order_id and num>t.num), 然后才能排序，效率比较差。 你可以试一下，创建 （name，order_id，num）这个复合索引，然后再看一下效率如何。

Answer 5

好象也没提高多少。如果效率仍不满意，则只能通过存储过程，或者你的PHP程序本身来实现这个功能。毕竟数据库中SQL语句实现这些东西效率并不好。

Answer 6

在程序中打开两个记录集select * from t_advancejar where order_id =0 order by name ,num  desc;select * from t_advancejar where order_id =1 order by name ,num  desc;然后用程序依次从两个结果集中输出。注意 name 的匹配。

Answer 7

select a.name,a.num,a.order_id
from tty a left join tty b 
on a.name=b.name and a.order_id=b.order_id and a.num>=b.num
group by a.name,a.num,a.order_id
order by a.name,count(b.order_id) desc,a.num,a.order_id