select max(case when t01=substr(&val,1,1) then t02 end) ||'-'||max(case when t01=substr(&val,1,2) then t02 end) ||'-'||max(case when t01=&val then t02 end) col from test
select * from tab t where ? like '%'||t.id||'%'; ? 是您传入的参数id,这样试试 看行不,呵呵
刚写错了 select * from tab where instr('124',id)>0 lz怎么写的啊,共享一下
select * from tab where substr('124',length(id)+1)=(select replace('124',id) from dual) union all select * from tab where id='124' 貌似这样也可以
公司要求搞得这个东西,写了个函数,具体是这样的: CREATE OR REPLACE FUNCTION F_GETNAME(A_ID IN NUMBER) RETURN VARCHAR2 IS
V_NAME VARCHAR2(100) := ''; --名称 V_TLEN NUMBER :=1; --临时计数 V_TNAME VARCHAR2(100) :=''; --临时名BEGIN for V_TLEN in 1..length(A_ID) loop begin select t.name into V_TNAME from tem t where t.id=substr(A_ID,1,V_TLEN); V_NAME := V_NAME || V_TNAME || '-'; EXCEPTION when NO_DATA_FOUND then V_TNAME :=''; end; end loop; RETURN substr(V_NAME,1,length(V_NAME)-1); END F_GETNAME;晕死,当时没有想到,这样一个表里查询东西都要这么复杂的控制-_-!
||'-'||max(case when t01=substr(&val,1,2) then t02 end)
||'-'||max(case when t01=&val then t02 end) col
from test
? 是您传入的参数id,这样试试 看行不,呵呵
select * from tab where instr('124',id)>0
lz怎么写的啊,共享一下
union all
select * from tab where id='124'
貌似这样也可以
CREATE OR REPLACE FUNCTION F_GETNAME(A_ID IN NUMBER)
RETURN VARCHAR2 IS
V_NAME VARCHAR2(100) := ''; --名称
V_TLEN NUMBER :=1; --临时计数
V_TNAME VARCHAR2(100) :=''; --临时名BEGIN
for V_TLEN in 1..length(A_ID)
loop
begin
select t.name
into V_TNAME
from tem t where t.id=substr(A_ID,1,V_TLEN);
V_NAME := V_NAME || V_TNAME || '-';
EXCEPTION
when NO_DATA_FOUND then
V_TNAME :='';
end;
end loop;
RETURN substr(V_NAME,1,length(V_NAME)-1);
END F_GETNAME;晕死,当时没有想到,这样一个表里查询东西都要这么复杂的控制-_-!