例如:
表account(accountid varchar(200)
roleid varchar(100))
表role_account (roleid varchar(100)
accountid varchar(200) )
select a.accountid,b.roleid from account a,role_account b
where a.accountid=b.accountid
order by accountid;
结果如下:
accountid roleid
-----------------------------
A 10
B 10
B 20
B 30
C 5
D 5
D 30
E 20
E 30
现在需要的查询结果集要求如下(把相同Accountid的roleid值拼接起来):
accountid roleid
-----------------------------
A 10
B 10,20,30
C 5
D 5,30
E 20,30求SQL(自定义函数除外)
表account(accountid varchar(200)
roleid varchar(100))
表role_account (roleid varchar(100)
accountid varchar(200) )
select a.accountid,b.roleid from account a,role_account b
where a.accountid=b.accountid
order by accountid;
结果如下:
accountid roleid
-----------------------------
A 10
B 10
B 20
B 30
C 5
D 5
D 30
E 20
E 30
现在需要的查询结果集要求如下(把相同Accountid的roleid值拼接起来):
accountid roleid
-----------------------------
A 10
B 10,20,30
C 5
D 5,30
E 20,30求SQL(自定义函数除外)
from account a,role_account b
where a.accountid=b.accountid
group by a.accountid
order by accountid;
select accountid,
substr(max(sys_connect_by_path(roleid,',')),2)roleid
from (
select a.accountid,b.roleid,
row_number()over(partition by a.ccountid order by rownum)rn
from account a,role_account b
where a.accountid=b.accountid )
start with rn=1
connect by prior rn=rn-1
group by accountid