rucheng_hjp_tables

 

/*create table tabA

(a1 number,

a2 varchar2(100));

 

create table tabB

(b1 number,

a1 number,

b2 varchar2(100));

 

create table tabC

(c1 number,

c2 varchar2(100));*/

 

alter table tabA add a3 number

 

 

 

select * from tabA /*for update*/;

select * from tabB for update;

select * from tabC c

where c.c2 in ('张三','李四','王五','马六','赵大海');

select '''' || replace(b.b2, ',',''',''')|| '''' from tabB b

 

 

with tabB2 as

(SELECT bb.*,REGEXP_SUBSTR(b2 ,'[^,]+',1,l)AS servicereq

 FROM tabB bb,(SELECT LEVEL l FROM DUAL CONNECT BY LEVEL<=100) b

WHERE l <=LENGTH(b2) -LENGTH(REPLACE(b2,','))+1

ORDER BY 1,2)

select a.a1 表1Id,a.a2 表1性别,b.b1 表2Id,b.b2 表2Name,c.c1 表3Id,c.c2 

from tabA a

left join tabB2 b on a.a1 = b.a1

left join tabC c on b.servicereq = c.c2;

 

select a.a1 表1Id,a.a2 表1性别,b.b1 表2Id,b.b2 表2Name,c.c1 表3Id,c.c2 

from tabA a

left join (SELECT a.*,REGEXP_SUBSTR(b2,'[^,]+',1,l) AS servicereq

 FROM tabB a,(SELECT LEVEL l FROM DUAL CONNECT BY LEVEL<=100) b

WHERE l <=LENGTH(b2) - LENGTH(REPLACE(b2,','))+1

ORDER BY 1,2) b on a.a1 = b.a1

left join tabC c on b.servicereq = c.c2;

 

 

 

 

---------------------------------------------------------------------------------------------------------------------------------

--正式语句1

with tabB2 as

(SELECT bb.*,REGEXP_SUBSTR(b2 ,'[^,]+',1,l)AS c2

 FROM tabB bb,(SELECT LEVEL l FROM DUAL CONNECT BY LEVEL<=100) b

WHERE l <=LENGTH(b2) -LENGTH(REPLACE(b2,','))+1

--and bb.a1 in('1', '2')

ORDER BY 1,2)

select distinct a.a1, a.a2,c.c1,c.c2 ,'Y'as flag

from tabA a, tabB2 b, tabc c

where a.c3 = c.c3 and a.a1 = b.a1

and b.c2 = c.c2

--and a.a1 in('1', '2')

union

select a.a1, a.a2,c.c1,c.c2,'N' as flag

from tabA a, tabB2 b, tabc c

where a.c3 = c.c3 and a.a1 = b.a1 and b.c2!= c.c2

--and a.a1 in('1', '2')

 

--正式语句2

with tabB2 as

(SELECT bb.*,REGEXP_SUBSTR(b2 ,'[^,]+',1,l)AS c2

 FROM tabB bb,(SELECT LEVEL l FROM DUAL CONNECT BY LEVEL<=100) b

WHERE l <=LENGTH(b2) -LENGTH(REPLACE(b2,','))+1

--and bb.a1 in('1', '2')

ORDER BY 1,2)

select distinct a.a1, a.a2,c.c1,c.c2,

case when b.c2 = c.c2 then 'Y' when b.c2 !=c.c2 then 'N' end flag

from tabA a, tabB2 b, tabc c

where a.c3 = c.c3 and a.a1 = b.a1

--and a.a1 in('1', '2')

----------------------------------------------------------------------------------------------------------------

 

SELECT a.*,REGEXP_SUBSTR(b2 ,'[^,]+',1,l)AS servicereq

 FROM tabB a,(SELECT LEVEL l FROM DUAL CONNECT BY LEVEL<=100) b

WHERE l <=LENGTH(b2) -LENGTH(REPLACE(b2,','))+1

ORDER BY 1,2;

 

/*select a.a2,(select b.b2 from tabB bwhere b.a1 = a.a1 and b.b2 = c.c2) ifYet

from tabA a

left join tabC c on c.c1 = a.a3*/

 

张三,李四,王五,马六,赵大海

/*

insert into tabA(A1,A2)

values(1,'我是妹子');

insert into tabB(b1,b2,A1)

values(1,'张三,李四,王五,马六,赵大海',1);

insert into tabC(c1,c2)

values(1,'张三');

insert into tabC(c1,c2)

values(2,'李四');

insert into tabC(c1,c2)

values(3,'王五');

insert into tabC(c1,c2)

values(4,'马六');

insert into tabC(c1,c2)

values(5,'赵大海');

insert into tabC(c1,c2)

values(6,'鸡鸡侠');*/