我有一个球员表现的表格:
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`
没有。给出所有的记录。
我尝试了各种奇特的查询,每一个都有不同的结果,但没有一个能让我更接近解决这个问题。
在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 < >小提琴
在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 < >小提琴