从多个表插入到一个表中(MySql)

Question

insert ignore into user_login_history 
(`created_at`,`updated_at`,`created_by`,
`updated_by`,`USER_ID`,`TENANT`,
`LAST_LOGIN`,`deleted`,`published`)
values(
(SELECT 
a.created_at,
a.updated_at,
a.created_by,
a.updated_by,
user.id,
a.tenant,
a.created_at,
b'0',
b'1'
FROM
(SELECT audit_log_summary.*
FROM audit_log_summary, (SELECT 
user, MAX(created_at) AS created_at, tenant
FROM
audit_log_summary
WHERE
audit_log_summary.REVISION_TYPE = 'LOGGED_IN'
GROUP BY user , TENANT) max_user
WHERE
audit_log_summary.user = max_user.user
AND audit_log_summary.tenant = max_user.tenant
AND audit_log_summary.created_at = max_user.created_at) a
INNER JOIN
user ON a.user = user.email));

错误:

错误码:1136。列计数与第1行的值计数不匹配

基本上我有3个表，我想在一个表中填充数据，从其他两个表中获取数据。

要填充的表:user_login_history获取数据的表:audit_log_summary &用户

Answer 1

您必须从子句中删除VALUES，因此使用INSERT INTO SELECT FROM代替INSERT INTO VALUES。也许SELECT audit_log_summary.*应该只选择需要的列。在你的选择中，你选择了a.created_at两次，检查它是否是你想要的。也许这会有帮助:

insert ignore into user_login_history  (`created_at`,
`updated_at`,
`created_by`, 
`updated_by`,
`USER_ID`,
`TENANT`,
`LAST_LOGIN`,
`deleted`,
`published`)
(SELECT 
a.created_at,
a.updated_at,
a.created_by,
a.updated_by,
user.id,
a.tenant,
a.created_at,
b'0',
b'1'
FROM
(SELECT audit_log_summary.*
FROM audit_log_summary, (SELECT 
user, MAX(created_at) AS created_at, tenant
FROM
audit_log_summary
WHERE
audit_log_summary.REVISION_TYPE = 'LOGGED_IN'
GROUP BY user , TENANT) max_user
WHERE
audit_log_summary.user = max_user.user
AND audit_log_summary.tenant = max_user.tenant
AND audit_log_summary.created_at = max_user.created_at) a
INNER JOIN
user ON a.user = user.email));