是否可以使用 MYSQL GROUP BY 选择堆栈值

Question

我收到这样的查询：

SELECT email
FROM abc_table
GROUP BY email
HAVING ( COUNT(email) > 1 )

所以它会返回我：

email   
[email protected]     
[email protected]     
[email protected]

现在我需要调整查询以获得这样的结果：

email       id
[email protected]     1
[email protected]     2   
[email protected]     3
[email protected]     4
[email protected]     5
[email protected]     6

是否可以通过使用 GROUP BY HAVING 获得此结果？或者有什么建议可以得到这个结果吗？

多谢！

Answer 1

SELECT a.email
     , a.id
FROM abc_table a
  JOIN
    ( SELECT email
      FROM abc_table
      GROUP BY email
      HAVING COUNT(email) > 1 
    ) AS ag
    ON ag.email = a.email

Answer 2

使用 GROUP_CONCAT：（

SELECT email, GROUP_CONCAT(id) FROM abc_table GROUP BY email

编辑：误读问题）