我有一個表student, 有id, name, email, 這時候我表里記錄有name重復(fù)的值, 而email不一樣, 我想按照name來group by一下, 把有重復(fù)的數(shù)據(jù)里面的email用逗號分開寫在一起.
表結(jié)構(gòu)以及數(shù)據(jù)如下:
id name email
1 eric zhourui@founder.com
2 maggie maggie@163.com
3 scott scott@yahoo.com
4 eric eric@163.com
我想的到如下數(shù)據(jù)
eric zhourui@founder.com,eric@163.com
maggie maggie@163.com
scott scott@yahoo.com
如果使用MySQL, 則實(shí)現(xiàn)語句如下:
select name ,
group_concat(email order by email separator ", ") as email from student group by name
如果以上效果想在Oracle中顯示, 則比較復(fù)雜點(diǎn)了, 因?yàn)镺racle中沒有行合并函數(shù), 則需要使用sys_connect_by_path()來實(shí)現(xiàn), 代碼如下:
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
周銳 閱讀(756)
評論(0) 編輯 收藏 所屬分類:
MySQL 、
Oracle