我有一個表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
周銳 閱讀(756)
評論(0) 編輯 收藏 所屬分類:
MySQL 、
Oracle