2025 NineData 第三届数据库编程大赛圆满举办！本次大赛由 NineData 和云数据库技术社区主办，并联合佰晟智算、达梦数据、 ITPUB、CSDN、IFclub、开源中国、DataFun、墨天轮等技术社区共同举办。本届大赛延续 "一条 SQL" 的核心挑战，设置题目为「用一条 SQL」解数独问题。查看赛题详情

以下是本次决赛第 8 名选手，郑毅的参赛介绍：

第8名

郑毅

参赛选手：郑毅

个人简介：数据库新手，从事算法工作

参赛数据库：PostgreSQL

性能评测：1万级数据代码性能评测 14.448 秒

综合得分：67.3

以下是郑毅的代码说明思路：

---

---

参赛完整SQL：

WITH input AS (
    SELECT
        id,
        regexp_replace(puzzle, '\s', '', 'g') AS p,
        81 - length(replace(regexp_replace(puzzle, '\s', '', 'g'), '?', '')) AS zeros
    FROM sudoku9_9
    WHERE 81 - length(replace(regexp_replace(puzzle, '\s', '', 'g'), '?', '')) <= 50
    ORDER BY zeros, id
    LIMIT 10000
),
candidates AS (
    WITH RECURSIVE x(id, p, pos) AS (
        SELECT
            i.id,
            i.p,
            strpos(i.p, '?') AS pos
        FROM input i

        UNION ALL

        SELECT
            x.id,
            overlay(x.p PLACING nums.d::text FROM x.pos FOR 1) AS p,
            strpos(overlay(x.p PLACING nums.d::text FROM x.pos FOR 1), '?') AS pos
        FROM x
        CROSS JOIN LATERAL (
            VALUES ('1'), ('2'), ('3'), ('4'), ('5'),
                   ('6'), ('7'), ('8'), ('9')
        ) AS nums(d)
        WHERE x.pos > 0
          AND strpos(
                substr(x.p, ((x.pos - 1) / 9) * 9 + 1, 9),
                nums.d
              ) = 0
          AND strpos(
                substr(x.p, (x.pos - 1) % 9 + 1, 1)  ||
                substr(x.p, (x.pos - 1) % 9 + 10, 1) ||
                substr(x.p, (x.pos - 1) % 9 + 19, 1) ||
                substr(x.p, (x.pos - 1) % 9 + 28, 1) ||
                substr(x.p, (x.pos - 1) % 9 + 37, 1) ||
                substr(x.p, (x.pos - 1) % 9 + 46, 1) ||
                substr(x.p, (x.pos - 1) % 9 + 55, 1) ||
                substr(x.p, (x.pos - 1) % 9 + 64, 1) ||
                substr(x.p, (x.pos - 1) % 9 + 73, 1),
                nums.d
              ) = 0
          AND strpos(
                substr(
                    x.p,
                    ((x.pos - 1) / 27) * 27 + ((x.pos - 1) % 9) / 3 * 3 + 1,
                    3
                ) ||
                substr(
                    x.p,
                    ((x.pos - 1) / 27) * 27 + ((x.pos - 1) % 9) / 3 * 3 + 10,
                    3
                ) ||
                substr(
                    x.p,
                    ((x.pos - 1) / 27) * 27 + ((x.pos - 1) % 9) / 3 * 3 + 19,
                    3
                ),
                nums.d
              ) = 0
    )
    -- 这里的关键是：对每个 id 只取一条解
    SELECT DISTINCT ON (id)
        id,
        p
    FROM x
    WHERE pos = 0
    ORDER BY id
)
SELECT
    s.id,
    s.puzzle,
    CASE
        WHEN c.p IS NOT NULL THEN
            substr(c.p, 1,9) || E'\n' ||
            substr(c.p,10,9) || E'\n' ||
            substr(c.p,19,9) || E'\n' ||
            substr(c.p,28,9) || E'\n' ||
            substr(c.p,37,9) || E'\n' ||
            substr(c.p,46,9) || E'\n' ||
            substr(c.p,55,9) || E'\n' ||
            substr(c.p,64,9) || E'\n' ||
            substr(c.p,73,9)
        ELSE NULL
    END AS result
FROM sudoku9_9 s
LEFT JOIN candidates c ON s.id = c.id
ORDER BY s.id;

《数据库编程大赛》

下一次再聚！

感谢大家对本次《数据库编程大赛》的关注和支持，欢迎加入技术交流群，更多精彩活动不断，欢迎各路数据库爱好者来挑战！