mysql - 当表寄存器上的用户名更改时，只获取mysql中的最后一个用户名

Question

这是我tbl_register在 MySQL 数据库上的表

+---------------------+------------------+---------------------+-----+
| sDateTime           | sRegisterAccount | sNewRegisterAccount | sID |
+---------------------+------------------+---------------------+-----+
| 2019-11-27 18:52:00 | 116019           | NULL                |   1 |
| 2017-08-05 14:01:00 | 030270           | 116019              |   2 |
| 2020-04-01 14:36:00 | 542116           | NULL                |   3 |
| 2018-10-30 18:36:00 | 000647           | 542116              |   4 |
| 2019-03-07 14:19:00 | 115286           | NULL                |   5 |
| 2020-02-17 23:16:00 | 170134           | NULL                |   6 |
+---------------------+------------------+---------------------+-----+

在此表tbl_register上还记录了该列中的任何用户名更改sNewRegisterAccount

例如

+---------------------+------------------+---------------------+-----+
| sDateTime           | sRegisterAccount | sNewRegisterAccount | sID |
+---------------------+------------------+---------------------+-----+
| 2020-04-01 14:36:00 | 542116           | NULL                |   3 |
| 2018-10-30 18:36:00 | 000647           | 542116              |   4 |
+---------------------+------------------+---------------------+-----+

在列sRegisterAccount中的旧帐户值000647

在列sNewRegisterAccount中的新帐户值542116

我只需要得到最后一个值sNewRegisterAccount作为回报

+---------------------+------------------+---------------------+-----+
| sDateTime           | sRegisterAccount | sNewRegisterAccount | sID |
+---------------------+------------------+---------------------+-----+
| 2019-11-27 18:52:00 | 116019           | NULL                |   1 |
| 2020-04-01 14:36:00 | 542116           | NULL                |   3 |
| 2019-03-07 14:19:00 | 115286           | NULL                |   5 |
| 2020-02-17 23:16:00 | 170134           | NULL                |   6 |
+---------------------+------------------+---------------------+-----+

我试过这个查询没有成功

mysql> SELECT
    A.*
FROM
    `tbl_register` A
INNER JOIN (
    SELECT
        sRegisterAccount,
        MAX(sDateTime) LastDatetimeForNode
    FROM
        `tbl_register`
    GROUP BY
        sRegisterAccount
) B ON A.sRegisterAccount = B.sRegisterAccount
AND A.sDateTime = B.LastDatetimeForNode;

+---------------------+------------------+---------------------+-----+
| sDateTime           | sRegisterAccount | sNewRegisterAccount | sID |
+---------------------+------------------+---------------------+-----+
| 2019-11-27 18:52:00 | 116019           | NULL                |   1 |
| 2017-08-05 14:01:00 | 030270           | 116019              |   2 |
| 2020-04-01 14:36:00 | 542116           | NULL                |   3 |
| 2018-10-30 18:36:00 | 000647           | 542116              |   4 |
| 2019-03-07 14:19:00 | 115286           | NULL                |   5 |
| 2020-02-17 23:16:00 | 170134           | NULL                |   6 |
+---------------------+------------------+---------------------+-----+
6 rows in set

我究竟做错了什么？

拜托，你能帮帮我吗？

结构表tbl_register如下

DROP TABLE IF EXISTS `tbl_register`;
CREATE TABLE `tbl_register` (
  `sDateTime` datetime DEFAULT NULL,
  `sRegisterAccount` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `sNewRegisterAccount` varchar(255) DEFAULT NULL,
  `sID` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`sID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of tbl_register
-- ----------------------------
INSERT INTO `tbl_register` VALUES ('2019-11-27 18:52:00', '116019', null, '1');
INSERT INTO `tbl_register` VALUES ('2017-08-05 14:01:00', '030270', '116019', '2');
INSERT INTO `tbl_register` VALUES ('2020-04-01 14:36:00', '542116', null, '3');
INSERT INTO `tbl_register` VALUES ('2018-10-30 18:36:00', '000647', '542116', '4');
INSERT INTO `tbl_register` VALUES ('2019-03-07 14:19:00', '115286', null, '5');
INSERT INTO `tbl_register` VALUES ('2020-02-17 23:16:00', '170134', null, '6');

更新

预期成绩

+---------------------+------------------+---------------------+-----+
| sDateTime           | sRegisterAccount | sNewRegisterAccount | sID |
+---------------------+------------------+---------------------+-----+
| 2019-11-27 18:52:00 | 116019           | NULL                |   1 |
| 2020-04-01 14:36:00 | 542116           | NULL                |   3 |
| 2019-03-07 14:19:00 | 115286           | NULL                |   5 |
| 2020-02-17 23:16:00 | 170134           | NULL                |   6 |
+---------------------+------------------+---------------------+-----+

标签： mysql