列出不属于特定类型的项的MySQL查询

Question

我试图在我们的数据库中得到一个要么是account_level IN ('basic', 'full')的公司列表。我们的客户是account_level =‘企业’。在许多情况下，从那时起，企业客户端在数据库中也会有行，它们是基本的或完全的，所以我想排除所有有企业行的基本/完整公司(即，我想排除我们当前的客户端)。这样，我就可以得到一个严格的基本或完整的公司名单，实际上还不是我们的客户。

下面是公司表的一个示例：

1    company a    basic
2    company a    full
3    company b    basic
4    company b    enterprise
5    company c    basic

我希望查询返回公司a和c。

我试着用：

    SELECT * 
    FROM company c1  
    INNER JOIN company c2 ON c1.id=c2.id
    WHERE c1.company NOT IN  (SELECT c2.company FROM company c2       
    WHERE account_level = 'enterprise')
    AND c1.account_level IN ('full', 'basic')
    ORDER BY c1.company;

但没有结果。有人能看出我做错了什么吗？对不起，我在mysql方面还不太有经验。谢谢你的帮助。

Answer 1

您可以使用EXISTS和NOT EXISTS的组合获得所需的结果。

SELECT DISTINCT c1.company 
FROM company c1
WHERE EXISTS (SELECT 1 
              FROM company AS c2 
              WHERE c1.company = c2.company AND c2.account_level IN ('full', 'basic'))

      AND

      NOT EXISTS (SELECT 1 
                  FROM company AS c3
                  WHERE c1.company = c3.company AND c3.account_level IN ('enterprise')

或者，甚至更简单：

SELECT DISTINCT c1.company 
FROM company c1
WHERE c1.account_level IN ('full', 'basic'))

      AND

      NOT EXISTS (SELECT 1 
                  FROM company AS c2
                  WHERE c1.company = c2.company AND c2.account_level IN ('enterprise')