首頁 > 資料庫 > mysql教程 > MySQL之—GROUP BY分組取字段最大值的範例程式碼詳情

MySQL之—GROUP BY分組取字段最大值的範例程式碼詳情

黄舟
發布: 2018-05-15 14:14:58
原創
3396 人瀏覽過

MySQL之—GROUP BY分组取字段最大值的示例代码详情

假设有一个业务场景,需要查询用户登录记录信息,其中表结构如下:

CREATE TABLE `tb` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `uid` int(11) NOT NULL,
  `ip` varchar(16) NOT NULL,
  `login_time` datetime,
  PRIMARY KEY (`id`),
  KEY (`uid`)
);
登入後複製

再来点测试数据:

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';
登入後複製

表数据情况:<br/>

+----+------+---------------+---------------------+
| 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 |
+----+------+---------------+---------------------+
登入後複製

如果只需要针对用户查出其最后登录的时间,可以简单写出:<br/>

SELECT uid, max(login_time)
FROM tb
GROUP BY uid;
登入後複製
+------+---------------------+
| uid  | max(login_time)       |
+------+---------------------+
| 1001 | 2017-01-21 19:55:34 |
| 1002 | 2017-01-21 19:12:32 |
| 1003 | 2017-01-21 19:30:51 |
+------+---------------------+
登入後複製

若还需要查询用户最后登录时的其他信息,就不能用这种sql写了:<br/>

-- 错误写法
SELECT uid, ip, max(login_time)
FROM tb
GROUP BY uid;
-- 错误写法
登入後複製

这样的语句是非SQL标准的,虽然能够在MySQL数据库中执行成功,但返回的却是未知的<br/>(如果sql_mode开启了only_full_group_by,则不会执行成功。)

<br/>
登入後複製

可能ip字段会取uid分组前的第一个row的值,显然不是所需信息<br/>写法1<br/>写一个子查询:<br/>

SELECT a.uid, a.ip, a.login_time
FROM tb a
WHERE a.login_time in (
SELECT max(login_time)
FROM tb
GROUP BY uid);
登入後複製

写法2<br/>再或者换一个写法:<br/>

SELECT a.uid, a.ip, a.login_time
FROM tb a
WHERE a.login_time = (
SELECT max(login_time)
FROM tb
WHERE a.uid = uid);
登入後複製

顺便测了一下<br/>在5.6以前的版本中,写法②这条sql在大数据量的情况下,执行计划不理想,目测性能不佳。<br/>在5.6及以后的版本中,写法②这条sql会快很多,执行计划也有了改变<br/>5.5.50:<br/>

+----+--------------------+-------+------+---------------+------+---------+------+------+-------------+
| 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:<br/>

+----+--------------------+-------+------+---------------+------+---------+------------+------+-------------+
| 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           |
+----+--------------------+-------+------+---------------+------+---------+------------+------+-------------+
登入後複製

写法3<br/>直接改成join性能会更加好:<br/>

SELECT a.uid, a.ip, a.login_time
FROM (SELECT uid, max(login_time) login_time
FROM tb
GROUP BY uid
) b JOIN tb a ON a.uid = b.uid AND a.login_time = b.login_time;
登入後複製

当然,结果都相同:<br/>

+------+---------------+---------------------+
| uid  | ip            | login_time          |
+------+---------------+---------------------+
| 1003 | 192.168.1.153 | 2017-01-21 19:30:51 |
| 1002 | 192.168.1.66  | 2017-01-21 19:12:32 |
| 1001 | 192.168.1.231 | 2017-01-21 19:55:34 |
+------+---------------+---------------------+
登入後複製

注:如果要分组取最小值直接改对应函数和符号就行了。


以上是MySQL之—GROUP BY分組取字段最大值的範例程式碼詳情的詳細內容。更多資訊請關注PHP中文網其他相關文章!

來源:php.cn
本網站聲明
本文內容由網友自願投稿,版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容,請聯絡admin@php.cn
熱門教學
更多>
最新下載
更多>
網站特效
網站源碼
網站素材
前端模板