傅昌林：百万级数据挑战的大师，NineData编程大赛的卓越表现

;WITH Nums AS (
    SELECT CAST(value as INT) as val, value as str
    FROM STRING_SPLIT('1,2,3,4,5,6,7,8,9,10', ',')
), Ops AS (
    SELECT value as str
    FROM STRING_SPLIT('+,-,*,/', ',')
), ExprN2 AS (
    SELECT
        u.str + op.str + v.str AS str,
        CASE
            WHEN op.str = '+' THEN u.val + v.val
            WHEN op.str = '-' THEN u.val - v.val
            WHEN op.str = '*' THEN u.val * v.val
            WHEN op.str = '/' THEN u.val  v.val
        END AS val,
        u.val AS n1,
        v.val AS n2,
        op.str AS op1
    FROM Nums u
    LEFT JOIN Nums v ON 1 = 1
    LEFT JOIN Ops op ON 1 = 1
    WHERE (op.str = '/' AND v.val <> 0 AND u.val % v.val = 0) OR (op.str != '/')
), ExprN3 AS (
  SELECT
        IIF(op.str IN ('*', '/') AND u.op1 IN ('+', '-'), '(' + u.str + ')' + op.str + v.str, u.str + op.str + v.str) AS str,
        CASE
            WHEN op.str = '+' THEN u.val + v.val
            WHEN op.str = '-' THEN u.val - v.val
            WHEN op.str = '*' THEN u.val * v.val
            WHEN op.str = '/' THEN u.val / v.val
        END AS val,
        u.n1,
    u.n2,
        v.val AS n3,
    u.op1,
        op.str AS op2
    FROM ExprN2 u
    LEFT JOIN Nums v ON 1 = 1
    LEFT JOIN Ops op ON 1 = 1
    WHERE (op.str = '/' AND v.val <> 0 AND u.val % v.val = 0) OR (op.str != '/')
), ExprN4 AS (
  SELECT
        IIF(op.str IN ('*', '/') AND u.op2 IN ('+', '-'), '(' + u.str + ')' + op.str + v.str, u.str + op.str + v.str) AS str,
        CASE
            WHEN op.str = '+' THEN u.val + v.val
            WHEN op.str = '-' THEN u.val - v.val
            WHEN op.str = '*' THEN u.val * v.val
            WHEN op.str = '/' THEN u.val / v.val
        END AS val,
        u.n1,
    u.n2,
    u.n3,
        v.val AS n4
    FROM ExprN3 u
    LEFT JOIN Nums v ON 1 = 1
    LEFT JOIN Ops op ON 1 = 1
    WHERE (op.str = '/' AND v.val <> 0 AND u.val % v.val = 0) OR (op.str != '/')
), ExprN2N2 AS (
    SELECT
    '(' + u.str + ')' + op.str + '(' + v.str + ')' AS str,
        CASE
            WHEN op.str = '+' THEN u.val + v.val
            WHEN op.str = '-' THEN u.val - v.val
            WHEN op.str = '*' THEN u.val * v.val
            WHEN op.str = '/' THEN IIF(v.val = 0, 0, u.val / v.val)
        END AS val,
        u.n1,
    u.n2,
        v.n1 AS n3,
        v.n2 AS n4
    FROM ExprN2 u
    LEFT JOIN ExprN2 v ON 1 = 1
    LEFT JOIN Ops op ON 1 = 1
    WHERE (op.str = '/' AND IIF(v.val = 0, -1, u.val % v.val) = 0) OR (op.str != '/')
), ExprSpecial AS (
  -- Case 1: a * (b/c + d) = ab/c + ad
  SELECT
    a.str + '*(' + b.str + '/' + c.str + '+' + d.str + ')' AS str,
    24 AS val,
    a.val AS n1,
    b.val AS n2,
    c.val AS n3,
    d.val AS n4
  FROM Nums a
  LEFT JOIN Nums b ON 1 = 1
  LEFT JOIN Nums c ON 1 = 1
  LEFT JOIN Nums d ON 1 = 1
  WHERE a.val * b.val % c.val = 0 AND a.val * b.val / c.val + a.val * d.val = 24
  -- Case 2: a * (b/c - d) = ab/c - ad
  UNION
  SELECT
    a.str + '*(' + b.str + '/' + c.str + '+' + d.str + ')' AS str,
    24 AS val,
    a.val AS n1,
    b.val AS n2,
    c.val AS n3,
    d.val AS n4
  FROM Nums a
  LEFT JOIN Nums b ON 1 = 1
  LEFT JOIN Nums c ON 1 = 1
  LEFT JOIN Nums d ON 1 = 1
  WHERE a.val * b.val % c.val = 0 AND a.val * b.val / c.val - a.val * d.val = 24
  -- Case 3: a * (b - c/d) = ab - ac/d
  UNION
  SELECT
    a.str + '*(' + b.str + '-' + c.str + '/' + d.str + ')' AS str,
    24 AS val,
    a.val AS n1,
    b.val AS n2,
    c.val AS n3,
    d.val AS n4
  FROM Nums a
  LEFT JOIN Nums b ON 1 = 1
  LEFT JOIN Nums c ON 1 = 1
  LEFT JOIN Nums d ON 1 = 1
  WHERE a.val * c.val % d.val = 0 AND a.val *  b.val - a.val * c.val / d.val = 24
  -- Case 4: a / (b/c + d) = ac / (b + cd)
  UNION
  SELECT
    a.str + '/(' + b.str + '/' + c.str + '+' + d.str + ')' AS str,
    24 AS val,
    a.val AS n1,
    b.val AS n2,
    c.val AS n3,
    d.val AS n4
  FROM Nums a
  LEFT JOIN Nums b ON 1 = 1
  LEFT JOIN Nums c ON 1 = 1
  LEFT JOIN Nums d ON 1 = 1
  WHERE (b.val + c.val * d.val) <> 0 AND a.val * c.val % (b.val + c.val * d.val) = 0 AND a.val *  c.val / (b.val + c.val * d.val) = 24
  -- Case 5: a / (b/c - d) = ac / (b - cd)
  UNION
  SELECT
    a.str + '/(' + b.str + '/' + c.str + '-' + d.str + ')' AS str,
    24 AS val,
    a.val AS n1,
    b.val AS n2,
    c.val AS n3,
    d.val AS n4
  FROM Nums a
  LEFT JOIN Nums b ON 1 = 1
  LEFT JOIN Nums c ON 1 = 1
  LEFT JOIN Nums d ON 1 = 1
  WHERE (b.val - c.val * d.val) <> 0 AND a.val * c.val % (b.val - c.val * d.val) = 0 AND a.val *  c.val / (b.val - c.val * d.val) = 24
  -- Case 6: a / (b - c/d) = ad / (bd - c)
  UNION
  SELECT
    a.str + '/(' + b.str + '-' + c.str + '/' + d.str + ')' AS str,
    24 AS val,
    a.val AS n1,
    b.val AS n2,
    c.val AS n3,
    d.val AS n4
  FROM Nums a
  LEFT JOIN Nums b ON 1 = 1
  LEFT JOIN Nums c ON 1 = 1
  LEFT JOIN Nums d ON 1 = 1
  WHERE (b.val * d.val - c.val) <> 0 AND a.val * d.val % (b.val * d.val - c.val) = 0 AND a.val *  d.val / (b.val * d.val - c.val) = 24
), ExprMerged AS (
  SELECT *
  FROM ExprN4
  WHERE val = 24
  UNION
  SELECT *
  FROM ExprN2N2
  WHERE val = 24
  UNION
  SELECT *
  FROM ExprSpecial
), ExprWithTag AS (
  SELECT S.str AS result, STRING_AGG(num, ',') WITHIN GROUP (ORDER BY num) as tag
  FROM (SELECT str, col, num FROM ExprMerged UNPIVOT (num for col IN (n1, n2, n3, n4)) unpvt) S
  GROUP BY S.str 
), ResultWithRk AS (
  SELECT tag, result, ROW_NUMBER() OVER(PARTITION BY tag ORDER BY result) as rk
  FROM ExprWithTag
), ResultLookup AS (
  SELECT tag, result
  FROM ResultWithRk
  WHERE Rk = 1
)
SELECT Src.id, Src.c1, Src.c2, Src.c3, Src.c4, Ans.result
FROM poker24.cards Src
JOIN (
  SELECT S.id, STRING_AGG(num, ',') WITHIN GROUP (ORDER BY num) as tag
  FROM (SELECT id, col, num FROM poker24.cards UNPIVOT (num for col IN (c1, c2, c3, c4)) unpvt) S
  GROUP BY S.id 
) IdTag
  ON Src.id = IdTag.id
LEFT JOIN ResultLookup Ans
  ON IdTag.tag = Ans.tag
ORDER BY Src.id