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语句解决不了,存储过程也行.

[qiaozhiwei]

我的理解是你的表记录表示的是一个范围,你知道了account,要得到一个范围,但是你的表数据好像不是很清楚,请把问题表述清楚一点

[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,这两个字段的值.

[eagle4510]

怎么这么设计这个表啊 “=<” 用“<=”不行吗？
建议使用游标，成生动态sql语句。

[sunnyrain]

vi_department_type
vi_account


for v_record in (select * from table where department_type<>'其他') loop
v_sql:=v_sql ||' select * from table where '||v_record.department_type||'='''||vi_department_type||''' and low_account '||v_record.low_sign||' vi_account and vi_account '||v_record.high_sign||' high_account union ';
end loop;

v_sql:=v_sql||' select * from table where department_type='''其他''' and not exists (select * from table where department_type ='''||vi_department_type||''') and 0<='||vi_account||' and '||vi_account||'<=300';



大概这样吧，不知道有没有用