mysql - 在SQL里, 我该如何SELECT行和MAX( Column值) , DISTINCT 另一列?

我的表格是:


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

我需要选择每个不同的home 保持 datetime的最大值。

结果将是:


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

我尝试过:


-- 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

无效。Result-set有 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 

不给所有记录。


-- 3.. something exotic: 

有各种结果。

时间:

你已经非常接近了你需要做的就是选择 home,它是最大的日期时间,然后返回到两个字段的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

这里是 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

编辑
不幸的是,在MySQL的函数中没有 RANK() 。
但是它可以模拟,看到模拟分析与mysql ( AKA等级) 功能。
这是 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

即使对于每个 home 具有相等 DATETIME的两个或者更多行,这也可以工作:


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

我认为这将给你所需的结果:


SELECT home, MAX(datetime)
FROM my_table
GROUP BY home

如果你还需要其他列,就可以使用原始表进行联接。

最佳注意事项。

这适用于 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

你也可以尝试这个,对于大型表查询性能会更好。 当每个家的记录不超过两个,并且它们的日期不同时它就工作。 更好的通用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

或者,如果是Postgres或者提供解析功能的那些 dbs


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

因为人们似乎一直在使用这个线程( 评论日期范围从 1.5年),所以它并不是那么简单:

SELECT * FROM (SELECT * FROM topten ORDER BY datetime DESC) tmp GROUP BY home

不需要聚合函数。。

干杯。


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

尝试 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

最快的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

解释:

使用 home 列将该表与其自身联接。 使用 LEFT JOIN 确保表 m 中的所有行出现在结果集中。 那些在表 b 中没有匹配的,将有 NULL的列的。

JOIN 要求其他条件匹配的行从 b 有更大的价值从 mdatetime 列的行。

使用在问题中发布的数据,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 |.. . | *
+------------------------------------------+--------------------------------+

最后,WHERE 子句只保留 b ( 上面的表格中标有 * ) 列中具有 NULL的对;这意味着,由于 JOIN 子句的第二个条件,从子句中选择的行在列 datetime 中的值最大值。

阅读 SQL反模式: 避免数据库编程的陷阱( ) 用于其他SQL技巧。

...