ITPUB第2届“盛拓传媒杯”SQL数据库编程大赛第二期正式开始，欢迎大家踊跃参与答题！

〇〇 · 发表于 2025-3-27 08:10

有两处错误：1.(row_num-1)*(:v_width+1) + col_num计算坐标宽度不该加1，LISTAGG(line, ' ') WITHIN GROUP (ORDER BY row_num)连接分隔符不应该有空格

〇〇 · 发表于 2025-3-27 08:39

探雷的思路有点新颖，用的是穷举法，我让他改成了postgresql语法

WITH recursive
-- 从表q中获取参数
params AS (
SELECT 4 width, 4 height,3 cnt, '111 2*1 *32112*1' str
--FROM q
--LIMIT 1
),
-- 解析输入字符串为网格表(行号,列号,单元格值)
grid AS (
SELECT
row_num,
col_num,
CASE
WHEN SUBSTR(p.str, (row_num-1)*p.width + col_num, 1) ~ '[0-9]' THEN
SUBSTR(p.str, (row_num-1)*p.width + col_num, 1)::INTEGER
WHEN SUBSTR(p.str, (row_num-1)*p.width + col_num, 1) = ' ' THEN NULL
ELSE NULL
END AS hint,
(row_num-1)*p.width + col_num AS pos
FROM
params p,
GENERATE_SERIES(1, p.height) AS row_num,
GENERATE_SERIES(1, p.width) AS col_num
),
-- 获取所有可能的雷位置（空格位置）
possible_mines AS (
SELECT row_num, col_num, pos
FROM grid
WHERE hint IS NULL
ORDER BY pos
),
-- 递归生成有效的雷组合
mine_combinations AS (
SELECT
ARRAY[(row_num, col_num)] AS mines,
ARRAY[pos] AS positions,
1 AS count
FROM possible_mines
UNION ALL
SELECT
m.mines || (p.row_num, p.col_num),
m.positions || p.pos,
m.count + 1
FROM mine_combinations m
JOIN possible_mines p ON p.pos > m.positions[array_length(m.positions, 1)]
WHERE m.count < (SELECT cnt FROM params)
),
-- 验证雷组合是否符合提示
valid_mines AS (
SELECT mines
FROM mine_combinations
WHERE count = (SELECT cnt FROM params)
AND NOT EXISTS (
SELECT 1
FROM grid g
WHERE g.hint IS NOT NULL
AND g.hint <> (
SELECT COUNT(*)
FROM UNNEST(mines) AS m(row_num INT, col_num INT)
WHERE m.row_num BETWEEN g.row_num-1 AND g.row_num+1
AND m.col_num BETWEEN g.col_num-1 AND g.col_num+1
AND (m.row_num <> g.row_num OR m.col_num <> g.col_num)
)
)
LIMIT 1
),
-- 构建结果网格
result_grid AS (
SELECT
g.row_num,
g.col_num,
CASE
WHEN EXISTS (
SELECT 1 FROM valid_mines v, UNNEST(v.mines) AS m(row_num INT, col_num INT)
WHERE m.row_num = g.row_num AND m.col_num = g.col_num
) THEN '*'
WHEN g.hint IS NOT NULL THEN g.hint::TEXT
ELSE ' '
END AS cell
FROM grid g
)
-- 组装最终结果字符串
SELECT STRING_AGG(
(SELECT STRING_AGG(cell, '' ORDER BY col_num)
FROM result_grid r2
WHERE r2.row_num = r1.row_num
), '' ORDER BY row_num) AS result
FROM (SELECT DISTINCT row_num FROM result_grid) r1;

复制代码