一道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