56,679
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE `t_user_flow` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`logTime` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
`userid` int(11) DEFAULT NULL,
`onlineTime` int(11) DEFAULT NULL,
`network` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=130 DEFAULT CHARSET=utf8;
用户身份转换表
CREATE TABLE `t_user_typechange` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`logTime` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
`userid` int(11) DEFAULT NULL,
`userType` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
INSERT INTO `test`.`t_user_flow` (`id`, `logTime`, `userid`, `onlineTime`, `network`) VALUES ('103', '2018-09-21 00:00:10', '1', '10', '20');
INSERT INTO `test`.`t_user_flow` (`id`, `logTime`, `userid`, `onlineTime`, `network`) VALUES ('104', '2018-09-21 00:00:20', '1', '10', '35');
INSERT INTO `test`.`t_user_flow` (`id`, `logTime`, `userid`, `onlineTime`, `network`) VALUES ('105', '2018-09-21 00:00:30', '1', '10', '50');
INSERT INTO `test`.`t_user_flow` (`id`, `logTime`, `userid`, `onlineTime`, `network`) VALUES ('106', '2018-09-21 00:00:40', '1', '10', '25');
INSERT INTO `test`.`t_user_flow` (`id`, `logTime`, `userid`, `onlineTime`, `network`) VALUES ('107', '2018-09-21 00:00:50', '1', '10', '54');
INSERT INTO `test`.`t_user_flow` (`id`, `logTime`, `userid`, `onlineTime`, `network`) VALUES ('108', '2018-09-21 00:01:00', '1', '10', '56');
INSERT INTO `test`.`t_user_flow` (`id`, `logTime`, `userid`, `onlineTime`, `network`) VALUES ('109', '2018-09-21 00:01:10', '1', '10', '55');
INSERT INTO `test`.`t_user_flow` (`id`, `logTime`, `userid`, `onlineTime`, `network`) VALUES ('110', '2018-09-21 00:01:20', '1', '10', '2');
INSERT INTO `test`.`t_user_flow` (`id`, `logTime`, `userid`, `onlineTime`, `network`) VALUES ('111', '2018-09-21 00:01:30', '1', '10', '54');
INSERT INTO `test`.`t_user_flow` (`id`, `logTime`, `userid`, `onlineTime`, `network`) VALUES ('112', '2018-09-21 00:01:40', '1', '10', '56');
INSERT INTO `test`.`t_user_typechange` (`id`, `logTime`, `userid`, `userType`) VALUES ('1', '2018-09-20 23:00:00', '1', '0');
INSERT INTO `test`.`t_user_typechange` (`id`, `logTime`, `userid`, `userType`) VALUES ('2', '2018-09-21 00:00:30', '1', '1');
mysql> select * from t_user_flow;
+-----+---------------------+--------+------------+---------+
| id | logTime | userid | onlineTime | network |
+-----+---------------------+--------+------------+---------+
| 103 | 2018-09-21 00:00:10 | 1 | 10 | 20 |
| 104 | 2018-09-21 00:00:20 | 1 | 10 | 35 |
| 105 | 2018-09-21 00:00:30 | 1 | 10 | 50 |
| 106 | 2018-09-21 00:00:40 | 1 | 10 | 25 |
| 107 | 2018-09-21 00:00:50 | 1 | 10 | 54 |
| 108 | 2018-09-21 00:01:00 | 1 | 10 | 56 |
| 109 | 2018-09-21 00:01:10 | 1 | 10 | 55 |
| 110 | 2018-09-21 00:01:20 | 1 | 10 | 2 |
| 111 | 2018-09-21 00:01:30 | 1 | 10 | 54 |
| 112 | 2018-09-21 00:01:40 | 1 | 10 | 56 |
+-----+---------------------+--------+------------+---------+
10 rows in set (0.01 sec)
mysql> select * from t_user_typechange;
+----+---------------------+--------+----------+
| id | logTime | userid | userType |
+----+---------------------+--------+----------+
| 1 | 2018-09-20 23:00:00 | 1 | 0 |
| 2 | 2018-09-21 00:00:30 | 1 | 1 |
+----+---------------------+--------+----------+
2 rows in set (0.00 sec)
mysql> select *,(select userType from t_user_typechange where userid=f.userid and logTime<=f.logTime order by logTime desc limit 1) as userType
-> from t_user_flow f;
+-----+---------------------+--------+------------+---------+----------+
| id | logTime | userid | onlineTime | network | userType |
+-----+---------------------+--------+------------+---------+----------+
| 103 | 2018-09-21 00:00:10 | 1 | 10 | 20 | 0 |
| 104 | 2018-09-21 00:00:20 | 1 | 10 | 35 | 0 |
| 105 | 2018-09-21 00:00:30 | 1 | 10 | 50 | 1 |
| 106 | 2018-09-21 00:00:40 | 1 | 10 | 25 | 1 |
| 107 | 2018-09-21 00:00:50 | 1 | 10 | 54 | 1 |
| 108 | 2018-09-21 00:01:00 | 1 | 10 | 56 | 1 |
| 109 | 2018-09-21 00:01:10 | 1 | 10 | 55 | 1 |
| 110 | 2018-09-21 00:01:20 | 1 | 10 | 2 | 1 |
| 111 | 2018-09-21 00:01:30 | 1 | 10 | 54 | 1 |
| 112 | 2018-09-21 00:01:40 | 1 | 10 | 56 | 1 |
+-----+---------------------+--------+------------+---------+----------+
10 rows in set (0.00 sec)
mysql>