sql - 扩展 mySQL 查询以包含更多内容

Question

我有一个 MySQL 查询，它选择当前用户的 friend 创建的内容并按时间倒序列出该内容。

    SELECT node.nid AS nid,
   node.created AS node_created
 FROM node node 
 LEFT JOIN user_relationships user_relationships ON node.uid = user_relationships.requestee_id
 LEFT JOIN users users_user_relationships ON user_relationships.requestee_id = users_user_relationships.uid
 WHERE (node.status <> 0) AND (node.type in ('drigg', 'blog_post')) AND (user_relationships.requester_id = 1)
   ORDER BY node_created DESC

我如何“扩展”这个查询，以便除了节点之外它还列出评论？

评论表的结构如下:

• cid(评论 ID)
• uid(发表评论的用户 ID)
• 时间戳(使用相同的 UNIX 日期格式为“节点创建”列来自上面的节点表)

所以现在查询会获取当前用户的好友所写的所有“节点”。我希望它也能获取当前用户的好友所写的所有评论。然后我想根据它们的创建日期以 DESC 顺序列出这些节点和评论。

看来我需要向我的 select 语句添加更多内容，但我不确定是什么。

Answer 1

您可以使用 UNION ALL 组合两个查询的结果:

SELECT nodeid, commentid, created FROM (
    SELECT node.nid AS nodeid, NULL as commentid, node.created AS created
    FROM node node 
    LEFT JOIN user_relationships user_relationships ON node.uid = user_relationships.requestee_id
    LEFT JOIN users users_user_relationships ON user_relationships.requestee_id = users_user_relationships.uid
    WHERE (node.status <> 0) AND (node.type in ('drigg', 'blog_post')) AND (user_relationships.requester_id = 1)
    UNION ALL
    SELECT NULL as nodeid, comment.cid AS commentid, comment.timestamp AS created
    FROM node node 
    LEFT JOIN user_relationships user_relationships ON node.uid = user_relationships.requestee_id
    LEFT JOIN users users_user_relationships ON user_relationships.requestee_id = users_user_relationships.uid
    WHERE (node.status <> 0) AND (node.type in ('drigg', 'blog_post')) AND (user_relationships.requester_id = 1)
)
ORDER BY created DESC