mysql 查寻重复姓名且年龄最大的列表

程序员文章站 2022-05-19 19:08:52

...

mysql 查找重复姓名且年龄最大的列表



mysql> select count(*) as count  ,name,sum(age) as age from t1 group by name order by count desc;

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

| count | name   | age  |

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

|     3 | atest  |   64 |

|     2 | btest  |   37 |

|     2 | ctest  |   43 |

|     2 | dtest  |   43 |

|     1 | mary   |   22 |

|     1 | kou    |   22 |

|     1 | perter |   23 |

|     1 | kate   |   19 |

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

8 rows in set (0.00 sec)

这里找到count 重复的数据
下面接着找 count 最大，切age 最大且相同的数据



mysql> select count,name,age from ( select count(*) as count  ,name,sum(age) as age from t1 group by

 name order by count desc ,age desc ) as tmp group by count order by count desc ,age desc;

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

| count | name   | age  |

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

|     3 | atest  |   64 |

|     2 | ctest  |   43 |

|     1 | perter |   23 |

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

3 rows in set (0.00 sec)

为什么少了一条 dtest ，dtest的数据和ctest在count和age上是一样的？

求指教！谢谢