例如一个表A里的字段a是(1,2,3,4,5),另一个表B是
序号 姓名
1 张三
2 李四
3 王五
4 李伟
5 小明
6 姗姗
7 丽丽
8 佳佳
我在select时,想要让表A里的字段a筛选出的是(张三,李四,王五,李伟,小明),要怎么写SQL语句?
10 个解决方案
#1
select * from B where (select a from A where ...) like '%'||id||'%';
注: where ... 是确定从A表中取出唯一记录的条件语句 ,如 where a='1,2,3,4,5'
注: where ... 是确定从A表中取出唯一记录的条件语句 ,如 where a='1,2,3,4,5'
#2
select regexp_replace(regexp_replace(regexp_replace(regexp_replace(regexp_replace(regexp_replace(regexp_replace(regexp_replace(字段a, 1, '张三'), 2, '李四'), 3, '王五'), 4, '李伟'), 5, '小明'), 6, '姗姗'), 7, '丽丽', 8, '佳佳') as 字段a from 表A;
#3
是这个
select regexp_replace(regexp_replace(regexp_replace(regexp_replace(regexp_replace(regexp_replace(regexp_replace(regexp_replace(字段a, 1, '张三'), 2, '李四'), 3, '王五'), 4, '李伟'), 5, '小明'), 6, '姗姗'), 7, '丽丽'), 8, '佳佳') as 字段a from 表A;
#4
试试这个:
select B.*
from B
inner join A
on ',' || cast(A.序号 as varchar2(20)) ||',' like '%,' || A.a ||',%'
#5
1.字符串转行;
2.表关联;
3.行转字符串;
2.表关联;
3.行转字符串;
#6
我那个举例真的只是个例子,实际上,我要操作的表B有近两万条记录,而且A表的那个字段也绝对不只5个,请问有没有可以批量处理的方法?
#7
11g的话,这个应该可以
SQL> with A as ( select '(1,2,3,4,5)' a from dual),
2 B as ( select '1' no, '张三' name from dual
3 union all select '2' no, '李四' name from dual
4 union all select '3' no, '王五' name from dual
5 union all select '4' no, '李伟' name from dual
6 union all select '5' no, '小明' name from dual
7 union all select '6' no, '姗姗' name from dual
8 union all select '7' no, '丽丽' name from dual
9 union all select '8' no, '佳佳' name from dual
10 ),
11 C as ( select
12 instr( ','|| substr(A.a,2,length(A.a)-2) || ',' , ','|| B.no || ',') ind,
13 B.name
14 from A, B
15 where instr( ','|| substr(A.a,2,length(A.a)-2) || ',' , ','|| B.no || ',') > 0
16 )
17 select '('|| listagg(C.name,',') within group (order by C.ind) || ')' from C ;
'('||LISTAGG(C.NAME,',')WITHIN
--------------------------------------------------------------------------------
(张三,李四,王五,李伟,小明)
SQL>
#8
这个如果要批量处理该怎么办啊,那可是几千条记录啊
#9
select A.a,(select wm_concat(B.name) from B where A.a like '%'||B.name||'%') name_h from A;
#10
我帮楼主把逗号隔开数据变成了多行,再与楼主的表关联即可 ,代码如下,如果有上万行的话可能性能会慢:
with T as
(select 1 id, '张三,李四,王五' name from dual
union all
select 2, '赵六,邹七' from dual
union all
select 3, '周八' from dual)
SELECT id, REGEXP_SUBSTR(T.name, '[^,]+', 1, rn) as name
FROM T,(SELECT rownum rn FROM dual CONNECT BY rownum <= (
select LENGTH(name) - LENGTH(REPLACE(name, ','))+1 from (select wm_concat(name) name from T)
)) b
WHERE
LENGTH(name) - LENGTH(REPLACE(name, ','))+1 >= rn
with T as
(select 1 id, '张三,李四,王五' name from dual
union all
select 2, '赵六,邹七' from dual
union all
select 3, '周八' from dual)
SELECT id, REGEXP_SUBSTR(T.name, '[^,]+', 1, rn) as name
FROM T,(SELECT rownum rn FROM dual CONNECT BY rownum <= (
select LENGTH(name) - LENGTH(REPLACE(name, ','))+1 from (select wm_concat(name) name from T)
)) b
WHERE
LENGTH(name) - LENGTH(REPLACE(name, ','))+1 >= rn
#1
select * from B where (select a from A where ...) like '%'||id||'%';
注: where ... 是确定从A表中取出唯一记录的条件语句 ,如 where a='1,2,3,4,5'
注: where ... 是确定从A表中取出唯一记录的条件语句 ,如 where a='1,2,3,4,5'
#2
select regexp_replace(regexp_replace(regexp_replace(regexp_replace(regexp_replace(regexp_replace(regexp_replace(regexp_replace(字段a, 1, '张三'), 2, '李四'), 3, '王五'), 4, '李伟'), 5, '小明'), 6, '姗姗'), 7, '丽丽', 8, '佳佳') as 字段a from 表A;
#3
是这个
select regexp_replace(regexp_replace(regexp_replace(regexp_replace(regexp_replace(regexp_replace(regexp_replace(regexp_replace(字段a, 1, '张三'), 2, '李四'), 3, '王五'), 4, '李伟'), 5, '小明'), 6, '姗姗'), 7, '丽丽'), 8, '佳佳') as 字段a from 表A;
#4
试试这个:
select B.*
from B
inner join A
on ',' || cast(A.序号 as varchar2(20)) ||',' like '%,' || A.a ||',%'
#5
1.字符串转行;
2.表关联;
3.行转字符串;
2.表关联;
3.行转字符串;
#6
我那个举例真的只是个例子,实际上,我要操作的表B有近两万条记录,而且A表的那个字段也绝对不只5个,请问有没有可以批量处理的方法?
#7
11g的话,这个应该可以
SQL> with A as ( select '(1,2,3,4,5)' a from dual),
2 B as ( select '1' no, '张三' name from dual
3 union all select '2' no, '李四' name from dual
4 union all select '3' no, '王五' name from dual
5 union all select '4' no, '李伟' name from dual
6 union all select '5' no, '小明' name from dual
7 union all select '6' no, '姗姗' name from dual
8 union all select '7' no, '丽丽' name from dual
9 union all select '8' no, '佳佳' name from dual
10 ),
11 C as ( select
12 instr( ','|| substr(A.a,2,length(A.a)-2) || ',' , ','|| B.no || ',') ind,
13 B.name
14 from A, B
15 where instr( ','|| substr(A.a,2,length(A.a)-2) || ',' , ','|| B.no || ',') > 0
16 )
17 select '('|| listagg(C.name,',') within group (order by C.ind) || ')' from C ;
'('||LISTAGG(C.NAME,',')WITHIN
--------------------------------------------------------------------------------
(张三,李四,王五,李伟,小明)
SQL>
#8
这个如果要批量处理该怎么办啊,那可是几千条记录啊
#9
select A.a,(select wm_concat(B.name) from B where A.a like '%'||B.name||'%') name_h from A;
#10
我帮楼主把逗号隔开数据变成了多行,再与楼主的表关联即可 ,代码如下,如果有上万行的话可能性能会慢:
with T as
(select 1 id, '张三,李四,王五' name from dual
union all
select 2, '赵六,邹七' from dual
union all
select 3, '周八' from dual)
SELECT id, REGEXP_SUBSTR(T.name, '[^,]+', 1, rn) as name
FROM T,(SELECT rownum rn FROM dual CONNECT BY rownum <= (
select LENGTH(name) - LENGTH(REPLACE(name, ','))+1 from (select wm_concat(name) name from T)
)) b
WHERE
LENGTH(name) - LENGTH(REPLACE(name, ','))+1 >= rn
with T as
(select 1 id, '张三,李四,王五' name from dual
union all
select 2, '赵六,邹七' from dual
union all
select 3, '周八' from dual)
SELECT id, REGEXP_SUBSTR(T.name, '[^,]+', 1, rn) as name
FROM T,(SELECT rownum rn FROM dual CONNECT BY rownum <= (
select LENGTH(name) - LENGTH(REPLACE(name, ','))+1 from (select wm_concat(name) name from T)
)) b
WHERE
LENGTH(name) - LENGTH(REPLACE(name, ','))+1 >= rn