php - mysql 将 3 个表中的选择与一个不同的列结合起来

Question

我有 3 个类似的表格，其中有 1 个不同的列

id | user_id | post_id | other_id | favorite(tinyint) | date

id | user_id | post_id | other_id | like(tinyint) | date

id | user_id | post_id | other_id | comment(tinyint) | date

有没有办法返回 user_id = 1 且按日期排序的所有组合？

Answer 1

我想你想要这样的东西:

SELECT * FROM (
SELECT id, user_id, post_id, other_id, 'favorite' as type, date FROM table1
UNION 
SELECT id, user_id, post_id, other_id, 'like' as type, date FROM table2
UNION
SELECT id, user_id, post_id, other_id, 'comment' as type, date FROM table3
) AS t ORDER BY date