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

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上是一样的？

求指教！谢谢

分享到：

------解决方案--------------------

select tmp1.count,tmp1.age from (select count,max(age) as 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) as tempd,( select count(*) as count ,name,sum(age) as age from t1 group by

name order by count desc ,age desc ) as tmp1 where tmp1.count=tempd.count and tmp1.age=tempd.age count order by tmp1.count desc ,tmp1.age desc;

相关文章

相关视频

网友评论

文明上网理性发言，请遵守 新闻评论服务协议我要评论

立即提交

专题推荐独孤九贱-php全栈开发教程

全栈 100W+

主讲：Peter-Zhu 轻松幽默、简短易学，非常适合PHP学习入门

玉女心经-web前端开发教程

入门 50W+

主讲：灭绝师太 由浅入深、明快简洁，非常适合前端学习入门

天龙八部-实战开发教程

实战 80W+

主讲：西门大官人 思路清晰、严谨规范，适合有一定web编程基础学习