函数拼接记录集某个字段，逗号分隔

数据表：user

id	name	group_id
1	张三	1
2	李四	1
3	王五	2

 

 

 

 

 

1. mysql、sqlite：group_contact函数:　 

select group_concat(id) ids, group_id from user group by group_id

结果：

ids	group_id
1,2	1
3	2

 

 

 

2.mssql：stuff函数 + FOR XML PATH

select stuff((select ','+convert(varchar(25), id) from user as t2 where t2.group_id = t1.group_id FOR XML PATH('')), 1, 1, '' ) as ids,
group_id from user as t1 group by group_id 

结果：

ids	group_id
1,2	1
3	2