状态\u tb
+----+----------+-------------+----------+
| id | status | description | state_id |
+----+----------+-------------+----------+
| 1 | new | north | 1 |
| 2 | assign | south | 2 |
| 3 |Postponed | east | 2 |
| 4 | Fixed | west | 3 |
| 35 | Test | South-test | 4 |
+----+----------+-------------+----------+
状态\u备份\u tb
+----------+----+----------+-------------+----------+
|backup_id | id | status | description | state_id |
+----------+----+----------+-------------+----------+
| 1 | 1 |new | north | 1 |
| 2 | 2 |assign | south | 2 |
| 3 | 3 |Postponed | east | 2 |
| 4 | 4 | Fixed | west | 3 |
| 7 | 35| Rejected | Testing | 4 |
+----------+----+----------+-------------+----------+
一旦我从结果mysql得到输入结果,我需要将这些结果插入另一个表(audit\u status\u tb),如下所示。。。我如何实现它?
这里是获取所需记录的sql
(SELECT
s.id,
'status' AS Column_changed,
s.status AS Old_value,
b.status AS New_value
FROM status_tb AS s
JOIN status_backup_tb AS b
ON b.id = s.id AND
b.status <> s.status)
UNION ALL
(SELECT
s.id,
'description' AS Column_changed,
s.description AS Old_value,
b.description AS New_value
FROM status_tb AS s
JOIN status_backup_tb AS b
ON b.id = s.id AND
b.description <> s.description)
UNION ALL
(SELECT
s.id,
'state_id' AS Column_changed,
s.state_id AS Old_value,
b.state_id AS New_value
FROM status_tb AS s
JOIN status_backup_tb AS b
ON b.id = s.id AND
b.state_id <> s.state_id)
审核状态
|new_id | id |Column_changed| Old_value |New_value |
+-------+----+--------------+-------------+----------+
|1 | 35 | status | Test | Rejected |
|2 | 35 |description | South-test | Testing |
+-------+----+--------------+-------------+----------+
我不知道什么样的插入选择?我应该用mysql来检索这些值并以上面的格式输入它们。。。
1条答案
按热度按时间tuwxkamq1#
你可以简单地使用
Insert Into .. Select
声明: