【讨论中】题目求教：求每个人的最高成绩

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的窗口函数实现

shang2010

要用group by，感觉楼上两位对了

cenalulu

科目名和分数不关连，group by + max显然是不对的。
楼主可以搜下 group by top 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这种分数都一样的情况：
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的话，这个恐怕不行。。。

@ 姚福坚
的最简单