100分求一sql语句

[meetyou930]

表如下:
id department_type low_account low_sign high_account high_sign persons operator_type
1 d1 0 =< 5 <= p1 10
2 d1 50 =< 100 <= p4 12
3 d2 5 < 50 < p2 12
4 其他 0 =< 300 < p3 11

low_sign和high_sign代表符号.
已知 department_type(可能是d3)和account,如果department_type=d3,按照其他处理.
如果sql语句解决不了,存储过程也行.

[fjmingyang]

都不知道楼主想要什么样的结果 ， 语句怎么写

[meetyou930]

有人帮我写了ms-sql的,谁能该成oracle的:
这个应该可以静态处理：

if not exists (
select 1 from tablename
where department_type =@department_type
)
select * from tablename
where department_type ='其他'
and (
(low_sign='<'
and low_account<@account
) or
(low_sign='<='
and low_account<=@account
)
)
and (
(high_sign='>'
and high_account>@account
) or
(high_sign='>='
and high_account<=@account
)
)
else
select * from tablename
where department_type =@department_type
and (
(low_sign='<'
and low_account<@account
) or
(low_sign='<='
and low_account<=@account
)
)
and (
(high_sign='>'
and high_account>@account
) or
(high_sign='>='
and high_account<=@account
)
)

[meetyou930]

对了,最好写成函数,我需要返回persons和operator_type,这两个字段的值.

[shengping20000]

我也不知道楼主到底想要什么结果。随便改了一下，试了一下F_GET_VAL('D3',2,'PERSONS')，还是行的通的。


--已知 department_type(可能是d3)和account,如果department_type=d3,按照其他处理
--返回persons和operator_type
create or replace function f_get_val
(
pi_department_type varchar2,
pi_account number,
pi_type_str varchar2 --类别persons和operator_type，判断返回值是persons或operator_type
)
return varchar2 is
v_str varchar2(20); --返回结果
v_i number(6);
v_j number(6);
begin
v_str := '';
select count(*) into v_j
from tablename
where department_type=pi_department_type;
dbms_output.put_line('v_j='||v_j||' pi_department_type='||pi_department_type||' pi_type_str='||pi_type_str);
--取得persons||'|'||operator_type,无符合条件的记录时返回''
if v_j=0
then
dbms_output.put_line('department_type不存在');
select persons||'|'||operator_type into v_str
from tablename
where department_type='其他'
and (
(low_sign='<'
and low_account<pi_account
)
or
(low_sign='=<'
and low_account<=pi_account
)
)
and (
(high_sign='<'
and high_account>pi_account
)
or
(high_sign='<='
and high_account<=pi_account
)
);
else
dbms_output.put_line('department_type存在');
select persons||'|'||operator_type into v_str
from tablename
where department_type=pi_department_type
and (
(low_sign='<'
and low_account<pi_account
)
or
(low_sign='=<'
and low_account<=pi_account
)
)
and (
(high_sign='<'
and high_account>pi_account
)
or
(high_sign='<='
and high_account<=pi_account
)
);
end if;
dbms_output.put_line('得到v_str:'||v_str);
v_i := instr(v_str,'|');
if pi_type_str='persons' then
v_str := substr(v_str,0,v_i-1);
elsif pi_type_str='operator_type' then
v_str := substr(v_str,v_i+1,length(v_str));
else
v_str := '';
end if;
dbms_output.put_line('返回v_str:'||v_str);
return v_str;
exception
when others then
dbms_output.put_line('error------');
return '';
end f_get_val;
/
show error;

[fjmingyang]

....

[zxhcloth]

请问楼主你要什么结果!
说了半天,我真不知道你要什么!!当然就没发帮你了

Thanks
Hima

[铁歌]

up

[issgates]

肯定用不着这么麻烦！！！！！！！！！！！！！！

存储过程搞这么多，日后谁来维护？