假如A表有 A,B字段 ,B表的尾C,D字段
A表的字段都有值, B表C字段有值,现在想根据B表的C字段与A表的A字段关联,填充D字段,结果如下所示:
A B C D
------- -------------
1 a 1,2 a,b
2 b 1,4 a,d
3 c 3 c
4 d 2,1 b,a
该怎么写语句呢?最好是正则表达式写法,。。不知道怎么写求指教
A表的字段都有值, B表C字段有值,现在想根据B表的C字段与A表的A字段关联,填充D字段,结果如下所示:
A B C D
------- -------------
1 a 1,2 a,b
2 b 1,4 a,d
3 c 3 c
4 d 2,1 b,a
该怎么写语句呢?最好是正则表达式写法,。。不知道怎么写求指教
Resulta varchar2(200);
V_STR varchar2(200);
v_sql varchar2(2000);
begin
V_STR:=''''||replace(V_ID,',',''',''')||'''';
--DBMS_OUTPUT.put_line(V_STR);
v_sql:='select wm_concat(to_char(b))
from tb_a
where event in ('||V_STR||')';
--DBMS_OUTPUT.put_line(v_sql);
execute immediate v_sql into Resulta;
return Resulta;
end;--更新B表
update tb_b set d=fun_get(c);
CREATE TABLE t1(a int, b varchar2(20));
CREATE TABLE t2(c varchar2(100), d varchar2(100));INSERT INTO t1(a,b) values(1,'a');
INSERT INTO t1(a,b) values(2,'b');
INSERT INTO t1(a,b) values(3,'d');
INSERT INTO t1(a,b) values(4,'e');
INSERT INTO t1(a,b) values(5,'ef');
INSERT INTO t1(a,b) values(6,'bd');
INSERT INTO t1(a,b) values(7,'nb');
INSERT INTO t1(a,b) values(8,'sb');
INSERT INTO t1(a,b) values(9,'md');
INSERT INTO t1(a,b) values(10,'cnm');
INSERT INTO t1(a,b) values(11,'nmb');
INSERT INTO t1(a,b) values(12,'wcnm');commit;INSERT INTO t2(C) values('1,2');
INSERT INTO t2(C) values('4,11');
INSERT INTO t2(C) values('1,5,10,12');
INSERT INTO t2(C) values('2,1');commit;
With a AS (SELECT t2.c,
wm_concat(t1.b) over( partition by t2.c order by instr(','||t2.c||',', ','||t1.a||',')) as b
FROM t2 left join t1 on instr(','||t2.c||',', ','||t1.a||',')>0 )
select a1.c, a1.b
from a a1
where exists (select ''
from a a2
where a2.c = a2.c
group by a2.c
having max(length(a2.b))=length(a1.b) );C B
--------------- --------------------------------------------------------------------------------
1,2 a,b
1,5,10,12 a,ef,cnm,wcnm
2,1 b,a
4,11 e,nmb已选择4行。
With a AS (SELECT t2.c,
wm_concat(t1.b) over( partition by t2.c order by instr(','||t2.c||',', ','||t1.a||',')) as d
FROM t2 left join t1 on instr(','||t2.c||',', ','||t1.a||',')>0 )
SELECT a1.c, a1.d
FROM a a1
WHERE EXISTS(SELECT ''
FROM a a2
WHERE a2.c = a2.c
GROUP BY a2.c
HAVING MAX(LENGTH(a2.d))=LENGTH(a1.d) );UPDATE t2 b1
SET b1.d=(SELECT b2.d
FROM v_t1t2 b2
WHERE b2.c=b1.c);
set b.d =
(select t.wm
from (select b.c,
wm_concat(a.b) over(partition by b.c order by instr(b.c, a.a)) wm,
length(wm_concat(a.b)
over(partition by b.c order by instr(b.c, a.a))) len
from a, b
where instr(b.c, a.a) > 0
order by len desc) t
where t.c = b.c
and rownum = 1)没有4楼的逻辑 但是勉强有哪个效果
可以,试过了,wm_concat应该是也是聚合函数吧