mysql - 查找列值的差异

使用GROUPBY，HAVING子句将让我知道给定ID是否有多个记录。是否仍要知道这两个记录在其余列中的差异？

mysql>select pid, name, city from table1;
+------+-------------+--------+
| pid  | name        | city   |
+------+-------------+--------+
|    1 | aa          | delhi  |
|    2 | bb          | delhi  |
|    3 | cc          | mumbai |
|    4 | salman      | pune   |
|    4 | salman khan | pune   |
+------+-------------+--------+
5 rows in set (0.00 sec)

mysql>select pid, count(*) as cnt from table1 group by pid having cnt > 1;
+------+-----+
| pid  | cnt |
+------+-----+
|    4 |   2 |
+------+-----+
1 row in set (0.00 sec)

预期结果：

+------+-------------+
| pid  | name        |
+------+-------------+
|    4 | salman      |
|    4 | salman khan |
+------+-------------+
2 rows in set (0.00 sec)

我可以通过使用以下查询来实现这一点…

mysql>select pid, name from table1 where pid=4;

但我怎么知道这两排的名字不同，城市是一样的呢？
表中有一个timestamp列，我需要根据该时间对这些行进行排序。给定PID的最早记录将是第一个。

最佳答案

要获得您发布的预期结果，请尝试：

select pid, name
from table1
where pid in
  (select pid
  from table1
  group by pid
  having count(*) > 1)
group by pid, name

如果你对以下情况特别感兴趣
城市和PID是一样的
名字不一样
按每组时间排序
正如你在问题中所解释的，试着：

select pid, name, city, timestamp
from table1
where pid in
  (select pid
  from table1
  group by pid, city
  having count(*) > 1)
group by pid, name, city
order by pid, city, timestamp