一道hive SQL面试题
一、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
