将具有不同列的表合并到单个查询中

Question

我正在编写一个网站，我想制作一个时间线来显示用户的活动：他写的评论(在网站上的文章(，他发布的消息(在论坛上(和他投的[赞成|反对]票。

现在我有三个表(跳过了不相关的字段(：

COMMENTS
id | author_id | article_id | timestamp
MESSAGES
id | author_id | thread_id | timestamp
VOTES
id | author_id | comment_id | message_id | type | timestamp

我想执行一个查询，给定一个author_id将返回一个结果，该结果结合了来自 COMMENTS 的article_id字段、来自 MESSAGES 的thread_id、来自 VOTES 的comment_id、message_id和type字段以及公共timestamp字段，所有这些都按时间戳排序。结果应该是这样的：

article_id | thread_id | comment_id | message_id | type | timestamp

我

正在使用PostgreSQL，到目前为止，我无法生成这种结果。

有人能指出我正确的方向吗？

编辑

为了澄清我想要实现的目标，如果我在数据库中有以下数据：

COMMENTS
id | author_id | article_id | timestamp
 1 |         1 |         1  | 11:00:10
 2 |         1 |         2  | 11:00:20
MESSAGES
id | author_id | thread_id | timestamp
 3 |         1 |        20 | 12:00:10
 4 |         1 |        21 | 12:00:20
VOTES
id | author_id | comment_id | message_id | type | timestamp
 5 |         1 |          1 |            |    1 | 13:00:10
 6 |         1 |            |          3 |    2 | 13:00:20

那么结果应该是这样的：

article_id | thread_id | comment_id | message_id | type | timestamp
         1 |           |            |            |      | 11:00:10
         2 |           |            |            |      | 11:00:20
           |        20 |            |            |      | 12:00:10
           |        21 |            |            |      | 12:00:20
           |           |          1 |            |    1 | 13:00:10
           |           |            |          3 |    2 | 13:00:20

提前谢谢。

Answer 1

我不知道

为什么需要此输出，但您可以尝试以下联合查询：

SELECT
    article_id, NULL, NULL, NULL, NULL, timestamp
FROM COMMENTS
WHERE author_id = 1
UNION ALL
SELECT
    NULL, thread_id, NULL, NULL, NULL, timestamp
FROM MESSAGES
WHERE author_id = 1
UNION ALL
SELECT
    NULL, NULL, comment_id, message_id, type, timestamp
FROM VOTES
WHERE author_id = 1
ORDER BY timestamp

Answer 2

SOlution

select c.author_id ,article_id, thread_id , comment_id, message_id, type, c.timestamp
    FROM COMMENTS c
    LEFT JOIN MESSAGES m
        ON c.author_id = m.author_id and c.timestamp = m.timestamp
    LEFT JOIN VOTES v
        ON c.author_id = v.author_id and c.timestamp = v.timestamp
    where author_id=@author_id

如果时间戳不同，请尝试此操作

select c.author_id ,article_id, thread_id , comment_id, message_id, type, isnull(isnull(c.timestamp,m.timestamp),v.timestamp) as timestamp
    FROM COMMENTS c
    full JOIN MESSAGES m
        ON c.author_id = m.author_id 
    full JOIN VOTES v
        ON c.author_id = v.author_id 
    where author_id=@author_id