mysql 分组查询前几名_【MYSQL分组查询前1或者前几名排名】

测试库表：

CREATE TABLE test(NAME VARCHAR(10),val INT,memo VARCHAR(20));

INSERT INTO test VALUES('a', 2, 'a2--a的第二个值') ;

INSERT INTO test VALUES('a', 1, 'a1--a的第一个值') ;

INSERT INTO test VALUES('a', 3, 'a3--a的第三个值') ;

INSERT INTO test VALUES('b', 1, 'b1--b的第一个值') ;

INSERT INTO test VALUES('b', 3, 'b3--b的第三个值') ;

INSERT INTO test VALUES('b', 2, 'b2--b的第二个值') ;

INSERT INTO test VALUES('b', 4, 'b2--b的第四个值') ;

INSERT INTO test VALUES('b', 5, 'b2--b的第五个值') ;

go

查询：

--方法1：

SELECT

a.*

FROM

test a

WHERE val =

(SELECT

MAX(val)

FROM

test

WHERE NAME = a.name)

ORDER BY a.name;

--方法2：

SELECT

a.*

FROM

test a

WHERE NOT EXISTS

(SELECT

1

FROM

test

WHERE NAME = a.name

AND val > a.val);

--方法3：

SELECT

a.*

FROM

test a,

(SELECT

NAME,

MAX(val) val

FROM

test

GROUP BY NAME) b

WHERE a.name = b.name

AND a.val = b.val

ORDER BY a.name ;

--方法4：

SELECT

a.*

FROM

test a

INNER JOIN

(SELECT

NAME,

MAX(val) val

FROM

test

GROUP BY NAME) b

ON a.name = b.name

AND a.val = b.val

ORDER BY a.name ;

--方法5

SELECT

a.*

FROM

test a

WHERE 1 >

(SELECT

COUNT(*)

FROM

test

WHERE NAME = a.name

AND val > a.val)

ORDER BY a.name;

/*

"name""val""memo"

"a""3""a3--a的第三个值"

"b""5""b2--b的第五个值"

*/

仅供参考