我有一个表student, 有id, name, email, 这时候我表里记录有name重复的值, 而email不一样, 我想按照name来group by一下, 把有重复的数据里面的email用逗号分开写在一起.
表结构以及数据如下:
id name email
1 eric zhourui@founder.com
2 maggie maggie@163.com
3 scott scott@yahoo.com
4 eric eric@163.com
我想的到如下数据
eric zhourui@founder.com,eric@163.com
maggie maggie@163.com
scott scott@yahoo.com
如果使用MySQL, 则实现语句如下:
select name ,
group_concat(email order by email separator ", ") as email from student group by name
如果以上效果想在Oracle中显示, 则比较复杂点了, 因为Oracle中没有行合并函数, 则需要使用sys_connect_by_path()来实现, 代码如下:
select name, ltrim(sys_connect_by_path(email,','),',') email from(
select name,email,
row_number() over(partition by name order by email) rn,
count(*) over(partition by name) cnt
from student
) where level = cnt
start with rn = 1
connect by prior name = name and prior rn + 1 = rn
posted on 2009-06-24 09:18
周锐 阅读(731)
评论(0) 编辑 收藏 所属分类:
MySQL 、
Oracle