mySQL 多重选择 : 1 complicated 1 simple

Question

我想将两个查询放在一起；一份来 self 的 posts 表，一份来 self 的 store_products 表，并在同一页面上显示结果。

所以我想拿这个...

SELECT 
      p.id AS pid 
    , p.uid
    , p.title
    , p.created_at
    , u.id AS uid
    , u.username
    , u.avatar 
    , f.following 
    , l.uid AS liked
    , u2.username AS source_username
    , u2.avatar AS source_avatar
    , u2.created_at AS source_created_at
FROM posts p 
LEFT JOIN users u ON p.uid=u.id 
LEFT JOIN posts_likes l ON l.pid=p.id 
LEFT JOIN follow f ON u.id = f.following 
LEFT JOIN posts p2 ON p.source_hash = p2.hash 
LEFT JOIN users u2 ON u2.id = p2.uid 
WHERE (f.user=2 OR p.uid=2) 
GROUP BY p.id ORDER BY p.id DESC

然后把它和这样的东西放在一起......

SELECT name, price FROM store_products WHERE uid=2

我尝试了多个 SELECT 语句，UNION，其中我会使用 NULL 来弥补 cols 的差异，我'已经尝试过LEFT、RIGHT和INNER联接，但所有这些所做的只是将产品信息添加到其他帖子行中，而不是创建单独的行对于每个产品。

我正在寻找的结果集与此类似

[0] Post 1
[1] Post 2
[2] Product 1
[3] Post 3
[4] Product 2

等等...

编辑:回答问题。两个表之间唯一的关系是用户id，否则它们是完全独立的。我想向访问者提供帖子和产品的列表，并且我尝试在一个查询中完成此操作，以节省服务器负载，并将它们放在同一个列表中，而不是单独的列表中。

Answer 1

使用联合:

SELECT 
      "Post" AS type,
    , p.id AS pid 
    , p.uid
    , p.title
    , p.created_at
    , u.id AS uid
    , u.username
    , u.avatar 
    , f.following 
    , l.uid AS liked
    , u2.username AS source_username
    , u2.avatar AS source_avatar
    , u2.created_at AS source_created_at
    , NULL AS product_name
    , NULL AS price
FROM posts p 
LEFT JOIN users u ON p.uid=u.id 
LEFT JOIN posts_likes l ON l.pid=p.id 
LEFT JOIN follow f ON u.id = f.following 
LEFT JOIN posts p2 ON p.source_hash = p2.hash 
LEFT JOIN users u2 ON u2.id = p2.uid 
WHERE (f.user=2 OR p.uid=2) 
GROUP BY p.id 
ORDER BY p.id DESC

UNION

SELECT 
      "Product" AS type
    , NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL
    , name, price
FROM store_products
WHERE uid = 2

然后，应用程序代码可以检查type列，以确定它是否正在打印Post或Product，并对其进行适当的格式化。