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上是一样的?求指教!谢谢
既然
| 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, a
e 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 |
+-------+--------+------+
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)group by count,name 这样找不到 count=2且age最大的数据和count=1且age最大的数据了~
+-------+--------+------+
| count | name | age |
+-------+--------+------+
| 3 | atest | 64 |
| 2 | ctest | 43 |
| 2 | dtest | 43 |
| 1 | perter | 23 |
+-------+--------+------+
这种预期的结果,能在一条sql里体现出来么?
(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;
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;