请教高效的分组排序sql

cenalulu

是同一个。。。但是我不知道那个只发了3贴的那个号怎么来的。。。。很晕啊。。。

sunnyfun

原帖由 zyrf2001 于 2009-5-26 10:21 发表
if(paper_id=@paper_id,@total:=@total+1, @total:=1) as temp,
                                  if(paper_id=@paper_id,null, @rank:=1) as temp1,        
                                  if(paper_ ...

都写成这样了还用啥存储过程啊，直接就搞定了：

DROP TABLE IF EXISTS tmp_order; CREATE TABLE tmp_order( paper_id int, class_student_id int, score numeric( 6 , 2 ) , order_in_town int ); SET @scorenow =99999 ; SET @rank =0 ; SET @total =0 ; SET @paper_id =0 ; INSERT INTO tmp_order( paper_id, class_student_id, order_in_town, score ) SELECT     paper_id,     class_student_id,     IF (             (                 IF (                     paper_id = @paper_id , @total := @total +1 , (                         @total :=1                         AND @rank :=1                         AND @paper_id := paper_id                     )                 )                 AND score < @scorenow             ),             @rank := @total , @rank := @rank     ) AS mrank,     (         @scorenow := score     ) AS mscore FROM tmp_subject_score ORDER BY paper_id, score DESC;

jb96_xlwang

[root@XXXXX home]# time mysql test -e "call show_mark_memory();" > xx1.result

real    0m0.152s
user    0m0.040s
sys     0m0.024s
[root@XXXXX home]# time mysql test -e "call show_mark();" > xx1.result

real    0m0.157s
user    0m0.045s
sys     0m0.016s
[root@XXXXX home]# time mysql test -e "call show_mark2();" > xx1.result

real    0m0.099s
user    0m0.005s
sys     0m0.004s
[root@XXXXX home]# time mysql test < orderScore.SQL  >xx.result

real    0m0.070s
user    0m0.048s
sys     0m0.020s

[root@XXXXX home]# cat orderScore.SQL

select A.class_student_id,A.paper_id,A.score,B.OrderNo From tmp_subject_score A , ( select X.paper_id,X.score,Sum(Y.CC) as OrderNo   From (select distinct paper_id,score From tmp_subject_score ) X ,        (select paper_id,score,count(*) as CC From tmp_subject_score          group by paper_id,score ) Y   where X.paper_id=Y.paper_id and X.score <=Y.score   Group by X.paper_id,X.score ) B where A.paper_id=B.paper_id and A.score=B.score

mysql> explain select A.class_student_id,A.paper_id,A.score,B.OrderNo
    -> From tmp_subject_score A ,
    -> ( select X.paper_id,X.score,Sum(Y.CC) as OrderNo
    ->   From (select distinct paper_id,score From tmp_subject_score ) X ,
    ->        (select paper_id,score,count(*) as CC From tmp_subject_score
    ->          group by paper_id,score ) Y
    ->   where X.paper_id=Y.paper_id and X.score <=Y.score
    ->   Group by X.paper_id,X.score ) B
    -> where A.paper_id=B.paper_id and A.score=B.score  ;
+----+-------------+-------------------+-------+---------------+-------+---------+--------------------+-------+---------------------------------+
| id | select_type | table             | type  | possible_keys | key   | key_len | ref                | rows  | Extra                           |
+----+-------------+-------------------+-------+---------------+-------+---------+--------------------+-------+---------------------------------+
|  1 | PRIMARY     | <derived2>        | ALL   | NULL          | NULL  | NULL    | NULL               |   583 |                                 |
|  1 | PRIMARY     | A                 | ref   | idx_1         | idx_1 | 7       | B.paper_id,B.score |    41 |                                 |
|  2 | DERIVED     | <derived3>        | ALL   | NULL          | NULL  | NULL    | NULL               |   583 | Using temporary; Using filesort |
|  2 | DERIVED     | <derived4>        | ALL   | NULL          | NULL  | NULL    | NULL               |   583 | Using where                     |
|  4 | DERIVED     | tmp_subject_score | index | NULL          | idx_1 | 7       | NULL               | 24000 | Using index                     |
|  3 | DERIVED     | tmp_subject_score | range | NULL          | idx_1 | 7       | NULL               |   586 | Using index for group-by        |
+----+-------------+-------------------+-------+---------------+-------+---------+--------------------+-------+---------------------------------+
6 rows in set (0.16 sec)

[ 本帖最后由 jb96_xlwang 于 2009-6-10 23:29 编辑 ]

zyrf2001

结果有出入

create table xlwang
select A.class_student_id,A.paper_id,A.score,B.OrderNo
From tmp_subject_score A ,
( select X.paper_id,X.score,Sum(Y.CC) as OrderNo
  From (select distinct paper_id,score From tmp_subject_score ) X ,
       (select paper_id,score,count(*) as CC From tmp_subject_score
         group by paper_id,score ) Y
  where X.paper_id=Y.paper_id and X.score <=Y.score
  Group by X.paper_id,X.score ) B
where A.paper_id=B.paper_id and A.score=B.score


SELECT * FROM xlwang order by paper_id, OrderNo, score
9168        10        116.00        1
9153        10        114.00        2
9416        10        113.00        4
9513        10        113.00        4
6499        10        112.00        6
9254        10        112.00        6
9240        10        111.00        10
6728        10        111.00        10
6501        10        111.00        10
10218        10        111.00        10
6553        10        110.00        12
10220        10        110.00        12
9232        10        109.00        16
9184        10        109.00        16
9212        10        109.00        16
9154        10        109.00        16
10647        10        108.00        27
9235        10        108.00        27
9238        10        108.00        27
9199        10        108.00        27
9171        10        108.00        27
9167        10        108.00        27
7807        10        108.00        27
9961        10        108.00        27
6895        10        108.00        27
6894        10        108.00        27
6953        10        108.00        27
9504        10        107.00        37
9241        10        107.00        37
9185        10        107.00        37
9187        10        107.00        37
9173        10        107.00        37
9164        10        107.00        37
9126        10        107.00        37
6566        10        107.00        37
6563        10        107.00        37
6558        10        107.00        37
9169        10        106.00        57
9327        10        106.00        57
9320        10        106.00        57
9367        10        106.00        57
9396        10        106.00        57
9385        10        106.00        57
9964        10        106.00        57
10070        10        106.00        57
10641        10        106.00        57
9155        10        106.00        57
9174        10        106.00        57
9974        10        106.00        57
6559        10        106.00        57
6756        10        106.00        57
6909        10        106.00        57
7547        10        106.00        57
7811        10        106.00        57
8031        10        106.00        57
8653        10        106.00        57
9138        10        106.00        57
9465        10        105.00        90
9483        10        105.00        90
9489        10        105.00        90
9491        10        105.00        90
9438        10        105.00        90
9302        10        105.00        90
9216        10        105.00        90
9457        10        105.00        90
9521        10        105.00        90
9971        10        105.00        90

cenalulu

select A.class_student_id,A.paper_id,A.score,B.OrderNo
From tmp_subject_score A ,
( select X.paper_id,X.score,Sum(Y.CC)-Y.CC+1 as OrderNo
  From (select distinct paper_id,score From tmp_subject_score ) X ,
       (select paper_id,score,count(*) as CC From tmp_subject_score
         group by paper_id,score ) Y
  where X.paper_id=Y.paper_id and X.score <= Y.score
  Group by X.paper_id,X.score ) B
where A.paper_id=B.paper_id and A.score=B.score ;

他把同分数的人数也算到排名里去了，稍微修改了下。

[ 本帖最后由 cenalulu 于 2009-6-16 19:55 编辑 ]

zyrf2001

嗯，跳出了常规思路，值得借鉴，但是效率上并不是最强

mysql> SET @scorenow =99999 ;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @rank =0 ;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @total =0 ;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @paper_id =0 ;
Query OK, 0 rows affected (0.00 sec)

mysql> create table  cenalulu
    -> SELECT
    ->     paper_id,
    ->     class_student_id,
    ->     IF (
    ->             (
    ->                 IF (
    ->                     paper_id = @paper_id , @total := @total +1 , (
    ->                         @total :=1
    ->                         AND @rank :=1
    ->                         AND @paper_id := paper_id
    ->                     )
    ->                 )
    ->                 AND score < @scorenow
    ->             ),
    ->             @rank := @total , @rank := @rank
    ->     ) AS mrank,
    ->     (
    ->         @scorenow := score
    ->     ) AS mscore
    -> FROM tmp_subject_score
    -> ORDER BY paper_id, score DESC;
Query OK, 24000 rows affected (0.11 sec)
Records: 24000  Duplicates: 0  Warnings: 0

mysql> create table xlwang
    -> select A.class_student_id,A.paper_id,A.score,B.OrderNo
    -> From tmp_subject_score A ,
    -> ( select X.paper_id,X.score,Sum(Y.CC)-Y.CC+1 as OrderNo
    ->   From (select distinct paper_id,score From tmp_subject_score ) X ,
    ->        (select paper_id,score,count(*) as CC From tmp_subject_score
    ->          group by paper_id,score ) Y
    ->   where X.paper_id=Y.paper_id and X.score <= Y.score
    ->   Group by X.paper_id,X.score ) B
    -> where A.paper_id=B.paper_id and A.score=B.score;
Query OK, 24000 rows affected (0.31 sec)
Records: 24000  Duplicates: 0  Warnings: 0

sunnyfun

原帖由 zyrf2001 于 2009-6-17 09:42 发表
嗯，跳出了常规思路，值得借鉴，但是效率上并不是最强

mysql> SET @scorenow =99999 ;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @rank =0 ;
Query OK, 0 rows affected (0.00 sec)

mysql>  ...

一个select VS 四个select