mysql sql 笔记-获取分组中某个字段的最大的行

获取分组中某个字段的最大的行：

+----+---------+------+--------+
| id | name    | age  | utype  |
+----+---------+------+--------+
|  1 | nick    |   18 | normal |
|  2 | rose    |   20 | normal |
|  3 | richard |   22 | admin  |
|  4 | sarah   |   18 | admin  |
|  5 | adney   |   25 | admin  |
|  6 | jack    |   25 | admin  |
+----+---------+------+--------+

按 utype 分组，获取每个用户类型中年龄 age 的最大的行；
1 .

select * from user where age in (select MAX(age) from user group by utype);

2 . 注：1

select * from user as b where not exists(select 1 from user where utype= b.utype and b.age<age); 

结果：

+----+-------+------+--------+
| id | name  | age  | utype  |
+----+-------+------+--------+
|  2 | rose  |   20 | normal |
|  5 | adney |   25 | admin  |
|  6 | jack  |   25 | admin  |
+----+-------+------+--------+

插入到另一个表中：

insert into usercopy select * from user where age in (select MAX(age) from user group by utype);

---

insert into usercopy select * from user as b where not exists(select 1 from user where utype= b.utype and b.age<age); 

插入到一个新表中：

create table usercopy select * from user where age in (select MAX(age) from user group by utype);

---

create table usercopy select * from user as b where not exists(select 1 from user where utype= b.utype and b.age<age); 

如果在上面筛选记录的基础上只取 id 最大的一行：

select * from user where id in (select MAX(id) from user where age in (select MAX(age) from user group by utype) group by utype);

---

1. http://www.myexception.cn/oracle-develop/58562.html ↩