表如下:
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 11low_sign和high_sign代表符号.
已知 department_type(可能是d3)和account,如果department_type=d3,按照其他处理.
如果sql语句解决不了,存储过程也行.
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 11low_sign和high_sign代表符号.
已知 department_type(可能是d3)和account,如果department_type=d3,按照其他处理.
如果sql语句解决不了,存储过程也行.
这个应该可以静态处理: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
)
)
建议使用游标,成生动态sql语句。
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';大概这样吧,不知道有没有用