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

[advancejar]

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

[advancejar]

如果我这样设计表不对，请帮忙指导下，谢谢

[阿_布]

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

[advancejar]

[Quote=引用 2 楼 zhoupuyue 的回复:]
SQL codeSELECT*FROM tab1WHERE name='张三'ORDERBY numDESC,numASC
[/Quote]

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

[ACMAIN_CHM]

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>

[ACMAIN_CHM]

这个语句对每个记录都会运算一次 (select count(*) from t_advancejar where name=t.name and order_id=t.order_id and num>t.num), 然后才能排序，效率比较差。

你可以试一下，创建 （name，order_id，num）这个复合索引，然后再看一下效率如何。

[advancejar]

创建了索引，本来1.01秒，现在0.99，快了一个数量级了

[ACMAIN_CHM]

好象也没提高多少。

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

[advancejar]

好的，谢谢

[ACMAIN_CHM]

在程序中打开两个记录集

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 的匹配。

[WWWWA]

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