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