TableA
create table A
(
ID NUMBER(13) ID是主键
STATE NUMBER(2)
ADDRESS VARCHAR2(10)
NAME VARCHAR2(80)
)假设有6条记录分别如下
1 2 'test' ‘123’
2 2 'test' ‘123124’
3 2 'test' ‘12123412343’
4 2 'test' ‘12234123’
5 2 'test' ‘121232341233’
6 2 'test' ‘12werqwer3’
假设我要查询满足ID条件的一些数,如果存在就把结果都取出来,如果不存在,就把State设置为0, ADDRESS, Name的值都是空的
例如我要查询 ID在 1,2,4,11,12的记录,希望列出5个记录如下
1 2 'test' ‘123’
2 2 'test' ‘123124’
4 2 'test' ‘12234123’
11 0 '' ''
12 0 '' ''
create table A
(
ID NUMBER(13) ID是主键
STATE NUMBER(2)
ADDRESS VARCHAR2(10)
NAME VARCHAR2(80)
)假设有6条记录分别如下
1 2 'test' ‘123’
2 2 'test' ‘123124’
3 2 'test' ‘12123412343’
4 2 'test' ‘12234123’
5 2 'test' ‘121232341233’
6 2 'test' ‘12werqwer3’
假设我要查询满足ID条件的一些数,如果存在就把结果都取出来,如果不存在,就把State设置为0, ADDRESS, Name的值都是空的
例如我要查询 ID在 1,2,4,11,12的记录,希望列出5个记录如下
1 2 'test' ‘123’
2 2 'test' ‘123124’
4 2 'test' ‘12234123’
11 0 '' ''
12 0 '' ''
(SELECT '1,2,4,11,12' str FROM dual)
SELECT b.id, nvl(a.state, 0), a.address, a.name
FROM a,
(SELECT regexp_substr(str, '[^,]+', 1, LEVEL) ID
FROM t
CONNECT BY LEVEL <= length(str) - length(REPLACE(str, ',')) + 1) b
WHERE a.id(+) = b.id;
(SELECT '1,2,4,11,12' str FROM dual)
SELECT b.id, nvl(a.state, 0), a.address, a.name
FROM a,
(SELECT regexp_substr(str, '[^,]+', 1, LEVEL) ID
FROM t
CONNECT BY LEVEL <= length(str) - length(REPLACE(str, ',')) + 1) b
WHERE a.id(+) = b.id;最后的 b.id 的b无效标示符啊