水友居士的一个需求.
听他广东的普通话真是费劲.不过还是大概听明白了。

实验准备

Oracle

先创建Oracle的数字辅助表

create table nums(
id int primary key
);
insert into nums select level from dual connect by level<=50000;
commit;

create table t1(
userid int primary key,
fathername varchar2(32) ,
mothername varchar2(32) ,
CONSTELLATION varchar2(32)
);
truncate table t1;
insert into t1 select 1,'王大锤','大嫂子','白羊座' from dual;
insert into t1 select 2,'王钢蛋','绿茶','白羊座' from dual;
insert into t1 select 3,'彪子','朴一生','白羊座' from dual;
insert into t1 select 4,'朴一生','水鸡妹','白羊座' from dual;
insert into t1 select 5,'刘英','永强','白羊座' from dual;
insert into t1 select 6,'水鸡妹','彪子','白羊座' from dual;
insert into t1 select 7,'乔治','约翰','金牛座' from dual;
insert into t1 select 8,null,'永强','金牛座' from dual;
insert into t1 select 9,null,'铁头','金牛座' from dual;
insert into t1 select 10,'棒槌',null,'金牛座' from dual;
insert into t1 select 11,'铁头',null,'金牛座' from dual;
insert into t1 select 12,'约翰','约翰','金牛座' from dual;
insert into t1 select 13,'绿茶',null,'金牛座' from dual;
commit;
select * from t1;

该表有用户ID,父亲名称,母亲名称和星座

查询:
星座相同,父母任意一个名字相同的用户列表

中间运算的查询:

select * from (
select
CONSTELLATION,
case when rn=1 then fathername when rn=2 then mothername end w ,
wmsys.wm_concat( distinct rn||'|'|| userid) useridlist
from t1 u1 ,
(select rownum rn from dual connect by level<=2)
where case when rn=1 then fathername when rn=2 then mothername end is not null
group by CONSTELLATION,case when rn=1 then fathername when rn=2 then mothername end
) t2 where instr(useridlist,',')>0;

按照居士的需求，得到最终结果的SQL如下

SELECT REGEXP_SUBSTR(useridlist, '[^,]+', 1, id) AS value_str
FROM nums,
(
select CONSTELLATION,w,useridlist useridlist from (
select
CONSTELLATION,
case when rn=1 then fathername when rn=2 then mothername end w ,
wmsys.wm_concat( distinct userid) useridlist
from t1 u1 ,
(select rownum rn from dual connect by level<=2)
where case when rn=1 then fathername when rn=2 then mothername end is not null
group by CONSTELLATION,case when rn=1 then fathername when rn=2 then mothername end
) t2 where instr(useridlist,',')>0
) t3 where id<= (length(useridlist) - length(replace(useridlist,',',''))+1);

但是我诚然不知道他查询这个有什么用.

如果是我,可能会查询这些信息

select CONSTELLATION,w,nvl(k,'[汇总]'),userlist
from (
select
CONSTELLATION,w,
decode(substr(value_str,0,instr(value_str,'|')-1) , 1 , '父亲名称' , '母亲名称') k,
substr(value_str,instr(value_str,'|')+1) userid,
wm_concat(substr(value_str,instr(value_str,'|')+1)) userlist
from (
SELECT CONSTELLATION,w,
REGEXP_SUBSTR(useridlist, '[^,]+', 1, id) AS value_str
FROM nums,
(
select CONSTELLATION,w,useridlist useridlist from (
select
CONSTELLATION,
case when rn=1 then fathername when rn=2 then mothername end w ,
wmsys.wm_concat( distinct rn||'|'||userid) useridlist
from t1 u1 ,
(select rownum rn from dual connect by level<=2)
where case when rn=1 then fathername when rn=2 then mothername end is not null
group by CONSTELLATION,case when rn=1 then fathername when rn=2 then mothername end
) t2 where instr(useridlist,',')>0
) t3 where id<= (length(useridlist) - length(replace(useridlist,',',''))+1)
)
group by rollup
(
CONSTELLATION,w,
decode(substr(value_str,0,instr(value_str,'|')-1) , 1 , '父亲名称' , '母亲名称'),
substr(value_str,instr(value_str,'|')+1)
)
having
(
(GROUPING(CONSTELLATION) =0 and GROUPING(w) =0 and GROUPING(decode(substr(value_str,0,instr(value_str,'|')-1) , 1 , '父亲名称' , '母亲名称')) =0 and GROUPING(substr(value_str,instr(value_str,'|')+1)) =0)
or
(GROUPING(CONSTELLATION) =0 and GROUPING(w) =0 and GROUPING(decode(substr(value_str,0,instr(value_str,'|')-1) , 1 , '父亲名称' , '母亲名称')) =1 and GROUPING(substr(value_str,instr(value_str,'|')+1)) =1)
) order by 1,2,3
) ;

ITPub博客

两列任意匹配一列

最新文章