MySQL之——GROUP BY分组取字段最大值__mysql分组取最大值

link之家

链接快照平台

输入网页链接，自动生成快照
标签化管理网页链接

相关文章推荐

微醺的勺子 · Target class ...· 1 年前 ·

文雅的弓箭 · MongoDB 文档的删除操作 - 悦光阴 ...· 1 年前 ·

玉树临风的苦瓜 · java json特殊字符转义-掘金· 1 年前 ·

听话的感冒药 · Excel vba刷新等待 - 问答 - ...· 1 年前 ·

INSERT INTO tb SELECT null , 1001, '192.168.1.1' , '2017-01-21 16:30:47' ;
INSERT INTO tb SELECT null , 1003, '192.168.1.153' , '2017-01-21 19:30:51' ;
INSERT INTO tb SELECT null , 1001, '192.168.1.61' , '2017-01-21 16:50:41' ;
INSERT INTO tb SELECT null , 1002, '192.168.1.31' , '2017-01-21 18:30:21' ;
INSERT INTO tb SELECT null , 1002, '192.168.1.66' , '2017-01-21 19:12:32' ;
INSERT INTO tb SELECT null , 1001, '192.168.1.81' , '2017-01-21 19:53:09' ;
INSERT INTO tb SELECT null , 1001, '192.168.1.231' , '2017-01-21 19:55:34' ;

表数据情况： [plain] view plain

+----+------+---------------+---------------------+
| id | uid | ip | login_time |
+----+------+---------------+---------------------+
| 1 | 1001 | 192.168.1.1 | 2017-01-21 16:30:47 |
| 2 | 1003 | 192.168.1.153 | 2017-01-21 19:30:51 |
| 3 | 1001 | 192.168.1.61 | 2017-01-21 16:50:41 |
| 4 | 1002 | 192.168.1.31 | 2017-01-21 18:30:21 |
| 5 | 1002 | 192.168.1.66 | 2017-01-21 19:12:32 |
| 6 | 1001 | 192.168.1.81 | 2017-01-21 19:53:09 |
| 7 | 1001 | 192.168.1.231 | 2017-01-21 19:55:34 |
+----+------+---------------+---------------------+

如果只需要针对用户查出其最后登录的时间，可以简单写出： [html] view plain 这样的语句是非SQL标准的，虽然能够在MySQL数据库中执行成功，但返回的却是未知的
（如果sql_mode开启了only_full_group_by，则不会执行成功。）
可能ip字段会取uid分组前的第一个row的值，显然不是所需信息
写一个子查询： [sql] view plain

+----+--------------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | PRIMARY | a | ALL | NULL | NULL | NULL | NULL | 7 | Using where |
| 2 | DEPENDENT SUBQUERY | tb | ALL | uid | NULL | NULL | NULL | 7 | Using where |
+----+--------------------+-------+------+---------------+------+---------+------+------+-------------+

5.6.30： [plain] view plain

+----+--------------------+-------+------+---------------+------+---------+------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-------+------+---------------+------+---------+------------+------+-------------+
| 1 | PRIMARY | a | ALL | NULL | NULL | NULL | NULL | 7 | Using where |
| 2 | DEPENDENT SUBQUERY | tb | ref | uid | uid | 4 | test.a.uid | 1 | NULL |
+----+--------------------+-------+------+---------------+------+---------+------------+------+-------------+
直接改成join性能会更加好： [sql] view plain
1. +------+---------------+---------------------+
2. | uid | ip | login_time |
3. +------+---------------+---------------------+
4. | 1003 | 192.168.1.153 | 2017-01-21 19:30:51 |
5. | 1002 | 192.168.1.66 | 2017-01-21 19:12:32 |
6. | 1001 | 192.168.1.231 | 2017-01-21 19:55:34 |
7. +------+---------------+---------------------+ 注：如果要分组取最小值直接改对应函数和符号就行了。
  1. INSERT INTO tb SELECT null , 1001, '192.168.1.1' , '2017-01-21 16:30:47' ;
  2. INSERT INTO tb SELECT null , 1003, '192.168.1.153' , '2017-01-21 19:30:51' ;
  3. INSERT INTO tb SELECT null , 1001, '192.168.1.61' , '2017-01-21 16:50:41' ;
  4. INSERT INTO tb SELECT null , 1002, '192.168.1.31' , '2017-01-21 18:30:21' ;
  5. INSERT INTO tb SELECT null , 1002, '192.168.1.66' , '2017-01-21 19:12:32' ;
  6. INSERT INTO tb SELECT null , 1001, '192.168.1.81' , '2017-01-21 19:53:09' ;
  7. INSERT INTO tb SELECT null , 1001, '192.168.1.231' , '2017-01-21 19:55:34' ;
  表数据情况： [plain] view plain
  1. +----+------+---------------+---------------------+
  2. | id | uid | ip | login_time |
  3. +----+------+---------------+---------------------+
  4. | 1 | 1001 | 192.168.1.1 | 2017-01-21 16:30:47 |
  5. | 2 | 1003 | 192.168.1.153 | 2017-01-21 19:30:51 |
  6. | 3 | 1001 | 192.168.1.61 | 2017-01-21 16:50:41 |
  7. | 4 | 1002 | 192.168.1.31 | 2017-01-21 18:30:21 |
  8. | 5 | 1002 | 192.168.1.66 | 2017-01-21 19:12:32 |
  9. | 6 | 1001 | 192.168.1.81 | 2017-01-21 19:53:09 |
  10. | 7 | 1001 | 192.168.1.231 | 2017-01-21 19:55:34 |
  11. +----+------+---------------+---------------------+
  如果只需要针对用户查出其最后登录的时间，可以简单写出： [html] view plain
这样的语句是非SQL标准的，虽然能够在MySQL数据库中执行成功，但返回的却是未知的
（如果sql_mode开启了only_full_group_by，则不会执行成功。）
可能ip字段会取uid分组前的第一个row的值，显然不是所需信息
写一个子查询： [sql] view plain
1. +----+--------------------+-------+------+---------------+------+---------+------+------+-------------+
2. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
3. +----+--------------------+-------+------+---------------+------+---------+------+------+-------------+
4. | 1 | PRIMARY | a | ALL | NULL | NULL | NULL | NULL | 7 | Using where |
5. | 2 | DEPENDENT SUBQUERY | tb | ALL | uid | NULL | NULL | NULL | 7 | Using where |
6. +----+--------------------+-------+------+---------------+------+---------+------+------+-------------+
5.6.30： [plain] view plain
1. +----+--------------------+-------+------+---------------+------+---------+------------+------+-------------+
2. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
3. +----+--------------------+-------+------+---------------+------+---------+------------+------+-------------+
4. | 1 | PRIMARY | a | ALL | NULL | NULL | NULL | NULL | 7 | Using where |
5. | 2 | DEPENDENT SUBQUERY | tb | ref | uid | uid | 4 | test.a.uid | 1 | NULL |
6. +----+--------------------+-------+------+---------------+------+---------+------------+------+-------------+
  直接改成join性能会更加好： [sql] view plain
  1. +------+---------------+---------------------+
  2. | uid | ip | login_time |
  3. +------+---------------+---------------------+
  4. | 1003 | 192.168.1.153 | 2017-01-21 19:30:51 |
  5. | 1002 | 192.168.1.66 | 2017-01-21 19:12:32 |
  6. | 1001 | 192.168.1.231 | 2017-01-21 19:55:34 |
  7. +------+---------------+---------------------+ 注：如果要分组取最小值直接改对应函数和符号就行了。
    java 对象实例 java对象实例数据
    
    java对象在内存中占用的空间分为3类， 1. 对象头（Header）； 2. 实例数据（Instance Data）; 3. 对齐填充（Padding）。常说的基础数据类型大小主要是指第二类实例数据。具体示例如下：import org.openjdk.jol.info.ClassLayout; public class JOLDemo { public static void mai