mysql - 如何使用MySQL Group by查询

我有一个表，列名，EmailId，进程，状态如下。

**Input**

   Name       EmailId                Process      Status
   apple1     [email protected]       process1     Closed
   apple2     [email protected]       process2     In Progress
   apple3     [email protected]       process3     Complete
   orange1    [email protected]      process1     Closed
   orange2    [email protected]      process2     Closed
   oran       [email protected]      process3     Closed
   mango1     [email protected]       process1     To Start

现在我想要的是emailId和状态为所有进程关闭的人的状态-Process1，process2，Process 3

**Output**

EmailId              Status
[email protected]    Closed

因为橘子的生产过程已经结束了，我有上面一排
类似地，我希望拥有进程已关闭的表的所有电子邮件ID。

最佳答案

另一种方式：

SELECT
    EmailId
FROM
    tableX AS t
GROUP BY
    EmailId
HAVING
    COUNT( CASE WHEN Status <> 'Closed'
             THEN 1
           END
         ) = 0 ;

或：

SELECT
    t.EmailId
FROM
    ( SELECT DISTINCT EmailId
      FROM tableX
    ) AS t
  LEFT JOIN
    tableX AS tt
      ON  tt.EmailId = t.EmailId
      AND tt.Status <> 'Closed'
WHERE
    tt.EmailId IS NULL ;

关于mysql - 如何使用MySQL Group by查询，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/11451667/