得到错误的选择列表中无效，因为它不是在聚合函数或GROUP BY子句

Question

嗨，我是新来这里我SQL包含有表像这样

ID | Name 
    1 a 
    2 a 
    3 h 
    4 e 
    5 d 
    6 d 

想输出应该

name | IDS 
    a 1,2 
    d 5,6 

我曾试过，但抛出的错误

select distinct ID, (select CAST(t.ID AS VARCHAR(10)) +',' 
      from tbl t 
      where t.ID=tb.ID 
      for xml path('')) name 
from tbl tb 
group by name 

以下错误：

invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause 

任何一个可以建议我提前

Answer 1

WIRTE查询请感谢。如果你正在使用MySQL

select name, group_concat(t.id) 
from tbl tb 
group by name 
having count(*) > 1; 

在SQL Server：

select name, 
     stuff((select ',' + CAST(t.ID AS VARCHAR(255)) 
       from tbl t 
       where t.name = tb.name 
       for xml path('') 
      ), 1, 1, '') name 
from tbl tb 
group by name 
having count(*) > 1;