mysql+distinct+max_如何通过SQL中的另一列选择具有MAX（列值），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

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

结果将是：

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

不起作用 尽管数据库保留187个，但结果集有130行。结果包括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:

具有各种结果。

#1楼

SELECT c1, c2, c3, c4, c5 FROM table1 WHERE c3 = (select max(c3) from table)

SELECT * FROM table1 WHERE c3 = (select max(c3) from table1)

#2楼

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

#3楼

这是MySQL版本，仅打印一个条目，其中一组中有重复的MAX(datetime)。

样本数据

mysql> SELECT * from topten;

+------+------+---------------------+--------+----------+

| id | home | datetime | player | resource |

+------+------+---------------------+--------+----------+

| 1 | 10 | 2009-04-03 00:00:00 | john | 399 |

| 2 | 11 | 2009-04-03 00:00:00 | juliet | 244 |

| 3 | 10 | 2009-03-03 00:00:00 | john | 300 |

| 4 | 11 | 2009-03-03 00:00:00 | juliet | 200 |

| 5 | 12 | 2009-04-03 00:00:00 | borat | 555 |

| 6 | 12 | 2009-03-03 00:00:00 | borat | 500 |

| 7 | 13 | 2008-12-24 00:00:00 | borat | 600 |

| 8 | 13 | 2009-01-01 00:00:00 | borat | 700 |

| 9 | 10 | 2009-04-03 00:00:00 | borat | 700 |

| 10 | 11 | 2009-04-03 00:00:00 | borat | 700 |

| 12 | 12 | 2009-04-03 00:00:00 | borat | 700 |

+------+------+---------------------+--------+----------+

带有用户变量的MySQL版本

SELECT *

FROM (

SELECT ord.*,

IF (@prev_home = ord.home, 0, 1) AS is_first_appear,

@prev_home := ord.home

FROM (

SELECT t1.id, t1.home, t1.player, t1.resource

FROM topten t1

INNER JOIN (

SELECT home, MAX(datetime) AS mx_dt

FROM topten

GROUP BY home

) x ON t1.home = x.home AND t1.datetime = x.mx_dt

ORDER BY home

) ord, (SELECT @prev_home := 0, @seq := 0) init

) y

WHERE is_first_appear = 1;

+------+------+--------+----------+-----------------+------------------------+

| id | home | player | resource | is_first_appear | @prev_home := ord.home |

+------+------+--------+----------+-----------------+------------------------+

| 9 | 10 | borat | 700 | 1 | 10 |

| 10 | 11 | borat | 700 | 1 | 11 |

| 12 | 12 | borat | 700 | 1 | 12 |

| 8 | 13 | borat | 700 | 1 | 13 |

+------+------+--------+----------+-----------------+------------------------+

4 rows in set (0.00 sec)

接受答案的出局

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

+------+------+---------------------+--------+----------+

| id | home | datetime | player | resource |

+------+------+---------------------+--------+----------+

| 1 | 10 | 2009-04-03 00:00:00 | john | 399 |

| 2 | 11 | 2009-04-03 00:00:00 | juliet | 244 |

| 5 | 12 | 2009-04-03 00:00:00 | borat | 555 |

| 8 | 13 | 2009-01-01 00:00:00 | borat | 700 |

| 9 | 10 | 2009-04-03 00:00:00 | borat | 700 |

| 10 | 11 | 2009-04-03 00:00:00 | borat | 700 |

| 12 | 12 | 2009-04-03 00:00:00 | borat | 700 |

+------+------+---------------------+--------+----------+

7 rows in set (0.00 sec)

#4楼

您也可以尝试这一操作，对于大表查询性能会更好。 当每个房屋的记录不超过两个且它们的日期不同时，它将起作用。 更好的一般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或提供分析功能的数据库的情况下尝试

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

#5楼

最快的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 S代表的列b 。

JOIN上的另一个条件要求仅匹配b的datetime列上的值大于m的行。

使用问题中发布的数据， 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子句中的第二个条件，从m中选择的行在datetime列中具有最大值。