返回Min()和另一个字段? [英] Returning a Min() and another field?

问题描述

我正在建立排行榜，并希望返回MIN()值-但是，我还希望另一个字段与最小值位于同一行.

I'm building a leaderboard and want to return a MIN() value - however, I also want another field which is on the same row as the min value.

我到目前为止有这个:

SELECT u.username, MIN(timer) AS intScore, l.hashtag 
        FROM users u 
        JOIN leaderboard l ON l.users_id = u.users_id 
        GROUP BY u.username
        ORDER BY 
        intScore ASC

...这将返回正确的MIN intScore，但不会返回正确的井号.

...this returns the correct MIN intScore, but not the correct hashtag.

数据库看起来像这样:

排行榜数据库:

users_id, timer, hashtag
1, 10, TEST
2, 12, TEST
1, 20, TEST
3, 15, TEST
1, 5, LIVE
4, 20, LIVE

我希望结果是:

users_id, timer, hashtag
1, 5, LIVE
2, 12, TEST
3, 15, TEST
4, 20, LIVE

任何帮助将不胜感激

推荐答案

问题是这样的:任何聚合函数都不关心"其其余行 如果不是MIN而是SUM，则更容易看到...

The thing is this: any aggregate function "doesn't care" about the rest of its line If it wasn't MIN but SUM, its easier to see...

该解决方案有些棘手，您应该执行涉及不等式的LEFT JOIN的事情:

The solution is a bit tricky, you should do something that involves LEFT JOIN with an inequality :

SELECT u.username, l.timer AS intScore, l.hashtag

FROM
 leaderboard l
 INNER JOIN users u ON u.users_id = l.users_id
 LEFT JOIN leaderboard l2 ON l.users_id = l2.users_id AND l2.timer < l.timer
WHERE
 l2. users_id IS NULL
ORDER BY intScore ASC

通过跳过MIN函数并获取整行来获得最低价值

The idea is getting lowest value by skipping the MIN function and getting the entire row

从排行榜表格中按日期(或与此问题相关的其他任何列/条件)进行过滤，将需要我们对我们使用的每个表格进行过滤 在ON条件下过滤LEFT JOINed表很重要，否则我们将消除NULL过滤效果:

Filtering by date (or any other column / criteria for that matter) from the leaderboard table, will require us to filter each table we use It's important to filter the LEFT JOINed table at the ON conditition, otherwise we eliminate the filtering NULLs effect:

SET @date0 = '2018-01-01';
SELECT u.username, l.timer AS intScore, l.hashtag

FROM
 leaderboard l
 INNER JOIN users u ON u.users_id = l.users_id
 LEFT JOIN leaderboard l2 ON l.users_id = l2.users_id AND l2.timer < l.timer AND l2.game_date >= @date0
WHERE
 l.game_date >= @date0
 l2.users_id IS NULL
ORDER BY intScore ASC

希望有帮助

这篇关于返回Min()和另一个字段?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！