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 が最も大きいデータ、最大で同じ年齢のデータを見つけます

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 が 1 つ少ないのはなぜですか、 dtest と ctest のデータは count と ctest が同じです。年？

アドバイスをお願いします！ありがとうございます

ディスカッションに返信します
2 番目の式はカウントごとにグループ化されているため、同じカウントを持つものはグループ内に存在する必要があります。 dtest | 43 |

グループ内では、当然 1 つしか出現できません

したがって、グループ化条件に age を追加する必要があります。つまり、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)

group by count,name この方法では、count=2 で最大の年齢のデータと count=1 で最大の年齢のデータを見つけることはできません。最大の年齢〜

+-------+--------+------+| 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;

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 および tmp1.age=tempd.age count order by tmp1.count desc ,tmp1.age desc;

モデレーターさん、ありがとうございます