我有一个球员表现的表格:
CREATE TABLE TopTen (
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
home INT UNSIGNED NOT NULL,
`datetime`DATETIME NOT NULL,
player VARCHAR(6) NOT NULL,
resource INT NOT NULL
);
哪个查询将为每个不同的家庭返回包含其datetime最大值的行?换句话说，我如何通过最大datetime(按home分组)进行过滤，并在结果中仍然包括其他非分组、非聚合列(例如player) ?
对于这个示例数据:
INSERT INTO TopTen
(id, home, `datetime`, player, resource)
VALUES
(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)
;
结果应该是:
| 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
结果集有130行，但数据库有187行，这表明结果包括home的一些副本。
然后我尝试连接到一个子查询，为每个行id获得最大日期时间:
-- 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`
没有。给出所有的记录。
我尝试了各种奇特的查询，每一个都有不同的结果，但没有一个能让我更接近解决这个问题。
下面是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() OVER函数。
但它可以被模拟，见模拟分析(AKA排名)函数与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
在MySQL 8.0中，这可以通过使用row_number()窗口函数和公共表表达式有效地实现。
(这里的row_number()基本上是为每个玩家的每一行按资源降序从1开始生成唯一的序列。因此，对于序号为1的每个玩家行将具有最高的资源价值。现在我们要做的就是为每个玩家选择序号为1的行。这可以通过围绕这个查询编写一个外部查询来实现。但我们使用了公共表表达式，因为它更易于阅读。)
模式:
create TABLE TestTable(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
查询:
with cte as
(
select id, home, date , player, resource,
Row_Number()Over(Partition by home order by date desc) rownumber from TestTable
)
select id, home, date , player, resource from cte where rownumber=1
输出:
| id |
home |
date |
player |
resource |
| 1 |
10 |
2009-03-04 00:00:00 |
john |
399 |
| 2 |
11 |
2009-03-04 00:00:00 |
juliet |
244 |
| 5 |
12 |
2009-03-04 00:00:00 |
borat |
555 |
| 8 |
13 |
2009-01-01 00:00:00 |
borat |
700 |
db < >小提琴
