MySQL按日期时间选择一列中的所有唯一行和另一列中的所有最大行 - mysql select all unique rows in one column and all max rows in another column by datetime

mysql select all unique rows in one column and all max rows in another column by datetime

我需要的是获取每个table_id的最新（按date_time）unique player_id

桌子：

buyin_id   player_id   table_id       date_time
---------|-----------|----------|--------------------|
    1    |    10     |    21    | 2015-01-26 00:00:01
    2    |    11     |    21    | 2015-01-26 00:00:02
    3    |    12     |    21    | 2015-01-26 00:00:03
    4    |    10     |    21    | 2015-01-26 00:00:04
    5    |    11     |    21    | 2015-01-26 00:00:05
    6    |    12     |    22    | 2015-01-26 00:00:06
    7    |    13     |    22    | 2015-01-26 00:00:07
    8    |    13     |    22    | 2015-01-26 00:00:08

期望的结果：

buyin_id   player_id   table_id       date_time
---------|-----------|----------|--------------------|
    3    |    12     |    21    | 2015-01-26 00:00:03
    4    |    10     |    21    | 2015-01-26 00:00:04
    5    |    11     |    21    | 2015-01-26 00:00:05
    6    |    12     |    22    | 2015-01-26 00:00:06
    8    |    13     |    22    | 2015-01-26 00:00:08

我尝试了这样的事情，它只返回 1 行，而不是每table_id 1 行

SELECT pb.buyin_id, pb.player_id, pb.buyin, pb.cashout, pb.cashout_error, pb.date_time
FROM poker_buyin AS pb
INNER JOIN (SELECT player_id, MAX(date_time) AS MaxDateTime
FROM poker_buyin GROUP BY player_id) groupedpb 
ON pb.player_id = groupedpb.player_id 
AND pb.date_time = groupedpb.MaxDateTime
WHERE pb.player_id = '$player_id'";

您提到的查询查找每个玩家 ID 的最新记录。然后你过滤它，只找到一个玩家，所以你得到一行。

如果要查找每个玩家和牌桌的最新记录，则内部查询需要如下所示：

            SELECT  MAX(buyin_id) buyin_id
              FROM  poker_buyin
             GROUP  BY player_id, table_id

这将从您的表中获取代表最新玩家/桌组合的行 ID。

然后，您可以使用它从表中提取记录，如下所示（http://sqlfiddle.com/#!2/be68b7/2/0）

SELECT whatever_columns
  FROM poker_buyin
 WHERE buyin_id IN
       (
            SELECT  MAX(buyin_id) buyin_id
              FROM  poker_buyin
             GROUP  BY player_id, table_id
       )
 WHERE player_id = '$player_id'
 ORDER BY player_id, table_id

此查询中有一个小技巧：buyin_id值不断上升，因此这是为每个组合选择最新时间记录的好方法。

如果您不需要在结果列中buyin_id很简单：

SELECT DISTINCT player_id, table_id, max(date_time) as dt 
FROM `poker_buyin ` 
GROUP BY player_id, table_id