推奨:MAX関数とGROUP BY文で使用されるエラー

MAX関数やGROUPを使うと予想外のデータがSELECTされます.
簡単な例を示します.
SCOREIDごとに数学の成績が一番高い点数を知りたいです.
テーブル情報:
/*DDL Information For - test.lkscore*/
--------------------------------------
Table    Create Table                                                                
-------  -----------------------------------------------------------------------------
lkscore  CREATE TABLE `lkscore` (                                                    
           `scoreid` int(11) DEFAULT NULL,                                           
           `chinese` int(11) DEFAULT '0',                                            
           `math` int(11) DEFAULT '0',                                               
           KEY `fk_class` (`scoreid`),                                               
           CONSTRAINT `fk_class` FOREIGN KEY (`scoreid`) REFERENCES `lkclass` (`id`) 
         ) ENGINE=InnoDB DEFAULT CHARSET=gb2312                                       select * from lkscore;
query result(12 records)
scoreid
chinese
math
1
90
80
2
100
99
3
29
98
4
87
79
5
89
99
1
49
98
3
98
56
2
76
88
2
80
90
3
90
70
1
90
90
1
67
90
エラーSELECTselect scoreid,chinese,max(math) max_math from lkscore group by scoreid;
query result(5 records)
scoreid
chinese
max_math
1
90
98
2
100
99
3
29
98
4
87
79
5
89
99
上の90は明らかに間違っています.
方法1:select scoreid,chinese,math max_math from
( select * from lkscore order by math desc
) T
group by scoreid;
query result(5 records)
scoreid
chinese
max_math
1
49
98
2
100
99
3
29
98
4
87
79
5
89
99
方法2:select * from lkscore a where a.math = (select max(math) from lkscore where scoreid = a.scoreid) order by scoreid asc;
query result(5 records)
scoreid
chinese
max_math
1
49
98
2
100
99
3
29
98
4
87
79
5
89
99
これもMAX関数を使用しており、関連サブクエリも使用されています.
この2つの効率を見てみましょう.explain
select scoreid,chinese,math max_math from (select * from lkscore order by math desc) T group by scoreid;
query result(2 records)
id
select_type
table
type
possible_keys
key
key_len
ref
rows
Extra
1
PRIMARY

ALL
(NULL)
(NULL)
(NULL)
(NULL)
12
Using temporary; Using filesort
2
DERIVED
lkscore
ALL
(NULL)
(NULL)
(NULL)
(NULL)
12
Using filesort
明らかに、FULL TABLE SCANが2つあります.
explain
select scoreid,chinese,math max_math from lkscore a where a.math =
(select max(math) from lkscore where scoreid = a.scoreid) order by scoreid asc;
query result(2 records)
id
select_type
table
type
possible_keys
key
key_len
ref
rows
Extra
1
PRIMARY
a
index
(NULL)
fk_class
5
(NULL)
12
Using where
2
DEPENDENT SUBQUERY
lkscore
ref
fk_class
fk_class
5
a.scoreid
1
Using where
2つ目はKEYで、サブクエリではジャンプレコードしかスキャンされませんでした.
明らかです.この場合、2番目は1番目より効率が高いです.
本文は“神様、私たちは会わないと散らない!”のブログから出て、転載して作者と連絡してください!
 
 
推奨:MAX関数とGROUP BY文で使用されるエラーhttp://yueliangdao0608.blog.51cto.com/397025/81278