Is it posible to select a stack of value by using MYSQL GROUP BY
I got 开发者_运维知识库a query like this :
SELECT email
FROM abc_table
GROUP BY email
HAVING ( COUNT(email) > 1 )
So it will return me :
email
a@b.com
c@d.com
e@f.com
And now i need to adjust the query to get something like this :
email id
a@b.com 1
a@b.com 2
c@d.com 3
c@d.com 4
c@d.com 5
e@f.com 6
Is it posible to get this result by using GROUP BY HAVING ? Or any suggestion to get this result?
Thanks a lot!
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
Use GROUP_CONCAT:
SELECT email, GROUP_CONCAT(id) FROM abc_table GROUP BY email
(EDIT: misread the question)
精彩评论