[20250528]24点计算的SQL版本.txt

[20250528]24点计算的SQL版本.txt

--//有一张表 cards，id 是自增字段的数字主键，另外有4个字段 c1,c2,c3,c4 ，每个字段随机从 1~10 之间选择一个整数，要求选手
--//使用一条 SQL 给出 24 点的计算公式.

--//单独写出一条sql语句有点难度，我先尝试使用sqlplus+ bash shell的方式实现看看，计算使用dc或者bc。
--//实际上sqlplus一般采用穷举方法计算，蛮力计算结果是否等于24，使用sqlplus，要根据运算符号计算2个数字的计算结果，每次运
--//算都是如此，处理难度集中在哪里，而且题目并没有除法时必须整除，另外运算除法时分母是0该如何处理。我简单一点，直接使用
--//sql语句写出运算的排列组合，然后交给XMLQUERY计算,我并没有建立表，先尝试直接带入4个数字计算。

$ cat 24dot.sql
WITH
FUNCTION js_exp (vc IN VARCHAR2)
        RETURN NUMBER
     AS
        n   NUMBER;
     BEGIN
        SELECT TO_NUMBER (XMLQUERY (vc RETURNING CONTENT)) INTO n FROM DUAL;

        RETURN n;
     EXCEPTION
        WHEN OTHERS
        THEN
           RETURN NULL;
     END;
x1
        AS (SELECT 1 id, ' +' c FROM DUAL
            UNION ALL
            SELECT 8 id, ' -' FROM DUAL
            UNION ALL
            SELECT 1.2 id, ' *' FROM DUAL
            UNION ALL
            SELECT 16 id, ' /' FROM DUAL)
    ,x2
        AS (  SELECT decode(x1.c||x2.c||x3.c,
' - + *',-100,
' + + +',-90,
' * + +',-80,
' * - *',-70,
' + * -',-60,
' - * +',-50,
' + - *',-40,
' + * +',-30,
x1.id + x2.id + x3.id) idop
                    ,x1.c p1
                    ,x2.c p2
                    ,x3.c p3
                FROM x1, x1 x2, x1 x3
            ORDER BY 1 )
    ,y1
        AS (SELECT 1 id, to_char(&&1,'999') n FROM DUAL
            UNION ALL
            SELECT 2 id, to_char(&&2,'999') FROM DUAL
            UNION ALL
            SELECT 3 id, to_char(&&3,'999') FROM DUAL
            UNION ALL
            SELECT 4 id, to_char(&&4,'999') FROM DUAL)
    ,y2
        AS (  SELECT DISTINCT x1.n n1
                             ,x2.n n2
                             ,x3.n n3
                             ,x4.n n4
                FROM y1 x1
                    ,y1 x2
                    ,y1 x3
                    ,y1 x4
               WHERE     x1.id <> x2.id
                     AND x1.id <> x3.id
                     AND x1.id <> x4.id
                     AND x2.id <> x3.id
                     AND x2.id <> x4.id
                     AND x3.id <> x4.id
            ORDER BY 1 ,2 ,3 ,4)
select txt,replace(dc1,'  ','') dc,replace(bc2,' ','') bc  from (
SELECT 'method 1' txt, '20k '||n1||n2||p1||n3||p2||n4||p3 dc1,'(('||n1||p1||n2||')'||p2||n3||')'||p3||n4 bc2 FROM y2, x2
union all
SELECT 'method 2' txt, '20k '||n1||n2||p1||n3||n4||p2||p3 dc1,'('||n1||p1||n2||')'||p3||'('||n3||p2||n4||')' bc2 FROM y2, x2
union all
SELECT 'method 3' txt, '20k '||n1||n2||n3||p1||p2||n4||p3 dc1,'('||n1||p2||'('||n2||p1||n3||'))'||p3||n4 bc2 FROM y2, x2
union all
SELECT 'method 4' txt, '20k '||n1||n2||n3||n4||p1||p2||p3 dc1, n1||p3||'('||n2||p2||'('||n3||p1||n4||'))' bc2 FROM y2, x2
union all
SELECT 'method 5' txt, '20k '||n1||n2||n3||p1||n4||p2||p3 dc1, n1||p3||'(('||n2||p1||n3||')'||p2||n4||')' bc2 FROM y2, x2
--) where TO_NUMBER(XMLQUERY(replace(bc2,'/','div') RETURNING CONTENT))=24 and rownum=1
--) where js_express(replace(bc2,'/','div') )=24 and rownum=1
--) where js_express(replace(bc2,'/','div') )=24
) where js_exp(replace(bc2,'/','div') )=24 and rownum=1
--) where js_exp(replace(bc2,'/','div') )=24
;
/

--//直接使用XMLQUERY有一个问题，遇到除法运算分母为0的情况会报错。

SCOTT@book01p> @ 24dot 1 2 3 4
TXT      DC                         BC
-------- -------------------------- --------------------------
method 1 20k1 2 * 4 * 3 *           ((1*2)*4)*3

SCOTT@book01p> @ 24dot 1 5 5 5
WITH x1
*
ERROR at line 1:
ORA-01476: divisor is equal to zero.

--//采用建立函数的方法绕过这个问题。
CREATE OR REPLACE FUNCTION js_express (vc IN VARCHAR2)
   RETURN NUMBER
AS
   n   NUMBER;
BEGIN
   SELECT TO_NUMBER (XMLQUERY (vc RETURNING CONTENT)) INTO n FROM DUAL;

   RETURN n;
EXCEPTION
   WHEN OTHERS
   THEN
      RETURN null;
END;
/

SCOTT@book01p> @ 24dot 1 5 5 5
TXT      DC                         BC
-------- -------------------------- --------------------------
method 3 20k5 1 5 / - 5 *           (5-(1/5))*5

--//12c支持sql自带函数，很容易改写实现，上面的代码已经采用自带函数的方式。
--//至于建立表cards再改进我就不做了。