状态\u tb
第一次变更

+----+----------+-------------+----------+--------------------+
 | id | status   | description | state_id |  last_modified_on  |
 +----+----------+-------------+----------+--------------------+
 |  1 |     new  | north       | 1        | 2018-10-10 12:30:00|
 |  2 |   assign | south       | 2        | 2018-10-10 13:30:00|
 |  3 |Postponed | east        | 2        | 2018-10-10 14:30:00|
 |  4 |    Fixed | west        | 3        | 2018-10-10 15:30:00|
 | 35 |     Test | South-test  | 4        | 2018-10-10 20:30:00|<-1st try
 +----+----------+-------------+----------+--------------------+

第二次变更

+----+----------+-------------+----------+--------------------+
 | id | status   | description | state_id |  last_modified_on  |
 +----+----------+-------------+----------+--------------------+
 |  1 |     new  | north       | 1        | 2018-10-10 12:30:00|
 |  2 |   assign | south       | 2        | 2018-10-10 13:30:00|
 |  3 |Postponed | east        | 2        | 2018-10-10 24:30:00|
 |  4 |    Fixed | west        | 3        | 2018-10-10 15:30:00|
 | 35 |  Try_OLD | Tried_OLD   | 4        | 2018-10-10 22:22:22|<-2nd try
 +----+----------+-------------+----------+--------------------+

状态\备份\ tb（旧值（b4更改）在状态\ tb中更新后通过触发器落入备份）

+----------+----+----------+-------------+----------+----------------+
 |backup_id | id | status   | description | state_id |last_modified_on|
 +----------+----+----------+-------------+----------+----------------+
 |  1       |  1 |new       | north       | 1        |2018-10-10 12:30:00
 |  2       |  2 |assign    | south       | 2        |2018-10-10 13:30:00
 |  3       |  3 |Postponed | east        | 2        |2018-10-10 24:30:00
 |  4       |  4 | Fixed    | west        | 3        |2018-10-10 15:30:00
 |  7       |  35| Rejected | Testing     | 4        |2018-10-10 11:00:00
 +----------+----+----------+-------------+----------+

 +----------+----+----------+-------------+----------+----------------+
 |backup_id | id | status   | description| state_id |last_modified_on|
 +----------+----+----------+------------+----------+----------------+
 |  1       |  1 |new       | north      | 1        |2018-10-10 12:30:00
 |  2       |  2 |assign    | south      | 2        |2018-10-10 13:30:00
 |  3       |  3 |Postponed | east       | 2        |2018-10-10 24:30:00
 |  4       |  4 | Fixed    | west       | 3        |2018-10-10 15:30:00
 |  7       |  35| Rejected | Testing    | 4        |2018-10-10 11:00:00
 |  8       |  35| Test     | South-test | 4        |2018-10-10 20:30:00<

第一次执行查询以插入和选择比较值时

|new_id | id |Column_changed| Old_value|New_value | Modified_on       |
+-------+----+--------------+----------+----------+-------------------+
|1      | 35 | status       |Rejected  | Test     |2018-10-10 20:30:00|
|2      | 35 |description   |Testing   |South-test|2018-10-10 20:30:00| 
+-------+----+--------------+----------+----------+-------------------+

第二次执行查询以插入和选择比较值
第一次修改的修改日期变回 2018-10-10 22:22:22 而不是留下来 2018-10-10 20:30:00 当我在同一个id上应用更改时
实际结果（错误）

|new_id| id |Column_changed| Old_value  |New_value |Modified_on        |
+------+----+--------------+------------+----------+-------------------+
|1     | 35 | status       |Rejected    |Test      |2018-10-10 22:22:22|<
|2     | 35 |description   | Testing    |South-test|2018-10-10 22:22:22|< 
+------+----+--------------+------------+----------+-------------------+
|3     | 35 | status       | Test       | Try_OLD  |2018-10-10 22:22:22|
|4     | 35 |description   | South-test |Tried_OLD |2018-10-10 22:22:22| 
+------+----+--------------+------------+----------+-------------------+

预期结果

|new_id | id |Column_changed| Old_value |New_value |Modified_on        |
+------+----+--------------+------------+----------+-------------------+
|1     | 35 | status       |Rejected    |Test      |2018-10-10 20:30:00|<
|2     | 35 |description   | Testing    |South-test|2018-10-10 20:30:00|< 
+------+----+--------------+------------+----------+-------------------+
|3     | 35 | status       | Test       | Try_OLD  |2018-10-10 22:22:22|
|4     | 35 |description   | South-test |Tried_OLD |2018-10-10 22:22:22| 
+------+----+--------------+------------+----------+-------------------+

新的\u id:1和2来自第一次更改，3和4来自第二次更改
sql语句

INSERT INTO audit_status_tb (id, Column_changed, Old_value, New_value) 

SELECT 
  distinct s.id, 
  'status' AS Column_changed, 
  s.status AS Old_value, 
  b.status AS New_value,
  s.last_modified_on 
  FROM status_tb AS s
  JOIN status_backup_tb AS b 
    ON b.id = s.id AND 
    b.status <> s.status

UNION ALL 

SELECT 
   distinct s.id, 
   'description' AS Column_changed, 
   s.description AS Old_value, 
   b.description AS New_value,
   s.last_modified_on 
     FROM status_tb AS s
     JOIN status_backup_tb AS b 
     ON b.id = s.id AND 
        b.description <> s.description

UNION ALL

SELECT 
  distinct  s.id, 
  'state_id' AS Column_changed, 
  s.state_id AS Old_value, 
  b.state_id AS New_value,
  s.last_modified_on 
     FROM status_tb AS s
     JOIN status_backup_tb AS b 
   ON b.id = s.id AND 
   b.state_id <> s.state_id