SQL Server、MySQL和 Oracle的行合并
多行一列数据合并成一行一列数据
在MySQL和Oracle中实现行合并
SQL Server
--SQL2005中的方法2
create table tb(id int, value varchar(10))
insert into tb values(1, 'aa')
insert into tb values(1, 'bb')
insert into tb values(2, 'aaa')
insert into tb values(2, 'bbb')
insert into tb values(2, 'ccc')
go
select id, [values]=stuff((select ','+[value] from tb t where id=tb.id
for xml path('')), 1, 1, '')
from tb
group by id
/*
id values
----------- --------------------
1 aa,bb
2 aaa,bbb,ccc
(2 row(s) affected)
*/
drop table tb
MySQL
create table tb(id int, value varchar(10))
insert into tb values(1, 'aa')
insert into tb values(1, 'bb')
insert into tb values(2, 'aaa')
insert into tb values(2, 'bbb')
insert into tb values(2, 'ccc')
go
select id, [values]=stuff((select ','+[value] from tb t where id=tb.id
for xml path('')), 1, 1, '')
from tb
group by id
/*
id values
----------- --------------------
1 aa,bb
2 aaa,bbb,ccc
(2 row(s) affected)
*/
drop table tb
MySQL
select name , group_concat(email order by email separator ", ") as email from student group by name
Oracle
如果以上效果想在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
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
版权保护: 本文由 绿茶加糖 原创，转载请保留链接: https://www.guobaosheng.com/shujuku/16.html
