H5W3
当前位置:H5W3 > mysql > 正文

【mysql】请教一个mysql查询排序问题

请教一个mysql查询排序问题,数据库数据如下,请问如何取出每个人的最好成绩(score越大,time越小,则成绩越好),然后再进行排序呢?也就是将红框中的数据取出,其他舍弃。谢谢。
【mysql】请教一个mysql查询排序问题

测试数据如下:

DROP TABLE IF EXISTS t;
CREATE TABLE t (
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
time int(11) NULL DEFAULT NULL,
score int(11) NULL DEFAULT NULL,
PRIMARY KEY (id) USING BTREE
) ENGINE = MyISAM AUTO_INCREMENT = 10 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

— —————————-
— Records of t
— —————————-
INSERT INTO t VALUES (1, ‘小明’, 24, 66);
INSERT INTO t VALUES (2, ‘小刚’, 19, 72);
INSERT INTO t VALUES (3, ‘小红’, 25, 47);
INSERT INTO t VALUES (4, ‘小王’, 30, 99);
INSERT INTO t VALUES (5, ‘小明’, 31, 72);
INSERT INTO t VALUES (6, ‘小红’, 25, 50);
INSERT INTO t VALUES (7, ‘小明’, 30, 80);
INSERT INTO t VALUES (8, ‘小王’, 40, 86);
INSERT INTO t VALUES (9, ‘小天’, 11, 72);
INSERT INTO t VALUES (10, ‘小王’, 20, 99);

SET FOREIGN_KEY_CHECKS = 1;

回答

select * from t
where
(name,score) in (select name,max(score) from t group by name)
and
(name,time) in (select name,min(time) from t where (name,score) in (select name,max(score) from t group by name) group by name);

我写了个, 您试试
select a.* from t a where (select count(1) from t where score>a.score and time<a.time) < 1;

如果成绩和时间没有比重要求的话,是不是换个思路,score – time 越大越好,然后按照这个数值直接降序查询就完事了,忘了,还要根据name group by

可以把每个人的最大值查出来,再连表查询排序
SELECT * FROM t INNER JOIN (SELECT MAX(score) as score, name FROM t GROUP BY name) as t2 ON t.score = t2.score AND
t.name = t2.name ORDER BY t.score DESC, t.time

本文地址:H5W3 » 【mysql】请教一个mysql查询排序问题

评论 0

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址