我想比较表 1 的列 A 和表 2 的列 B。 两者都有逗号分隔的值。
就我而言,如果 B 列中的所有值都存在于 A 列中,我必须返回 TRUE
否则是假的。请指教。例如:
1.列 A 有 a,b,c,def 列 B 有 a,c,b 它应该返回 TRUE
2. 第 1 列有 vvv,ccc,rr 第 2 列有 ccc,rr,125 它应该返回 FALSE
3.第 1 列为空,第 2 列为空,应返回 TRUE
请帮忙
可以使用此 SQL 完成:
with t1 as( select '1111,1985,433435,5765' as colA from dual union all
select '232323,7777,8888,9999' as colA from dual union all
select '111,2000,433435,5765' as colA from dual),
t2 as ( select '1111,466576,24323' as colB from dual union all
select '11111,1985,435657' as colb from dual union all
select '2222,232323,24989323' as colb from dual )
select colA from t1 where exists(
select 1 from t2 where REGEXP_COUNT(','||t1.colA||',' , ',('||REPLACE(t2.colB, ',' , '|') || '),' ) > 0)
希望这个片段有帮助。
SELECT DECODE(NULLIF(
(SELECT LISTAGG(x.col_val,',') WITHIN GROUP(
ORDER BY x.col_val)intrsctnVal
FROM
(SELECT SUBSTR(REPLACE(COL2,',',''),LEVEL,1) col_val
FROM
(SELECT 'a,b,c' AS COL2 FROM DUAL
)B
CONNECT BY level <= regexp_count(col2,',')+1
INTERSECT
SELECT SUBSTR(REPLACE(COL1,',',''),LEVEL,1) col_val
FROM
(SELECT 'a,b,c,d' AS COL1 FROM DUAL
)A
CONNECT BY LEVEL <= REGEXP_COUNT(COL1,',')+1
)X
),
(SELECT 'a,b,c' AS COL2 FROM DUAL
)),NULL,'TRUE','FALSE') MATCH
FROM DUAL;
最终,我得到了它(对我来说,仅使用 SQL 解决它是一个有趣的任务:
B N SORTED BOOL
---------- ---------- -------------------------------------------------------------------------------- -----
1 1 [a],[b],[c] true
1 2 [ccc],[rr],[vvv] false
1 3 [] true
1 步:合并两个表:
SQL> with
2 t1 as (
3 select 1 n, 'c,b,a' c from dual
4 union all
5 select 2 n, 'vvv,ccc,rr' c from dual
6 union all
7 select 3 n, null c from dual),
8 t2 as (
9 select 1 n, 'a,b,c' c from dual
10 union all
11 select 2 n, 'ccc,rr,125' c from dual
12 union all
13 select 3 n, null c from dual),
14 t12 as (select 1 b, n, c from t1 union all select 2 b, n, c from t2)
15 select * from t12
16 /
B N C
---------- ---------- ----------
1 1 c,b,a
1 2 vvv,ccc,rr
1 3
2 1 a,b,c
2 2 ccc,rr,125
2 3
6 rows selected
2 步:计数分隔符:
SQL> with
2 t1 as (
3 select 1 n, 'c,b,a' c from dual
4 union all
5 select 2 n, 'vvv,ccc,rr' c from dual
6 union all
7 select 3 n, null c from dual),
8 t2 as (
9 select 1 n, 'a,b,c' c from dual
10 union all
11 select 2 n, 'ccc,rr,125' c from dual
12 union all
13 select 3 n, null c from dual),
14 t12 as (select 1 b, n, c from t1 union all select 2 b, n, c from t2),
15 t as (select b, n, c, nvl(length(regexp_replace(c, '[^,]+')) + 1, 1) cnt from t12)
16 select * from t
17 /
B N C CNT
---------- ---------- ---------- ----------
1 1 c,b,a 3
1 2 vvv,ccc,rr 3
1 3 1
2 1 a,b,c 3
2 2 ccc,rr,125 3
2 3 1
6 rows selected
3 步骤:计算边框值
SQL> with
2 t1 as (
3 select 1 n, 'c,b,a' c from dual
4 union all
5 select 2 n, 'vvv,ccc,rr' c from dual
6 union all
7 select 3 n, null c from dual),
8 t2 as (
9 select 1 n, 'a,b,c' c from dual
10 union all
11 select 2 n, 'ccc,rr,125' c from dual
12 union all
13 select 3 n, null c from dual),
14 t12 as (select 1 b, n, c from t1 union all select 2 b, n, c from t2),
15 t as (select b, n, c, nvl(length(regexp_replace(c, '[^,]+')) + 1, 1) cnt from t12),
16 tbrd as (select b, n, c, cnt, sum(cnt) over (order by b,n) brd from t)
17 select * from tbrd
18 /
B N C CNT BRD
---------- ---------- ---------- ---------- ----------
1 1 c,b,a 3 3
1 2 vvv,ccc,rr 3 6
1 3 1 7
2 1 a,b,c 3 10
2 2 ccc,rr,125 3 13
2 3 1 14
6 rows selected
4 步骤:为行生成新查询
SQL> with
2 t1 as (
3 select 1 n, 'c,b,a' c from dual
4 union all
5 select 2 n, 'vvv,ccc,rr' c from dual
6 union all
7 select 3 n, null c from dual),
8 t2 as (
9 select 1 n, 'a,b,c' c from dual
10 union all
11 select 2 n, 'ccc,rr,125' c from dual
12 union all
13 select 3 n, null c from dual),
14 t12 as (select 1 b, n, c from t1 union all select 2 b, n, c from t2),
15 t as (select b, n, c, nvl(length(regexp_replace(c, '[^,]+')) + 1, 1) cnt from t12),
16 tbrd as (select b, n, c, cnt, sum(cnt) over (order by b,n) brd from t),
17 allrec as (select level lv from dual connect by level <= (select sum(cnt) from t))
18 select * from allrec
19 /
LV
----------
1
2
3
4
5
6
7
8
9
10
11
12
13
14
14 rows selected
5 步骤:主查询
SQL> with
2 t1 as (
3 select 1 n, 'c,b,a' c from dual
4 union all
5 select 2 n, 'vvv,ccc,rr' c from dual
6 union all
7 select 3 n, null c from dual),
8 t2 as (
9 select 1 n, 'a,b,c' c from dual
10 union all
11 select 2 n, 'ccc,rr,125' c from dual
12 union all
13 select 3 n, null c from dual),
14 t12 as (select 1 b, n, c from t1 union all select 2 b, n, c from t2),
15 t as (select b, n, c, nvl(length(regexp_replace(c, '[^,]+')) + 1, 1) cnt from t12),
16 tbrd as (select b, n, c, cnt, sum(cnt) over (order by b,n) brd from t),
17 allrec as (select level lv from dual connect by level <= (select sum(cnt) from t)),
18 step5 as (select r.b, r.n, r.c, regexp_substr(c, '[^,]+', 1, r.brd - a.lv + 1) s from tbrd r, allrec a
19 where r.brd - a.lv between 0 and r.cnt - 1)
20 select * from step5
21 /
B N C S
---------- ---------- ---------- ----------
1 1 c,b,a a
1 1 c,b,a b
1 1 c,b,a c
1 2 vvv,ccc,rr rr
1 2 vvv,ccc,rr ccc
1 2 vvv,ccc,rr vvv
1 3
2 1 a,b,c c
2 1 a,b,c b
2 1 a,b,c a
2 2 ccc,rr,125 125
2 2 ccc,rr,125 rr
2 2 ccc,rr,125 ccc
2 3
14 rows selected
6 步:对值进行排序
SQL> with
2 t1 as (
3 select 1 n, 'c,b,a' c from dual
4 union all
5 select 2 n, 'vvv,ccc,rr' c from dual
6 union all
7 select 3 n, null c from dual),
8 t2 as (
9 select 1 n, 'a,b,c' c from dual
10 union all
11 select 2 n, 'ccc,rr,125' c from dual
12 union all
13 select 3 n, null c from dual),
14 t12 as (select 1 b, n, c from t1 union all select 2 b, n, c from t2),
15 t as (select b, n, c, nvl(length(regexp_replace(c, '[^,]+')) + 1, 1) cnt from t12),
16 tbrd as (select b, n, c, cnt, sum(cnt) over (order by b,n) brd from t),
17 allrec as (select level lv from dual connect by level <= (select sum(cnt) from t)),
18 step5 as (select r.b, r.n, r.c, regexp_substr(c, '[^,]+', 1, r.brd - a.lv + 1) s from tbrd r, allrec a
19 where r.brd - a.lv between 0 and r.cnt - 1),
20 step6 as (
21 select b, n, listagg('[' || s || ']', ',') within group (order by s) sorted
22 from step5
23 group by b, n)
24 select * from step6
25 /
B N SORTED
---------- ---------- --------------------------------------------------------------------------------
1 1 [a],[b],[c]
1 2 [ccc],[rr],[vvv]
1 3 []
2 1 [a],[b],[c]
2 2 [125],[ccc],[rr]
2 3 []
6 rows selected
7 步骤:结果
SQL> with
2 t1 as (
3 select 1 n, 'c,b,a' c from dual
4 union all
5 select 2 n, 'vvv,ccc,rr' c from dual
6 union all
7 select 3 n, null c from dual),
8 t2 as (
9 select 1 n, 'a,b,c' c from dual
10 union all
11 select 2 n, 'ccc,rr,125' c from dual
12 union all
13 select 3 n, null c from dual),
14 t12 as (select 1 b, n, c from t1 union all select 2 b, n, c from t2),
15 t as (select b, n, c, nvl(length(regexp_replace(c, '[^,]+')) + 1, 1) cnt from t12),
16 tbrd as (select b, n, c, cnt, sum(cnt) over (order by b,n) brd from t),
17 allrec as (select level lv from dual connect by level <= (select sum(cnt) from t)),
18 step5 as (select r.b, r.n, r.c, regexp_substr(c, '[^,]+', 1, r.brd - a.lv + 1) s from tbrd r, allrec a
19 where r.brd - a.lv between 0 and r.cnt - 1),
20 step6 as (
21 select b, n, listagg('[' || s || ']', ',') within group (order by s) sorted
22 from step5
23 group by b, n)
24 select t.*, decode(
25 (select 1 from step6 z
26 where z.b = 2 and z.sorted = t.sorted and rownum < 2), 1, 'true', 'false') bool
27 from step6 t where b = 1
28 /
B N SORTED BOOL
---------- ---------- -------------------------------------------------------------------------------- -----
1 1 [a],[b],[c] true
1 2 [ccc],[rr],[vvv] false
1 3 [] true
当然,使用函数要好得多,但是,我只是想找到一种仅使用 SQL 的方法。
(2017-06-05: 新增功能(
功能可以是:
create or replace function comparesepval(vArg1 in varchar2, vArg2 in varchar2) return number result_cache deterministic
as
type tpStore is table of number index by varchar2(32);
tStore1 tpStore;
tStore2 tpStore;
nCnt number;
vVal varchar2(32);
begin
if (vArg1 is null and vArg2 is null) or (vArg1 = vArg2) then
return 1;
end if;
if vArg1 is null then
return 0;
end if;
nCnt := nvl(length(regexp_replace(vArg1, '[^,]+')), 0) + 1;
if nCnt = 1 or nCnt <> nvl(length(regexp_replace(vArg2, '[^,]+')), 0) + 1 then -- vArg1 <> vArg2
return 0;
end if;
-- parse string
for i in 1..nCnt
loop
declare
procedure prcAgr(vArg in varchar2, tStore in out tpStore)
as
vVal varchar2(32) := nvl(regexp_substr(vArg, '(.*?)(,|$)', 1, i, null, 1), ','); -- ',' - for null
begin
tStore(vVal) := case when tStore.exists(vVal) then tStore(vVal) + 1 else 1 end;
end;
begin
prcAgr(vArg1, tStore1);
prcAgr(vArg2, tStore2);
end;
end loop;
if tStore1.count <> tStore2.count then
return 0;
end if;
vVal := tStore1.first;
loop
exit when vVal is null;
if not tStore2.exists(vVal) then
return 0;
end if;
if tStore2(vVal) <> tStore1(vVal) then
return 0;
end if;
vVal := tStore1.next(vVal);
end loop;
return 1;
end;
/
测试:
SQL> with tbl as (
2 select null c1, null c2 from dual union all
3 select 'a' c1, 'a' c2 from dual union all
4 select 'a' c1, 'b' c2 from dual union all
5 select 'a,a' c1, 'a,b' c2 from dual union all
6 select 'a,a' c1, 'a,b,a' c2 from dual union all
7 select 'a,a,b' c1, 'a,b,a' c2 from dual union all
8 select 'a,,b' c1, ',b,a' c2 from dual union all
9 select 'a,' c1, 'd,' c2 from dual
10 )
11 select comparesepval(c1, c2) c, c1, c2 from tbl;
C C1 C2
---------- ----- -----
1
1 a a
0 a b
0 a,a a,b
0 a,a a,b,a
1 a,a,b a,b,a
1 a,,b ,b,a
0 a, d,
8 rows selected