select * from TEST0531
1、将女生爱好合并为一行
select '女',wm_concat(hobby) hobby from TEST0531 where sex= '女'
wm_concat(列名),该函数可以把列值以","号分隔起来,并显示成一行
2、将爱好拆分显示(注意:数据库中字段内数据应以英文逗号隔开:value1,value,value3)
select distinct (name), regexp_substr(hobby, '[^,]+', 1, level) hobby
from TEST0531
connect by regexp_substr(hobby, '[^,]+', 1, level) is not null
注意:distinct (name):使查询数据根据name唯一,这样查询出的数据不会重复
注意:regexp_substr(hobby, '[^,]+', 1, level:运用了正则表达式(具体意思没搞明白),
且这句话应与 connect by regexp_substr(hobby, '[^,]+', 1, level) is not null一同出现。
3、获取与周有相同爱好的人
select distinct (name)
from (select distinct (name),
regexp_substr(hobby, '[^,]+', 1, level) hobby
from TEST0531
connect by regexp_substr(hobby, '[^,]+', 1, level) is not null) h
where instr( (select hobby from TEST0531 where name ='周'),h.hobby)>0
instr('abc','a');表示在abc中查找有没有a这个字符。>0表示字符存在
4、获取与周有相同爱好的人的所有信息
select *
from TEST0531
where name in
(select distinct (name)
from (select distinct (name),
regexp_substr(hobby, '[^,]+', 1, level) hobby
from TEST0531
connect by regexp_substr(hobby, '[^,]+', 1, level) is not null) h
where instr((select hobby from TEST0531 where name = '周'), h.hobby) > 0)