使用MySQL从两个表中分组数据

Question

我有两个表，分别是posts和posts_flags。其中posts存储用户发表的帖子行数据。如果某一帖子被举报，其他用户可以对其进行举报，从而在posts_flags表中创建一个标记。所以基本上，posts_flags表可以为同一帖子添加多个标记。

现在，我想从posts表中获取帖子行数据，并添加一列来显示已被标记的帖子。要获取该列，我尝试使用post_id作为内部连接键连接posts和posts_flags。但是，问题出现了，因为posts_flags表可以为同一帖子添加多行记录。

我该如何获取这些数据呢？下面是我的表结构示例和我想要实现的目标。

posts表

| post_id | post_title | post_body |
|    1    |   title a  | something |
|    2    |   title b  | something |
|    3    |   title c  | something |

posts_flags表

| post_id | user_id |
|    1    |   4     | 
|    1    |   5     | 
|    2    |   5     | 

我想要实现的目标

|  post_id | post_title | post_body | flagged
|    1     | title a    | something |   1
|    2     | title b    | something |   1
|    3     | title c    | something |   0

谢谢！

- nTuply

---

为什么结果中第三行的文章ID是2而不是3？ - Barmar

@Barmar 对不起，是打错了。我已经编辑好了。 - nTuply

Answer 1

与其与整个表合并，不如使用仅返回每篇帖子 ID 的 1 行的子查询进行连接。

此外，它也需要是一个 LEFT JOIN 而不是 INNER JOIN，以便结果将包含没有标记的帖子行。

SELECT p.*, pf.post_id IS NOT NULL AS flagged
FROM posts AS p
LEFT JOIN (
    SELECT DISTINCT post_id
    FROM posts_flags) AS pf ON p.post_id = pf.post_id

- Barmar

Answer 2

您可以使用内联子查询来检查当前文章是否曾被标记，例如：

SELECT
    p.*,
    CASE WHEN EXISTS (
        SELECT 1 FROM posts_flags pf WHERE pf.post_id = p.post_id
    ) THEN 1 ELSE 0 END flagged
FROM posts p

或者更好的方法是：

SELECT
    p.*,
    EXISTS (SELECT 1 FROM posts_flags pf WHERE pf.post_id = p.post_id) flagged
FROM posts p

Demo on DB Fiddle:

| post_id | post_title | post_body | flagged |
| ------- | ---------- | --------- | ------- |
| 1       | title a    | something | 1       |
| 2       | title b    | something | 1       |
| 3       | title c    | something | 0       |

重要提示：为了提高性能，您需要在“posts_flags(post_id)”上创建一个索引。

- GMB

---

我认为这是最好的解决方案。在 posts_flags(post_id) 上建立索引，它应该具有最佳性能。而且，这是第一个提出的，所以我不知道为什么没有被接受。 - Gordon Linoff

@GordonLinoff：好吧，想不到啊... 不过还是谢谢！ - GMB

Answer 3

在GROUP BY查询中计算标志的数量，并检查标志计数是否大于零：

select
  p.post_id,
  p.post_title,
  p.post_body,
  count(f.post_id) > 0 as flagged
from posts p
left join posts_flags f on f.post_id = p.post_id
group by p.post_id, p.post_title, p.post_body

您可以使用以下方式：

min(f.post_id) is not null as flagged

那可能看起来有点混乱，但它避免了阅读帖子的所有标志。

- Paul Spiegel