MySQL 如何通过SQL中的另一列选择具有MAX（列值），DISTINCT的行？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/612231/
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
How can I SELECT rows with MAX(Column value), DISTINCT by another column in SQL?
提问by Kaptah
My table is:
我的表是：
id home datetime player resource
---|-----|------------|--------|---------
1 | 10 | 04/03/2009 | john | 399
2 | 11 | 04/03/2009 | juliet | 244
5 | 12 | 04/03/2009 | borat | 555
3 | 10 | 03/03/2009 | john | 300
4 | 11 | 03/03/2009 | juliet | 200
6 | 12 | 03/03/2009 | borat | 500
7 | 13 | 24/12/2008 | borat | 600
8 | 13 | 01/01/2009 | borat | 700
I need to select each distinct homeholding the maximum value of datetime.
我需要选择每个不同的home持有最大值的datetime.
Result would be:
结果将是：
id home datetime player resource
---|-----|------------|--------|---------
1 | 10 | 04/03/2009 | john | 399
2 | 11 | 04/03/2009 | juliet | 244
5 | 12 | 04/03/2009 | borat | 555
8 | 13 | 01/01/2009 | borat | 700
I have tried:
我试过了：
-- 1 ..by the MySQL manual:
SELECT DISTINCT
home,
id,
datetime AS dt,
player,
resource
FROM topten t1
WHERE datetime = (SELECT
MAX(t2.datetime)
FROM topten t2
GROUP BY home)
GROUP BY datetime
ORDER BY datetime DESC
Doesn't work. Result-set has 130 rows although database holds 187.
Result includes some duplicates of home.
不起作用。结果集有 130 行，尽管数据库有 187 行home。结果包括.
-- 2 ..join
SELECT
s1.id,
s1.home,
s1.datetime,
s1.player,
s1.resource
FROM topten s1
JOIN (SELECT
id,
MAX(datetime) AS dt
FROM topten
GROUP BY id) AS s2
ON s1.id = s2.id
ORDER BY datetime
Nope. Gives all the records.
不。给出所有记录。
-- 3 ..something exotic:
With various results.
有各种各样的结果。
回答by Michael La Voie
You are so close! All you need to do is select BOTH the home and its max date time, then join back to the toptentable on BOTH fields:
你离得那么近！您需要做的就是选择家庭及其最大日期时间，然后加入topten两个字段的表：
SELECT tt.*
FROM topten tt
INNER JOIN
(SELECT home, MAX(datetime) AS MaxDateTime
FROM topten
GROUP BY home) groupedtt
ON tt.home = groupedtt.home
AND tt.datetime = groupedtt.MaxDateTime
回答by axiac
The fastest MySQLsolution, without inner queries and without GROUP BY:
最快的MySQL解决方案，没有内部查询，也没有GROUP BY：
SELECT m.* -- get the row that contains the max value
FROM topten m -- "m" from "max"
LEFT JOIN topten b -- "b" from "bigger"
ON m.home = b.home -- match "max" row with "bigger" row by `home`
AND m.datetime < b.datetime -- want "bigger" than "max"
WHERE b.datetime IS NULL -- keep only if there is no bigger than max
Explanation:
说明：
Join the table with itself using the homecolumn. The use of LEFT JOINensures all the rows from table mappear in the result set. Those that don't have a match in table bwill have NULLs for the columns of b.
使用home列将表与自身连接起来。的使用LEFT JOIN确保表m中的所有行都出现在结果集中。那些在 table 中没有匹配项的s 的列b将具有NULLs b。
The other condition on the JOINasks to match only the rows from bthat have bigger value on the datetimecolumn than the row from m.
上的另一个条件JOIN要求仅匹配列中b值datetime大于 的行中的行m。
Using the data posted in the question, the LEFT JOINwill produce this pairs:
使用问题中发布的数据，LEFT JOIN将产生以下对：
+------------------------------------------+--------------------------------+
| the row from `m` | the matching row from `b` |
|------------------------------------------|--------------------------------|
| id home datetime player resource | id home datetime ... |
|----|-----|------------|--------|---------|------|------|------------|-----|
| 1 | 10 | 04/03/2009 | john | 399 | NULL | NULL | NULL | ... | *
| 2 | 11 | 04/03/2009 | juliet | 244 | NULL | NULL | NULL | ... | *
| 5 | 12 | 04/03/2009 | borat | 555 | NULL | NULL | NULL | ... | *
| 3 | 10 | 03/03/2009 | john | 300 | 1 | 10 | 04/03/2009 | ... |
| 4 | 11 | 03/03/2009 | juliet | 200 | 2 | 11 | 04/03/2009 | ... |
| 6 | 12 | 03/03/2009 | borat | 500 | 5 | 12 | 04/03/2009 | ... |
| 7 | 13 | 24/12/2008 | borat | 600 | 8 | 13 | 01/01/2009 | ... |
| 8 | 13 | 01/01/2009 | borat | 700 | NULL | NULL | NULL | ... | *
+------------------------------------------+--------------------------------+
Finally, the WHEREclause keeps only the pairs that have NULLs in the columns of b(they are marked with *in the table above); this means, due to the second condition from the JOINclause, the row selected from mhas the biggest value in column datetime.
最后，该WHERE子句只保留NULL列中包含s 的对b（它们*在上表中用 标记）；这意味着，由于JOIN子句中的第二个条件，从中选择的行在mcolumn 中具有最大值datetime。
Read the SQL Antipatterns: Avoiding the Pitfalls of Database Programmingbook for other SQL tips.
阅读SQL 反模式：避免数据库编程的陷阱，了解其他 SQL 技巧。
回答by Maksym Gontar
Here goes T-SQLversion:
这是T-SQL版本：
-- Test data
DECLARE @TestTable TABLE (id INT, home INT, date DATETIME,
player VARCHAR(20), resource INT)
INSERT INTO @TestTable
SELECT 1, 10, '2009-03-04', 'john', 399 UNION
SELECT 2, 11, '2009-03-04', 'juliet', 244 UNION
SELECT 5, 12, '2009-03-04', 'borat', 555 UNION
SELECT 3, 10, '2009-03-03', 'john', 300 UNION
SELECT 4, 11, '2009-03-03', 'juliet', 200 UNION
SELECT 6, 12, '2009-03-03', 'borat', 500 UNION
SELECT 7, 13, '2008-12-24', 'borat', 600 UNION
SELECT 8, 13, '2009-01-01', 'borat', 700
-- Answer
SELECT id, home, date, player, resource
FROM (SELECT id, home, date, player, resource,
RANK() OVER (PARTITION BY home ORDER BY date DESC) N
FROM @TestTable
)M WHERE N = 1
-- and if you really want only home with max date
SELECT T.id, T.home, T.date, T.player, T.resource
FROM @TestTable T
INNER JOIN
( SELECT TI.id, TI.home, TI.date,
RANK() OVER (PARTITION BY TI.home ORDER BY TI.date) N
FROM @TestTable TI
WHERE TI.date IN (SELECT MAX(TM.date) FROM @TestTable TM)
)TJ ON TJ.N = 1 AND T.id = TJ.id
EDIT
Unfortunately, there are no RANK() OVER function in MySQL.
But it can be emulated, see Emulating Analytic (AKA Ranking) Functions with MySQL.
So this is MySQLversion:
编辑
不幸的是，MySQL 中没有 RANK() OVER 函数。
但它可以被模拟，参见Emulating Analytic (AKA Ranking) Functions with MySQL。
所以这是MySQL版本：
SELECT id, home, date, player, resource
FROM TestTable AS t1
WHERE
(SELECT COUNT(*)
FROM TestTable AS t2
WHERE t2.home = t1.home AND t2.date > t1.date
) = 0
回答by Quassnoi
This will work even if you have two or more rows for each homewith equal DATETIME's:
即使您有两行或更多行，每行home具有相等的DATETIME's ，这也将起作用：
SELECT id, home, datetime, player, resource
FROM (
SELECT (
SELECT id
FROM topten ti
WHERE ti.home = t1.home
ORDER BY
ti.datetime DESC
LIMIT 1
) lid
FROM (
SELECT DISTINCT home
FROM topten
) t1
) ro, topten t2
WHERE t2.id = ro.lid
回答by Ricardo Felgueiras
I think this will give you the desired result:
我认为这会给你想要的结果：
SELECT home, MAX(datetime)
FROM my_table
GROUP BY home
BUTif you need other columns as well, just make a join with the original table (check Michael La Voieanswer)
但是，如果您还需要其他列，只需与原始表进行连接（检查Michael La Voie答案）
Best regards.
此致。
回答by MJB
Since people seem to keep running into this thread (comment date ranges from 1.5 year) isn't this much simpler:
由于人们似乎不断遇到此线程（评论日期范围从 1.5 年起）并没有那么简单：
SELECT * FROM (SELECT * FROM topten ORDER BY datetime DESC) tmp GROUP BY home
SELECT * FROM (SELECT * FROM topten ORDER BY datetime DESC) tmp GROUP BY home
No aggregation functions needed...
不需要聚合函数...
Cheers.
干杯。
回答by Shiva
You can also try this one and for large tables query performance will be better. It works when there no more than two records for each home and their dates are different. Better general MySQL query is one from Michael La Voie above.
你也可以试试这个，对于大表查询性能会更好。当每个家庭的记录不超过两个并且它们的日期不同时，它会起作用。更好的通用 MySQL 查询是上面 Michael La Voie 的一个查询。
SELECT t1.id, t1.home, t1.date, t1.player, t1.resource
FROM t_scores_1 t1
INNER JOIN t_scores_1 t2
ON t1.home = t2.home
WHERE t1.date > t2.date
Or in case of Postgres or those dbs that provide analytic functions try
或者在 Postgres 或那些提供分析功能的数据库的情况下，请尝试
SELECT t.* FROM
(SELECT t1.id, t1.home, t1.date, t1.player, t1.resource
, row_number() over (partition by t1.home order by t1.date desc) rw
FROM topten t1
INNER JOIN topten t2
ON t1.home = t2.home
WHERE t1.date > t2.date
) t
WHERE t.rw = 1
回答by SysDragon
Try this for SQL Server:
为 SQL Server 试试这个：
WITH cte AS (
SELECT home, MAX(year) AS year FROM Table1 GROUP BY home
)
SELECT * FROM Table1 a INNER JOIN cte ON a.home = cte.home AND a.year = cte.year
回答by FerranB
This works on Oracle:
这适用于 Oracle：
with table_max as(
select id
, home
, datetime
, player
, resource
, max(home) over (partition by home) maxhome
from table
)
select id
, home
, datetime
, player
, resource
from table_max
where home = maxhome
回答by Kaptah
SELECT tt.*
FROM TestTable tt
INNER JOIN
(
SELECT coord, MAX(datetime) AS MaxDateTime
FROM rapsa
GROUP BY
krd
) groupedtt
ON tt.coord = groupedtt.coord
AND tt.datetime = groupedtt.MaxDateTime
