mysql - 在多列中查找值的范围

Question

在任何 SQL 格式中，是否有一种方法允许在多个列中查找一系列值。

我的解决方案的常规做法是:

where 
      cost_price between my_budget_price and my_affordable_price
  or
      max_retail_price between my_budget_price and my_affordable_price

  or
      offer_price between my_budget_price and my_affordable_price

这里也是between ... values子句在多个列上重复。我不想重复它。

我正在寻找这样的东西:

1. where my_price in between ( cost_price, max_retail_price,
offer_price )
2. where ( cost_price, max_retail_price, offer_price )<br/>
between my_budget_price and my_affordable_price

Answer 1

在 Oracle 中，您可以编写用户定义的函数并使用输入参数执行您想要的任何逻辑。

即。

and 1 = my_udf(param1, param2, param3, param4)

然后在udf中做任何你想做的

 function my_udf(a varchar2, b varchar2, c varchar2, d varchar2) return number is
 begin
   if (any logic you want here) then
      return 1;
   else 
      return 2;
   end if;
 end;

或者更具体地说:

select * 
  from some_table
 where 1 = my_udf(cost_price, max_retail_price, offer_price, 
                  my_budget_price, my_affordable_price)

这个函数有时会像这样:

function my_udf(c number, r number, o number, l number, m number) return number is
begin
   if (c >= l and c <= m) or
      (r >= l and c <= m) or
      (o >= l and c <= m) then
      return 1;
   else
      return 2;
   end if; 
end;

这会做同样的事情:

select * 
  from some_table
 where (
        (cost_price       >= my_budget_price and cost_price       <= my_affordable_price)   
    or  (max_retail_price >= my_budget_price and max_retail_price <= my_affordable_price)
    or  (offer_price      >= my_budget_price and offer_price      <= my_affordable_price)
      )