Question

mysql 分组统计

user表
id name val
1 a 56
2 b 40
3 a 78
4 b 60
......

查找出a,b,..最高分所对应的记录如
id name val
3 a 78
4 b 60
......
select * from user group by name order by val desc 不能找到最高分
请问要怎么写这条语句
CREATE TABLE IF NOT EXISTS `user` (
`id` tinyint(2) NOT NULL,
`name` varchar(10) NOT NULL,
`val` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `user` (`id`, `name`, `val`) VALUES
(1, 'a', 56),
(2, 'b', 40),
(3, 'a', 78),
(4, 'b', 60);

Answer 1

SELECT (
SELECT s1.`id`
FROM `user` AS s1
WHERE s1.val=MAX(s.`val`)
)
AS id , s.`name` , MAX(s.`val`)
AS maxval
FROM `user` AS s
GROUP BY s.`name` ORDER BY s.val DESC

Answer 2

其实很简单，select后面条件加上下面这个就可以了。
select id,name,max(val) from user group by name order by val desc

追问

id 不对

追答

select * from user where val in (select max(val) from user group by name) ;

Answer 3

select id,name, max(val) as val from user group by name;