MySQL SQL SELECT 按 2 列排序和分组依据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1414123/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL SELECT order by 2 columns and group by
提问by
Here are the RS return and the SQL issued,
这是 RS 返回和发出的 SQL，
SELECT *, (UNIX_TIMESTAMP(end_time) - UNIX_TIMESTAMP(start_time)) AS T
FROM games
WHERE game_status > 10
ORDER BY status, T;
game_id, player_id, start_time, end_time, score, game_status, is_enabled, T
65, 22, '2009-09-11 17:50:35', '2009-09-11 18:03:07', 17, 11, 1, 752
73, 18, '2009-09-11 18:55:07', '2009-09-11 19:09:07', 30, 11, 1, 840
68, 20, '2009-09-11 18:03:08', '2009-09-11 18:21:52', 48, 11, 1, 1124
35, 18, '2009-09-11 15:46:05', '2009-09-11 16:25:10', 80, 11, 1, 2345
13, 8, '2009-09-11 12:33:31', '2009-09-11 15:21:11', 40, 11, 1, 10060
11, 5, '2009-09-11 12:22:34', '2009-09-11 15:21:42', 55, 11, 1, 10748
34, 17, '2009-09-11 15:45:43', '2009-09-11 21:00:45', 49, 11, 1, 18902
2, 1, '2009-09-10 20:46:59', '2009-09-11 23:45:21', 3, 11, 1, 97102
84, 1, '2009-09-11 23:51:29', '2009-09-11 23:51:42', 10, 12, 1, 13
I 'd like to group by player_id, (i.e. take the best result each Player_id, it's determined by "game_status - the min", and the time T,
我想按 player_id 分组，（即取每个 Player_id 的最佳结果，它由“game_status - min”和时间 T 决定，
so I added a group by clause, but it doesn't return the min
所以我添加了一个 group by 子句，但它没有返回 min
SELECT *, (UNIX_TIMESTAMP(end_time) - UNIX_TIMESTAMP(start_time)) AS T
FROM games
WHERE game_status > 10
GROUP BY player_id
ORDER BY game_status, T;
35, 18, '2009-09-11 15:46:05', '2009-09-11 16:25:10', 80, 11, 1, 2345
13, 8, '2009-09-11 12:33:31', '2009-09-11 15:21:11', 40, 11, 1, 10060
34, 17, '2009-09-11 15:45:43', '2009-09-11 21:00:45', 49, 11, 1, 18902
1, 1, '2009-09-10 20:39:44', '2009-09-10 20:41:21', 10, 12, 1, 97
24, 12, '2009-09-11 14:46:06', '2009-09-11 14:53:30', 10, 12, 1, 444
5, 3, '2009-09-11 10:56:22', '2009-09-11 11:13:01', 11, 12, 1, 999
37, 20, '2009-09-11 15:51:13', '2009-09-11 16:15:04', 14, 12, 1, 1431
79, 31, '2009-09-11 20:34:17', '2009-09-11 20:43:29', 4, 13, 1, 552
18, 9, '2009-09-11 13:09:47', '2009-09-11 18:33:10', 2, 13, 1, 19403
72, 30, '2009-09-11 18:46:29', '2009-09-11 18:48:44', 0, 14, 1, 135
40, 22, '2009-09-11 16:12:39', '2009-09-11 16:18:23', 3, 14, 1, 344
8, 5, '2009-09-11 12:15:54', '2009-09-11 12:21:48', 25, 14, 1, 354
85, 33, '2009-09-12 01:14:01', '2009-09-12 01:20:43', 0, 14, 1, 402
22, 11, '2009-09-11 13:50:41', '2009-09-11 13:57:24', 7, 14, 1, 403
SELECT *, min(UNIX_TIMESTAMP(end_time) - UNIX_TIMESTAMP(start_time)) AS T
FROM games
WHERE game_status > 10
GROUP BY player_id
ORDER BY game_status, T;
If I select min(T), it doesn't return the min row, but the min value on the hold column.
如果我选择 min(T)，它不会返回最小行，而是返回保持列上的最小值。
I'd searched for some method with self-join, say, http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/
我搜索了一些自加入的方法，比如http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in- sql/
The subquery SELECT for min(), but I can't issue two min() on two columns as it doesn't return the specific rows I want.
min() 的子查询 SELECT，但我不能在两列上发出两个 min()，因为它不返回我想要的特定行。
select type, min(price) as minprice
from fruits
group by type;
I hope there's a way as a filter on the first SQL to remove the duplicated player_id rows.
我希望有一种方法可以作为第一个 SQL 的过滤器来删除重复的 player_id 行。
回答by Eric
From what I can gather, you want to see what the minimum time was on the highest game_statusfor a given player_id, game_idcombination. Try this:
据我所知，你希望看到什么的最短时间是在最高game_status为给定的player_id，game_id组合。尝试这个：
select
g1.game_id,
g1.player_id,
min(UNIX_TIMESTAMP(g1.end_time) - UNIX_TIMESTAMP(g1.start_time)) as t,
g1.game_status
from
games g1
inner join (select game_id, player_id, max(game_status) as max_status
from games where game_status > 10) g2 on
g1.game_id = g2.game_id
and g1.player_id = g2.player_id
and g1.game_status = g2.max_status
group by
g1.game_id,
g1.player_id,
g1.game_status
order by
g1.player_id,
g1.game_id,
g1.game_status,
T
回答by Jonathan Leffler
I'm a little unsure of some phrases in your question, but you need to do a nested SELECT operation along the following lines:
我有点不确定您的问题中的一些短语，但您需要按照以下几行进行嵌套的 SELECT 操作：
SELECT g.*
FROM (SELECT *,
(UNIX_TIMESTAMP(g.end_time) - UNIX_TIMESTAMP(g.start_time)) AS t
FROM games
) AS g
JOIN (SELECT player_id,
MIN(UNIX_TIMESTAMP(end_time) -
UNIX_TIMESTAMP(start_time)) AS min_t
FROM games
WHERE game_status > 10
GROUP BY player_id
) AS r
ON g.player_id = r.player_id AND g.t = r.min_t
ORDER BY game_status, g.t;
The 'r' query returns the player ID and the corresponding minimum time for that player; that is joined with the main table fetching all the rows for that player with the same minimum time. Generally, that will be one entry, but if someone has two games with the same time, the query will return both.
'r' 查询返回玩家 ID 和该玩家对应的最短时间；它与主表连接，以相同的最短时间获取该玩家的所有行。通常，这将是一个条目，但如果有人同时有两场比赛，则查询将同时返回。
I'm not clear if there's another way of disambiguating the results set; there might be.
我不清楚是否有另一种消除结果集歧义的方法；可能有。
回答by Jonathan Leffler
Thanks for the replies.
感谢您的回复。
I am looking for Eric and Jonathan 's solution.
我正在寻找 Eric 和 Jonathan 的解决方案。
Let me explain in detail.
让我详细解释一下。
As Eric mentioned, I am seeking for the result from game_status and min time(T), I only need the status > 10 , and ranking from smaller, (i.e. 11 > 12 > 13 > 14, only four status) and determine from their time.
正如埃里克所提到的，我正在从 game_status 和 min time(T) 中寻找结果，我只需要状态 > 10 ，并从较小的排名，（即 11 > 12 > 13 > 14，只有四个状态）并从他们的时间。
I've taken the top 5 rows of player_id = 18 from the table:
我从表中取出了 player_id = 18 的前 5 行：
SELECT *, (UNIX_TIMESTAMP(end_time) - UNIX_TIMESTAMP(start_time)) AS T FROM games where player_id = 18 order by game_status, T;
SELECT *, (UNIX_TIMESTAMP(end_time) - UNIX_TIMESTAMP(start_time)) AS T FROM games where player_id = 18 order by game_status, T;
game_id, player_id, start_time, end_time, score, game_status, is_enabled, T
73, 18, '2009-09-11 18:55:07', '2009-09-11 19:09:07', 30, 11, 1, 840
35, 18, '2009-09-11 15:46:05', '2009-09-11 16:25:10', 80, 11, 1, 2345
53, 18, '2009-09-11 16:57:30', '2009-09-11 16:58:28', 0, 14, 1, 58
59, 18, '2009-09-11 17:27:42', '2009-09-11 17:28:51', 0, 14, 1, 69
57, 18, '2009-09-11 17:24:25', '2009-09-11 17:25:41', 0, 14, 1, 76
Player 18 played many times of the game. He got different results(game_status). Now, we are taking the best result on each of the players.
玩家 18 玩了很多次游戏。他得到了不同的结果（game_status）。现在，我们正在为每个球员取得最好的成绩。
Obviously, the best result for 18 is
显然，18 的最佳结果是
73, 18, '2009-09-11 18:55:07', '2009-09-11 19:09:07', 30, 11, 1, 840
As the status is 11, and time is 840.
因为状态是11，时间是840。
Note that the best time he took was game_id = 53(Line 3 above), We won't take this result as the status was 14. Hence, use min(UnixTimeSTAMP ... ) won't help as it take 58 as result set.
请注意，他花费的最佳时间是 game_id = 53（上面的第 3 行），我们不会采用此结果，因为状态为 14。因此，使用 min(UnixTimeSTAMP ... ) 将无济于事，因为结果是 58放。
回答by David Andres
It looks like you're missing the MIN function and a slight change to your filtering clause.
看起来您缺少 MIN 函数，并且您的过滤子句略有更改。
As in:
如：
SELECT *, MIN(UNIX_TIMESTAMP(end_time) - UNIX_TIMESTAMP(start_time)) AS T
FROM games
GROUP BY player_id
HAVING MIN(UNIX_TIMESTAMP(end_time) - UNIX_TIMESTAMP(start_time)) > 10
ORDER BY game_status, T;
I moved the "> 10" logic because I believe your intent is to filter out those players whose best game status is less than ten. This is a different criteria than filtering out any individual game status entries that are less than ten (which is what you were doing via the WHERE clause).
我移动了“> 10”逻辑，因为我相信您的意图是过滤掉那些最佳游戏状态低于 10 的玩家。这与过滤掉少于 10 个的任何单个游戏状态条目（这是您通过 WHERE 子句所做的）不同的标准。
Try it out. It looks like you're using MySQL, which is not a database system I am all that familiar with.
试试看。看起来您正在使用 MySQL，它不是我非常熟悉的数据库系统。
