[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再改进我就不做了。

来源链接：https://www.cnblogs.com/lfree/p/18903148