MySql的Group By

mysql> select * from user_test;
+----+-----------+----------+----------+
| id | user_name | user_sex | user_age |
+----+-----------+----------+----------+
|  1 | 张三      | 男       |       18 |
|  2 | 李四      | 男       |       17 |
|  3 | 王丽      | 女       |       18 |
|  4 | 孔梦      | 女       |       19 |
+----+-----------+----------+----------+
4 rows in set (0.03 sec)

mysql> select * from user_test group by user_sex;
+----+-----------+----------+----------+
| id | user_name | user_sex | user_age |
+----+-----------+----------+----------+
|  3 | 王丽      | 女       |       18 |
|  1 | 张三      | 男       |       18 |
+----+-----------+----------+----------+
2 rows in set (0.00 sec)
mysql> select * from (select * from user_test order by user_age)s group by s.user_sex;
+----+-----------+----------+----------+
| id | user_name | user_sex | user_age |
+----+-----------+----------+----------+
|  3 | 王丽      | 女       |       18 |
|  1 | 张三      | 男       |       18 |
+----+-----------+----------+----------+
2 rows in set (0.00 sec)
mysql> select * from user_test order by user_age;
+----+-----------+----------+----------+
| id | user_name | user_sex | user_age |
+----+-----------+----------+----------+
|  2 | 李四      | 男       |       17 |
|  1 | 张三      | 男       |       18 |
|  3 | 王丽      | 女       |       18 |
|  4 | 孔梦      | 女       |       19 |
+----+-----------+----------+----------+
4 rows in set (0.00 sec)
mysql> select * from (select * from user_test order by user_age limit 4)s group by s.user_sex;
+----+-----------+----------+----------+
| id | user_name | user_sex | user_age |
+----+-----------+----------+----------+
|  3 | 王丽      | 女       |       18 |
|  2 | 李四      | 男       |       17 |
+----+-----------+----------+----------+
2 rows in set (0.00 sec)

想在该表中查询男女同学中各自年龄最小的同学的基本信息,发现常规方法grop by后选取的一定是主键值小的,想完成该要求要在子查询中加limit.
另外,对于聚合列,应该是指被聚合函数用了的列而不是group by用的列,因为group by是完成了分组,聚合函数才是进行聚合的.

总结

分组要用非聚合列,查询要查分组列和聚合列