MySQL左连接计数列同时连接3个表

Question

MySQL左连接计数列同时连接3个表

3

我有三个表是我的数据库的一部分。

debates (id 'PK', unit_id, starter_pack_id 'FK', title)

debate_stakeholders (id 'PK', starter_pack_id 'FK', name)

debate_groups (id 'PK', debate_id 'FK', student_id, stakeholder_id 'FK')

为此，所有辩论都共享相同的利益相关者（总共4个利益相关者，所有辩论都针对这些利益相关者进行引用）。

我期望的结果是查询所有辩论，显示辩论 ID、辩论标题、辩论利益相关者名称以及在特定辩论中这些利益相关者出现的次数，不考虑利益相关者数量是否为0。这一点很重要，因为当我执行其他查询时，需要知道哪些查询计数大于或等于1、0和null。

以下是我的数据库示例数据：

我期望你能够翻译以下内容：（数字只是为了展示可能的格式）

我的期望结果：

我尝试创建了这个MySQL查询，但是无法满足我的精确要求。

我尝试了类似以下的查询语句：

SELECT 
    a.id, 
    a.name, 
    a.abbreviation, 
    d.id AS debateId, 
    IF(COUNT(b.stakeholder_id) = 0, 0, COUNT(b.stakeholder_id)) AS total_freq 
FROM 
    debate_stakeholders a LEFT JOIN debate_groups b ON b.stakeholder_id = a.id 
    LEFT JOIN debates as d ON b.debate_id = d.id
GROUP BY
    a.id, b.debate_id,d.id
HAVING

    COUNT(*) < 3
ORDER BY a.id,d.id
LIMIT 1

但是并没有完全达到我的计划。

- Nnaut

你目前尝试了什么？你应该包括这个，一些示例数据会更好。 - Tim Biegeleisen

那么辩论表有两个唯一键？id和starter_pack_id？为什么会这样？ - Thorsten Kettner

@ThorstenKettner 为什么在 LEFT JOIN 中记录被过滤掉了？请看这里。 - Tim Biegeleisen

因为 debates.starter_pack_id 是来自另一张表的外键，但它对于这个解决方案是无关紧要的。事实上，我不应该把它包含在内。我会将其移除。 - Nnaut

啊，好的。你把它标记为“PK”了。 - Thorsten Kettner

2个回答

0

试试这个，希望能有所帮助：

SELECT d.id, COALESCE(d.title, 'NA') AS title, COALESCE(ds.name, 'NA') AS name, COUNT(ds.id) AS count
FROM debates d
LEFT JOIN debate_groups AS dg ON d.id = dg.debate_id
LEFT JOIN debate_stakeholders AS ds ON dg.stakeholder_id = ds.id
GROUP BY d.id, dg.stakeholder_id

- d.coder

不行。你过滤掉了一些版主。原帖作者希望每个辩论中都出现所有的版主。 - Tim Biegeleisen

我更希望每个利益相关者都参加每场辩论，无论他们的数量是否为0。 - Nnaut

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Thorsten Kettner · Accepted Answer

我必须承认，表格名称让我感到困惑。一个辩论__利益相关者与一场辩论无关。它更像是属于初始包的利益相关者，而也有属于初始包的辩论。至少这是我从表结构中读到的。然后，一个辩论_组由一个学生和一位在辩论中的利益相关者组成。把这个称为一个组很奇怪。

然而，似乎你想将所有利益相关者与初始包中的所有辩论相结合（即获得所有组合）。然后，你想计算与每个这样的辩论/利益相关者组合相关联的学生人数。因此，编写一个查询来计算每个辩论和利益相关者的学生人数（按辩论和利益相关者分组的聚合查询），并将其用作外部连接到辩论/利益相关者组合的子查询。

SELECT d.id,
       d.title,
       ds.name,
       COALESCE(dg.students, 0) AS "count"
FROM debates d
JOIN debate_stakeholders ds
    ON ds.starter_pack_id = d.starter_pack_id
LEFT JOIN
(
    SELECT debate_id, stakeholder_id, COUNT(*) AS students
    FROM debate_groups
    GROUP BY debate_id, stakeholder_id
) dg
    ON dg.debate_id = d.id AND
       dg.stakeholder_id = ds.id;

Demo here:

MySQL左连接计数列同时连接3个表

SQLFiddle