MySQL查询每科成绩前1名

建表语句

CREATE TABLE `testclass` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `kecheng` varchar(20) DEFAULT NULL,
  `fenshu` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

插入语句

INSERT INTO testclass (id, name, kecheng, fenshu) VALUES (1, 张三, 语文, 10);
INSERT INTO testclass (id, name, kecheng, fenshu) VALUES (2, 张三, 数学, 80);
INSERT INTO testclass (id, name, kecheng, fenshu) VALUES (3, 李四, 语文, 50);
INSERT INTO testclass (id, name, kecheng, fenshu) VALUES (4, 李四, 外语, 100);
INSERT INTO testclass (id, name, kecheng, fenshu) VALUES (5, 王五, 语文, 66);
INSERT INTO testclass (id, name, kecheng, fenshu) VALUES (6, 王五, 数学, 79);

select * from testclass;

查询每科成绩前一名

SELECT t1.name,t1.kecheng,t1.fenshu FROM testclass t1 WHERE
(
SELECT count(1) FROM testclass t2 WHERE
t1.kecheng=t2.kecheng AND t2.fenshu>=t1.fenshu
)<=1
ORDER BY t1.kecheng,t1.fenshu DESC

一个函数介绍:group_concat

group_concat函数，它能将两条或者多条单个记录合并成为一条单个就记录