【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

以上是 【mysql】请教一个mysql查询排序问题 的全部内容， 来源链接： utcz.com/a/72040.html

回到顶部