mysql - 将两个表中不同列的行合并起来？

Question

我很难理解这个问题。我相信发生这种情况是因为我根据同一列 (user_id) 连接两个单独的表，但我不知道如何修复它，因为两个表之间唯一的共同点是 user_id 栏目。

这是查询。

SELECT users_data_existing.`date`,`message`,`action`,`status`,`data`,
users_data_new.`date`,`data_new`
FROM users_data_existing
INNER JOIN users_data_action USING (action_id)
INNER JOIN users_data_status_user USING (status_user_id)
INNER JOIN `users` USING (user_id)
INNER JOIN users_data_new USING (user_id)
INNER JOIN data ON users_data_existing.`data_id` = data.`id`
WHERE users_data_existing.`user_id` = 2
ORDER BY users_data_existing.`date`,users_data_new.`date` DESC

结果是，users_data_new.date 和 data_new 列连接或“附加”到前面的行。

+----------+-----------+-----------+-----------+-----------+----------+-----------+
|   date   |  message  |   action  |   status  |    data   |   date   |  data_new |
+----------+-----------+-----------+-----------+-----------+----------+-----------+
|2011-01-01|    data   |    data   |    data   |    data   |2011-01-02| data_new  |
-----------------------------------------------------------------------------------
|2011-01-01|    data   |    data   |    data   |    data   |2011-01-03| data_new1 |
-----------------------------------------------------------------------------------

               REPEATS PATTERN FOR TOTAL RECORDS IN users_data_new TABLE 

+----------+-----------+-----------+-----------+-----------+----------+-----------+
|   date   |  message  |   action  |   status  |    data   |   date   |  data_new |
+----------+-----------+-----------+-----------+-----------+----------+-----------+
|2011-01-01|   data1   |   data1   |   data1   |   data1   |2011-01-02| data_new  |
-----------------------------------------------------------------------------------
|2011-01-01|   data1   |   data1   |   data1   |   data1   |2011-01-03| data_new1 |
-----------------------------------------------------------------------------------

但这不是我需要的。如何将最后两列分成单独的行？我认为 UNION 可以解决这个问题，但我不能这样做，因为这些表几乎相同，但不共享 message 列。

Answer 1

正如问题中所怀疑的那样，我需要的是一个UNION。诀窍是在 users_data_new 中创建一个空列以匹配 user_data_existing。我在对其进行排序时也遇到了挑战，因此我也将其包含在这里。

(SELECT data_existing.date AS submitdate,status_user.status,action.action,
data.data,data_existing.message
FROM users_data_existing AS data_existing
INNER JOIN users_requested_status_user status_user 
ON data_existing.status_user_id = status_user.status_user_id
INNER JOIN users_requested_action action 
ON data_existing.action_id = action.action_id
INNER JOIN websites data 
ON data_existing.data_id = data.id
ORDER BY data_existing.date DESC) //sorts sub-query

UNION ALL

(SELECT data_new.date AS submitdate,status_user.status,  
action.action,data_new.data_new,'' message //needed to add this last empty column
FROM users_data_new AS data_new
INNER JOIN users_requested_status_user status_user 
ON data_new.status_user_id = status_user.status_user_id
INNER JOIN users_requested_action action 
ON data_new.action_id = action.action_id
ORDER BY data_new.date DESC) //sorts sub-query
ORDER BY submitdate DESC"; //sorts the entire result

请记住，对于日期别名，关联数组键将是您使用的任何别名。即 $result['submitdate']