1 SET NAMES utf8mb4;
2 SET FOREIGN_KEY_CHECKS = 0;
3
4 -- ----------------------------
5 -- Table structure for t_login_log
6 7 DROP TABLE IF EXISTS `t_login_log 8 CREATE TABLE ` (
9 idint(11) NOT NULL AUTO_INCREMENT,
10 user_idNULL COMMENT '用户ID',1); padding: 0 5px">11 device_namevarchar(32COLLATE utf8mb4_bin '登录设备'12 login_timedatetime DEFAULT CURRENT_TIMESTAMP ON UPDATE '登录时间'13 PRIMARY KEY ()
14 ) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_bin;
15
16 17 -- Records of t_login_log
18 19 BEGIN20 INSERT INTO ` VALUES (11121'iPhone 6s''2019-07-01 19:20:25');
21 22120'vivo x20''2019-06-28 16:21:11'22 31607'huawei P30''2019-07-04 19:21:59'23 4'2019-06-30 19:22:34'24 5'2019-07-04 19:23:07'25 6'iPad mini''2019-07-03 19:23:25'26 7'iPhone 8 Plus''2019-06-30 19:24:06'27 81970'MI8''2019-07-03 19:25:00'28 COMMIT29
30 ;
自连接，取最新的记录
1 SELECT * FROM t_login_log ORDER BY user_id;
2
3 SELECT
4 t1.*
5 t_login_log t1
6 LEFT JOIN t2 ON t1.user_id = t2.user_id AND t1.login_time < t2.login_time
7 WHERE t2.id IS NULL;
