mysql - 使用合并后尝试删除重复的数据行

Question

我有一个表，其中针对我的用户拥有的每种访问类型都有一条记录:

cm812    BRTYF78
cm812    BRgh5VD
cm812    GHYF7GJ

我想查询表并得到:

cm812    BRTYF78,BRgh5VD,GHYF7GJ

这是我写的:

  SELECT id
      ,COALESCE(access+',' ,'') + (access) as access 
  FROM [accesslist]
    group by id, access

我得到的是:

cm812      BRTYF78,BRgh5VD,GHYF7GJ

cm812      BRTYF78,BRgh5VD,GHYF7GJ

cm812      BRTYF78,BRgh5VD,GHYF7GJ

如何获得一个条目？？？:

cm812      BRTYF78,BRgh5VD,GHYF7GJ

Answer 1

SQL Server Fiddle :

SELECT A.id, 
SUBSTRING((
  SELECT ', ' + B.access 
  FROM accesslist B 
  WHERE B.id = A.id
  ORDER BY access
  For XML Path('')),2,8000) AS AccessList
FROM accesslist A  
GROUP BY A.id

MySQL Fiddle :

SELECT A.id, 
  GROUP_CONCAT(access ORDER BY access SEPARATOR ', ') AS AccessList
FROM accesslist A  
GROUP BY A.id