select.....
where column1 in
(case when column2='1' then '1'
when column2='2' then '2'
when column2='3' then '3,5,7,9' END)
上面的SQL语句,当column2='1'时 则column1 in('1'),
cloumn2='2'时column1 in('2')
这两句没有问题,当column2='3'时,我希望column1 in ('3','5','7','9'),可是我写的把'3,5,7,9'当成一个字符串,
麻烦那位高手帮我改一下,或者有什么更好的方案
where column1 in
(case when column2='1' then '1'
when column2='2' then '2'
when column2='3' then '3,5,7,9' END)
上面的SQL语句,当column2='1'时 则column1 in('1'),
cloumn2='2'时column1 in('2')
这两句没有问题,当column2='3'时,我希望column1 in ('3','5','7','9'),可是我写的把'3,5,7,9'当成一个字符串,
麻烦那位高手帮我改一下,或者有什么更好的方案
or column2='2' and column1='2'
or column3='3' and column1 in ('3','5','7','9')
select.....
where column2= decode(column1,'1','1','2','2','3','3','5','3','7','3','9','3')
试试上面wildwave给的两种写法
应该可以实现你的目的的
SQL> create or replace function replace_override_fnc(srcstr varchar2,
2 oldstr varchar2,
3 newstr varchar2,
4 startpos number,
5 repnum number default null)
6 return varchar2 as
7 occur_cnt number;
8 repl_num number;
9 oldstr_occur_pos number;
10 new_srcstr varchar2(2000);
11 stop_pos number;
12 final_srcstr varchar2(2000);
13 resstr_temp varchar2(2000);
14 head_srcstr varchar2(2000);
15 tail_srcstr varchar2(2000);
16 result_str varchar2(2000);
17 begin
18 select instr(srcstr, oldstr, 1, startpos) into oldstr_occur_pos from dual;
19
20 select substr(srcstr, oldstr_occur_pos) into new_srcstr from dual;
21
22 select (length(new_srcstr) - length(replace(new_srcstr, oldstr))) /length(oldstr) into occur_cnt from dual;
23
24 if repnum is null then
25 repl_num := occur_cnt;
26 else
27 if occur_cnt < repnum then
28 repl_num := occur_cnt;
29 else
30 repl_num := repnum;
31 end if;
32 end if;
33
34 select substr(srcstr, 1, oldstr_occur_pos - 1) into head_srcstr from dual;
35
36 select instr(new_srcstr, oldstr, 1, repl_num + 1) into stop_pos from dual;
37
38 if stop_pos <> 0 then
39 select substr(new_srcstr, 1, stop_pos - 1) into final_srcstr from dual;
40 select substr(new_srcstr, stop_pos) into tail_srcstr from dual;
41 else
42 select substr(new_srcstr, 1) into final_srcstr from dual;
43 tail_srcstr := '';
44 end if;
45
46 select replace(final_srcstr, oldstr, newstr) into resstr_temp from dual;
47
48 select head_srcstr || resstr_temp || tail_srcstr into result_str from dual;
49
50 return result_str;
51 end;
52 /函数已创建。SQL> select replace_override_fnc('3,5,7,9',',',''',''',1) from dual;REPLACE_OVERRIDE_FNC('3,5,7,9',',',''',''',1)
--------------------------------------------------------------------------------
3','5','7','9SQL> select decode('3','1','1','2','2','3','''||replace_override_fnc('3,5,7,9',',',''',''',1)||''') from dual;
select decode('3','1','1','2','2','3','''||replace_override_fnc('3,5,7,9',',',''',''',1)||''') from dual
*
ERROR 位于第 1 行:
ORA-00907: 缺失右括号
SQL> select decode('3','1','1','2','2','3',''||replace_override_fnc('3,5,7,9',',',''',''',1)||'') from dual;DECODE('3','1','1','2','2','3',''||REPLACE_OVERRIDE_FNC('3,5,7,9',',',''',''',1)
--------------------------------------------------------------------------------
3','5','7','9SQL> select decode('3','1','1','2','2','3',''''||replace_override_fnc('3,5,7,9',',',''',''',1)||'''') from dual;DECODE('3','1','1','2','2','3',''''||REPLACE_OVERRIDE_FNC('3,5,7,9',',',''',''',
--------------------------------------------------------------------------------
'3','5','7','9'SQL>
select.....
where column1 in
(case when column2='1' then '1'
when column2='2' then '2'
when column2='3' then '3' or '5','7','9' END)
WHERE column2 = decode(column1,
'1',
'1',
'2',
'2',
'3',
'3',
'5',
'3',
'7',
'3',
'9',
'3');
select.....
where (column2,column1) in
(('1','1'),('2','2'),('3','3'),('3','5'),('3','7'),('3','9'))
可万一楼主就是想用CASE WHEN 那就小尴尬了
where column2 in
(case column when '1' then '1'
when '2' then '2'
when '3,5,7,9' then '3' END)
where column2 in
(case column1 when '1' then '1'
when '2' then '2'
when '3,5,7,9' then '3' END)
where column2 =
(case column1 when '1' then '1'
when '2' then '2'
when '3,5,7,9' then '3' END)
where column2= decode(column1,'1','1','2','2','3','3','5','3','7','3','9','3')