Oracle将框的大小与引用进行比较

Question

我有一张桌子，里面有盒子的尺寸，长度，宽度和高度。我想把这个盒子和一个参考框进行比较，看看这个盒子是否比参考箱大，并输出给每个盒子，看看这个盒子是否大。

假设长度、宽度和高度分别为4、6和2。在这种情况下，由于(2,4,6) < ( 3,5 , 8 )，方框比基准小。

因此，基本上，我必须比较有序盒维数和有序给定维数。有没有办法用甲骨文做这件事？

我已经遇到了最大和最小的函数，但是我不能用这些方法来比较中间维。

Answer 1

你可以这样做：

CREATE OR REPLACE TYPE NUMBER_TABLE_TYPE AS TABLE OF NUMBER;


DECLARE
    refBox NUMBER_TABLE_TYPE := NUMBER_TABLE_TYPE(4,6,2);
    inBox NUMBER_TABLE_TYPE := NUMBER_TABLE_TYPE(3,5,8);

    sortedRefBox NUMBER_TABLE_TYPE;
    sortedInBox NUMBER_TABLE_TYPE;

    fitting BOOLEAN := TRUE;

BEGIN

    IF refBox.count <> 3 OR inBox.count <> 3 THEN
        RAISE VALUE_ERROR;      
    END IF;

    SELECT COLUMN_VALUE 
    BULK COLLECT INTO sortedRefBox 
    FROM TABLE(refBox)
    ORDER BY COLUMN_VALUE;

    SELECT COLUMN_VALUE 
    BULK COLLECT INTO sortedinBox
    FROM TABLE(inBox)
    ORDER BY COLUMN_VALUE;

    FOR i IN 1..3 LOOP
        fitting := fitting AND sortedinBox(i) <= sortedRefBox(i);
    END LOOP;

    IF fitting THEN
        DBMS_OUTPUT.PUT_LINE('InBox fits in Reference Box' );
    else
        DBMS_OUTPUT.PUT_LINE('InBox does not fit in Reference Box' );   
    end if;

END;    

Answer 2

中间值从三对中取得最少：

-- sample data
with boxes(length, width, height) as (
  select 4, 6, 2 from dual union all
  select 2, 6, 4 from dual union all
  select 1, 3, 2 from dual union all
  select 9, 7, 8 from dual )
-- 
select length, width, height,
       case when least(least(length, width), height)           < 3
                 and least(greatest(length, width), 
                           greatest(length, height), 
                           greatest(width, height))            < 5
                 and greatest(greatest(length, width), height) < 8
           then 'smaller than reference'
           else 'bigger or equal'
       end comparison
  from boxes

结果：

    LENGTH      WIDTH     HEIGHT COMPARISON
---------- ---------- ---------- ----------------------
         4          6          2 smaller than reference
         2          6          4 smaller than reference
         1          3          2 smaller than reference
         9          7          8 bigger or equal

Answer 3

最后，我使用了最大和最小的函数，如下所示。

GREATEST(BOX_LENGTH, BOX_HEIGHT, BOX_WIDTH) > GREATEST(REF_LENGTH, REF_HEIGHT, REF_WIDTH) OR 

LEAST(BOX_LENGTH, BOX_HEIGHT, BOX_WIDTH) > LEAST(REF_LENGTH, REF_HEIGHT, REF_WIDTH) OR 

BOX_LENGTH + BOX_HEIGHT + BOX_WIDTH - GREATEST(BOX_LENGTH, BOX_HEIGHT, BOX_WIDTH) - LEAST(BOX_LENGTH, BOX_HEIGHT, BOX_WIDTH) > REF_LENGTH + REF_HEIGHT + REF_WIDTH - GREATEST(REF_LENGTH, REF_HEIGHT, REF_WIDTH) - LEAST(REF_LENGTH, REF_HEIGHT, REF_WIDTH) 

那是

(Sum of dimensions) - Greatest - Least = Middle dimension