我如何选择行与最大(列值)，分区由另一个列在MYSQL?

我有一个球员表现的表格:

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`

没有。给出所有的记录。

我尝试了各种奇特的查询，每一个都有不同的结果，但没有一个能让我更接近解决这个问题。

当前回答

你太接近了!你所需要做的就是同时选择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

2009-03-04 20:22:47

其他回答

这是你需要的查询:

 SELECT b.id, a.home,b.[datetime],b.player,a.resource FROM
 (SELECT home,MAX(resource) AS resource FROM tbl_1 GROUP BY home) AS a

 LEFT JOIN

 (SELECT id,home,[datetime],player,resource FROM tbl_1) AS b
 ON  a.resource = b.resource WHERE a.home =b.home;

2016-07-14 06:45:29

你太接近了!你所需要做的就是同时选择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

2009-03-04 20:22:47

这将工作，即使你有两行或更多的每个家庭具有相等的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

2009-03-04 20:24:20

下面是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

2009-03-04 20:59:52

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

2009-03-04 20:58:54

我如何选择行与最大(列值)，分区由另一个列在MYSQL?

推荐文章

最新文章

标签