【討論中】題目求教：求每個人的最高成績

ipx-spx

1. master>select * from result;
   
2. +----+-------+-------------+-------+
   
3. | id | name  | subject     | score |
   
4. +----+-------+-------------+-------+
   
5. |  1 | tom   | english     |    92 |
   
6. |  2 | tom   | Mathematics |    99 |
   
7. |  3 | jack  | english     |    95 |
   
8. |  4 | susan | english     |   100 |
   
9. |  5 | jimmy | english     |    97 |
   
10. |  6 | john  | english     |    77 |
    
11. |  7 | jack  | Mathematics |    99 |
    
12. |  8 | susan | Mathematics |   100 |
    
13. |  9 | jimmy | Mathematics |    99 |
    
14. | 10 | john  | Mathematics |    91 |
    
15. | 11 | tom   | Science     |    71 |
    
16. | 12 | jack  | Science     |    92 |
    
17. | 13 | susan | Science     |   100 |
    
18. | 14 | jimmy | Science     |    87 |
    
19. | 15 | john  | Science     |   100 |
    
20. | 16 | tom   | sport       |   100 |
    
21. | 17 | jack  | sport       |    97 |
    
22. +----+-------+-------------+-------+
    
23. 17 rows in set (0.00 sec)

復制代碼

最總顯示每個人最高成績，顯示name、subject、score三個字段

1. +----+-------+-------------+-------+
   
2. | id | name  | subject     | score |
   
3. +----+-------+-------------+-------+
   
4. |  7 | jack  | Mathematics |    99 |
   
5. |  9 | jimmy | Mathematics |    99 |
   
6. | 15 | john  | Science     |   100 |
   
7. |  4 | susan | english     |   100 |
   
8. | 16 | tom   | sport       |   100 |
   
9. +----+-------+-------------+-------+
   
10. 5 rows in set (0.00 sec)

復制代碼

wenhq

select id,name,subject,max(score) from result  group by name ;?

wang1352083

用mysql 的set var 來模擬postgresql的窗口函數(shù)實現(xiàn)

shang2010

要用group by，感覺樓上兩位對了

cenalulu

科目名和分數(shù)不關(guān)連，group by + max顯然是不對的。
樓主可以搜下 group by top n 的關(guān)鍵字，方法有很多種就不贅述了

huowz

2樓的恐怕不行,

1. SELECT SUBSTRING_INDEX(score,'-',-1) AS id
   
2.        ,NAME
   
3.        ,MID(SUBSTRING_INDEX(score,'-',2),5) AS subject
   
4.        ,TRIM(LEADING '0' FROM SUBSTRING_INDEX(score,'-',1)) AS score
   
5. FROM (SELECT NAME
   
6.       ,MAX(CONCAT(LPAD(score,3,'0'),'-',`subject`,'-',`id`)) AS score
   
7. FROM result
   
8. GROUP BY 1) t1

復制代碼

這個應該可以，
并且要是不要id的話，

1. SELECT NAME
   
2.        ,MID(score,5) AS subject
   
3.        ,TRIM(LEADING '0' FROM SUBSTRING_INDEX(score,'-',1)) AS score
   
4. FROM (SELECT NAME
   
5.       ,MAX(CONCAT(LPAD(score,3,'0'),'-',`subject`)) AS score
   
6. FROM result
   
7. GROUP BY 1) t1

復制代碼

這樣就行！

alsmn

下面這條性能應該還可以，但是不適用susan這種分數(shù)都一樣的情況：
SELECT NAME,SUBJECT,score FROM result r1 WHERE ( SELECT COUNT(*) FROM result r2 WHERE r2.NAME=r1.NAME AND
                                                 r2.score >= r1.score) <= 1;
下面這條如果索引合適，應該也還行：
(SELECT * FROM result WHERE NAME = 'jack' ORDER BY score LIMIT 1)
UNION ALL
(SELECT * FROM result WHERE NAME = 'jimmy' ORDER BY score LIMIT 1)
UNION ALL
(SELECT * FROM result WHERE NAME = 'john' ORDER BY score LIMIT 1)
UNION ALL
(SELECT * FROM result WHERE NAME = 'tom' ORDER BY score LIMIT 1)
UNION ALL
(SELECT * FROM result WHERE NAME = 'susan' ORDER BY score LIMIT 1);

姚福堅

select id,name,subject,max(score) from (select id,name,subject,score from result order by score  desc ) as t group by name ;

huowz

那這樣應該也可以

1. SELECT NAME
   
2.       ,SUBSTRING_INDEX(GROUP_CONCAT(subject ORDER BY score DESC),',',1)
   
3.       ,MAX(score)
   
4. FROM result
   
5. GROUP BY NAME
   

復制代碼

只是不知道哪個效率更高，希望樓主可以測一下。

q15928

回復 9# huowz

如果需要Id的話，這個恐怕不行。。。

@ 姚福堅
的最簡單