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)
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)
第二式有 group by count，那么 count 相同的肯定在一组了
既然
| 2 | ctest | 43 |
| 2 | dtest | 43 |
在一组，那自然只能出现一个了
所以分组条件应加上 age，即 group by count,age
mysql> select count(*) as count ,name,sum(age) as age from t1 group by name order by count desc, ae desc ;+-------+--------+------+| count | name | age |+-------+--------+------+| 3 | zx | 64 || 2 | xz | 43 || 2 | john | 43 || 2 | tom | 37 || 1 | perter | 23 || 1 | mary | 22 || 1 | kou | 22 || 1 | kate | 19 |+-------+--------+------+8 rows in set (0.00 sec)mysql> select count,name,age from ( select count(*) as count ,name,sum(age) as age from t1 group b name order by count desc ,age desc ) as tmp group by count,age order by count desc ,age desc;+-------+--------+------+| count | name | age |+-------+--------+------+| 3 | zx | 64 || 2 | xz | 43 || 2 | tom | 37 || 1 | perter | 23 || 1 | mary | 22 || 1 | kate | 19 |+-------+--------+------+6 rows in set (0.00 sec)
+-------+--------+------+| count | name | age |+-------+--------+------+| 3 | atest | 64 || 2 | ctest | 43 || 2 | dtest | 43 || 1 | perter | 23 |+-------+--------+------+
select count,name,age from ( select count(*) as count ,name,sum(age) as age from t1 group b
name order by count desc ,age desc ) as tmp group by count, name order by count desc ,age desc;
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,name order by count desc ,age desc;+-------+--------+------+| count | name | age |+-------+--------+------+| 3 | zx | 64 || 2 | xz | 43 || 2 | john | 43 || 2 | tom | 37 || 1 | perter | 23 || 1 | kou | 22 || 1 | mary | 22 || 1 | kate | 19 |+-------+--------+------+8 rows in set (0.00 sec)
+-------+--------+------+| count | name | age |+-------+--------+------+| 3 | atest | 64 || 2 | ctest | 43 || 2 | dtest | 43 || 1 | perter | 23 |+-------+--------+------+
求指教
感觉效率应该不是很高，虽然可以做出来
(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 tempd.count desc ,tempd.age desc;
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;
非常感谢版主
每个人都需要一台速度更快、更稳定的 PC。随着时间的推移，垃圾文件、旧注册表数据和不必要的后台进程会占用资源并降低性能。幸运的是，许多工具可以让 Windows 保持平稳运行。
Copyright 2014-2025 https://www.php.cn/ All Rights Reserved | php.cn | 湘ICP备2023035733号
356
收藏
