Mysql select，如何查找在同一列中具有给定值的应用程序名称

Question

我有一个表，其中包含应用程序名称和授予该应用程序的权限。我需要找出需要这两者的应用程序是什么

name   permissions
app1    perm1
app1    perm5
app1    perm6
app2    perm1
app2    perm8
app3    perm1
app3    perm6
app3    perm2
app3    perm4

如何找到同时包含"perm1和perm6“....的应用程序名称？

Answer 1

这里有一种方法。

SELECT t.name
  FROM mytable t
 GROUP BY t.name
HAVING MAX(IFNULL(t.permissions,'')='perm1')
     + MAX(IFNULL(t.permissions,'')='perm6') = 2
 ORDER BY t.name

我们使用GROUP BY和聚合函数，如果我们找到一个具有“perm1”的行，则t.permisson= 'perm1‘返回1，否则返回0。(我们通过将t.permissions包装在IFNULL函数中来处理NULL的情况。)

如果同时找到'perm1‘和'perm6'，则HAVING谓词的值将为TRUE，并返回该行。

另一种方法是使用EXISTS谓词来运行相关子查询：

SELECT t.name
  FROM mytable t
 WHERE t.permissions = 'perm1'
   AND EXISTS 
       ( SELECT 1 
           FROM mytable s
          WHERE s.name = t.name
            AND s.permissions = 'perm6'
       )
 GROUP BY t.name
 ORDER BY t.name

或等效项，使用IN谓词：

 SELECT t.name
   FROM mytable t
  WHERE t.permissions = 'perm1'
    AND t.name IN 
        ( SELECT s.name
            FROM mytable s
           WHERE s.permissions = 'perm6'
             AND s.name IS NOT NULL
           GROUP BY s.name
        )
  GROUP BY t.name
  ORDER BY t.name

获得它的第四种方法是：

 SELECT t.name
   FROM mytable t
   JOIN mytable s
     ON t.name = s.name
    AND t.permissions = 'perm1'
    AND s.permissions = 'perm6'
  GROUP BY t.name
  ORDER BY t.name

Answer 2

SELECT name
FROM tableName
WHERE   permissions IN ('perm1','perm6')
GROUP BY name
HAVING COUNT(*) = 2

• SQLFiddle Demo

或

SELECT name
FROM tableName
WHERE   permissions IN ('perm1','perm6')
GROUP BY name
HAVING COUNT(DISTINCT permissions) = 2