合并 1 列并选择其他

Question

我有两个表，一个称为“固定”，一个称为“未读”

“固定”看起来像这样：

+---------+-------+
|pinned_by|post_id|
+---------+-------+
|2        |3      |
+---------+-------+
|2        |5      |
+---------+-------+

“未读”看起来像这样：

+---------+-------+
|unread_by|post_id|
+---------+-------+
|2        |5      |
+---------+-------+
|2        |10     |
+---------+-------+

我想从两个表中选择这个：

+-------+------+------+
|post_id|unread|pinned|
+-------+------+------+
|3      |0     |1     |
+-------+------+------+
|5      |1     |1     |
+-------+------+------+
|10     |1     |0     |
+-------+------+------+

我该怎么做？固定和未读的值可能是 1/0、1/null、true/false 等。我并不关心，只要我能区分哪些 post_id 来自未读、哪些来自固定、哪些来自来自两者。我正在使用MySQL。在本例中，_by 列全部为 2，但在实际实现中会有所不同。我们的想法是， where unread_by=2 和 where pinned_by=2 将以某种方式包含在内。

谢谢

Answer 1

假设有一个 Posts 表，你可以这样做：

Select P.post_Id
    , Max( Case When UR.unread_by Is Not Null Then 1 Else 0 End ) As unread
    , Max( Case When P2.pinned_by Is Not Null Then 1 Else 0 End ) As pinned
From Posts As P
    Left Join Unread As UR
        On UR.post_Id = P.post_Id
    Left Join Pinned As P2
        On P2.post_id = P.post_id
Group By P.post_id

Answer 2

SELECT COALESCE(pinned.post_id,unread_post_id) AS postid, unread_by, pinned_by 
FROM pinned OUTER JOIN unread ON pinned.post_id = unread.post_id

Answer 3

使用：

   SELECT x.post_id,
          COUNT(u.unread_by) AS unread,
          COUNT(x.pinned_by) AS pinned
     FROM (SELECT p.post_id
             FROM PINNED p
           UNION
           SELECT u.post_id
             FROM UNREAD u) x
LEFT JOIN UNREAD u ON u.post_id = x.post_id
LEFT JOIN PINNED p ON p.post_id = x.post_id
 GROUP BY x.post_id

Answer 4

您可以使用 UNION：

SELECT post_id, SUM(PinCount), SUM(UnreadCount)
FROM (
    SELECT post_id, COUNT(*) PinCount, 0 UnreadCount
    FROM pinned
    GROUP BY post_id
    UNION ALL SELECT post_id, 0, COUNT(*) UnreadCount
    FROM unread
    GROUP BY post_id
)
GROUP BY post_id

或者，如果您有一个包含所有帖子的 post 表，您可以使用该表 - 因为 MySql 没有完整的外连接。

SELECT t.post_id, p.PinCount, u.UnreadCount
FROM posts t LEFT JOIN (
    SELECT post_id, COUNT(*) PinCount
    FROM pinned
    GROUP BY post_id
  ) p USING(post_id)
  LEFT JOIN (
    SELECT post_id, COUNT(*) UnreadCount
    FROM unread
    GROUP BY post_id
  ) u USING(post_id)
GROUP BY t.post_id