一、hive中实现方法
基表：
组表：
gt | |
gid | gname |
1001 | g1 |
1002 | g2 |
1003 | g3 |
create table g(
gid int,
gname string
)row format delimited fields terminated by '\t'
stored as textfile;
用户表：
ut | |
uid | uname |
10001 | u1 |
10002 | u2 |
10003 | u3 |
10004 | u4 |
10005 | u5 |
10006 | u6 |
10007 | u7 |
10008 | u8 |
10009 | u9 |
10010 | u10 |
create table u(
uid int,
uname string
)row format delimited fields terminated by '\t'
stored as textfile;
权限表：
gu | |
gid | uid |
1001 | 10002,10001,10003,10009 |
1002 | 10004,10005,10006 |
1003 | 10007,10008,10010 |
create table gu(
gid int,
uid string
)row format delimited fields terminated by '\t'
stored as textfile;
组表gt中记录了组的信息组id和组名称，用户表记录了用户基本信息用户id和用户名称，gu是组表和用户表的关系，记录了每一个组内与用户对应关系，其中仅记录id信息。题目是根据gt和ut表将gu表中的所有id转换为名称？
我写的SQL是：
select t.gname,concat_ws(',',collect_set(t.uname)) from (
select g.gname,u.uname
from (
select gid,s_uid from gu
lateral view explode(split(uid,',')) uid as s_uid) temp,g,u
where temp.gid=g.gid and temp.s_uid=u.uid) t
group by t.gname;
运行结果如下：
hive> select t.gname,concat_ws(',',collect_set(t.uname)) from (
> select g.gname,u.uname
> from (
> select gid,s_uid from gu
> lateral view explode(split(uid,',')) uid as s_uid) temp,g,u
> where temp.gid=g.gid and temp.s_uid=u.uid) t
> group by t.gname;
OK
g1 u2,u1,u3,u9
g2 u4,u5,u6
g3 u7,u8,u10
二、oracle中实现方法
1、建立基表
create table g(
gid number(10),
gname varchar2(20)
)
create table u(
usrid number(10),
uname varchar2(20)
)
create table gu(
gid number(10),
usrid varchar2(200)
)
我所实现的sql方法如下：
select g.gname,
(select wm_concat(uname) from u where instr(gu.usrid, u.usrid) > 0)
from gu, g
where gu.gid = g.gid;
执行结果：
SQL> select g.gname,
2 (select wm_concat(uname) from u where instr(gu.usrid, u.usrid) > 0)
3 from gu, g
4 where gu.gid = g.gid;
GNAME (SELECTWM_CONCAT(UNAME)FROMUWH
-------------------- --------------------------------------------------------------------------------
g1 u1,u2,u3,u9
g2 u4,u5,u6
g3 u7,u8,u10