mysql按名称查询分组

Question

我需要一些有关 MySQL 查询的帮助。

我有这张表:

Name  | Date       | City------+------------+------Peter | 2013-04-01 | BerlinPaul  | 2013-03-01 | LondonPeter | 2013-03-01 | New York Peter | 2013-01-28 | Tokio

I need, for each name, the first date and corresponding city in the table.

I tried:

SELECT Name, MIN(Date) as MD, MIN(City) as CN FROM table GROUP BY Name

但日期与城市名称不对应，结果是:

Peter, 2013-01-028, Berlin

有人可以给我提示吗？

谢谢

Answer 1

您需要使用子查询通过 name 获取 min(date)，然后将该结果加入您的表中 name 和日期:

SELECT t1.Name, 
  t1.Date as MD, 
  t1.City as CN 
FROM yourtable t1
inner join
(
  select min(date) MinDate, name
  from yourtable
  group by name
) t2
  on t1.name = t2.name
  and t1.date = t2.mindate

参见 SQL Fiddle with Demo

结果是:

|  NAME |                             MD |     CN |
---------------------------------------------------
|  Paul |   March, 01 2013 00:00:00+0000 | London |
| Peter | January, 28 2013 00:00:00+0000 |  Tokio |