使用过滤位置对同一个表进行内连接

Question

谢谢你帮助我，我还在学习 SQL。我有一个名为 JEDI 的主表，我正在尝试获取有关使用 Inner JOIN 和Where 条件的具体信息。具体来说，我希望获得在特定日期之间访问特定星球的 JEDI 数量及其等级。

然后我想在同一张表上使用 Inner JOIN 进行过滤，并仅向我显示在该特定星球上拥有超过 1 个 padawan 的 JEDI 的结果。我认为第二个条件让我失望。我已经粘贴了我使用过的代码和示例数据表

我的结果应该只是2，欧比万大师是他唯一符合标准的

SELECT COUNT(jedi.jedi_id),jedi.rank_id
FROM jedi
WHERE jedi.date >='2022-01-01' AND jedi.date <='2022-06-31' AND jedi.planet='Tatoine'
INNER JOIN jedi ON jedi.jedi_id WHERE COUNT(jedi.padawan)>=2
GROUP BY jedi.rank_id

Answer 1

首先，您需要对列进行分组并使用 HAVING COUNT 来计算重复值。

SELECT COUNT(jedi.jedi_id),jedi.rank_id
FROM jedi
WHERE jedi.date BETWEEN '2022-01-01' AND '2022-06-31' 
                                     AND jedi.planet='Tatoine'
GROUP BY jedi.jedi_id, jedi.rank_id
HAVING COUNT(jedi.jedi_id) > 1

注意：仅当每个 jedi_id 具有唯一值 jedi.padawan 时，此功能才有效。此查询适用于这些场景。

但是，您可以通过添加另一个计算唯一 jedi.padawan 的条件来改进查询，以便每个 jedi.jedi_idjedi.padawan 值/code> 不会被包含

SELECT COUNT(jedi.jedi_id),jedi.rank_id
FROM jedi
WHERE jedi.date BETWEEN '2022-01-01' AND '2022-06-31' 
                                     AND jedi.planet='Tatoine'
GROUP BY jedi.jedi_id, jedi.rank_id
HAVING COUNT(jedi.jedi_id) > 1 AND
       COUNT (DISTINCT jedi.padawan) > 1

Answer 2

您可以使用必须替换此 INNER JOIN jedi ON jedi.jedi_id WHERE COUNT(jedi.padawan)>=2