2025 NineData 第三届数据库编程大赛 给出的题目，题目详情
此次比赛已是 NineData 举办的第三届了。但个人感觉这届的题更难，所以这届我只解决4×4的数独，未解决9×9的数独。
有兴趣的朋友,可根据以上规则试试. 构造测试数据
CREATE TABLE `sudoku4_4` (
`id` int NOT NULL AUTO_INCREMENT,
`puzzle` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
-- `sudoku4_4`
INSERT INTO `sudoku4_4` (`id`,`puzzle`) VALUES
(1,'1?3?\n??14\n4??3\n3241'),
(2,'1342\n42?3\n3??4\n?431'),
(3,'2?4?\n142?\n?1?2\n321?'),
(4,'2??3\n1?2?\n4??2\n?241'),
(5,'4?13\n132?\n31??\n2431'),
(6,'3412\n21??\n12??\n432?'),
(7,'14?2\n3??1\n?123\n?3?4'),
(8,'12?4\n43??\n24??\n??42'),
(9,'?42?\n??4?\n1??4\n431?'),
(10,'231?\n412?\n???2\n3??1'),
(11,'?432\n???1\n3214\n4123'),
(12,'?421\n?134\n1?42\n?2?3'),
(13,'4321\n2134\n12??\n?4??'),
(14,'42?3\n314?\n132?\n2??1'),
(15,'?132\n2341\n??14\n1??3'),
(16,'?213\n?12?\n?3??\n?4?1'),
(17,'41??\n3?14\n?34?\n14??'),
(18,'???1\n3?4?\n?3?4\n1423'),
(19,'24?3\n?32?\n41?2\n?2??'),
(20,'?412\n?1?4\n1?4?\n43?1'),
(21,'1234\n3?2?\n2??3\n4???'),
(22,'?132\n23?1\n?42?\n3?1?'),
(23,'??24\n243?\n4213\n314?'),
(24,'1342\n4??1\n3?24\n2?13'),
(25,'3?2?\n?413\n4231\n????'),
(26,'1??4\n3?12\n?1?3\n?341'),
(27,'413?\n2?14\n32?1\n1?2?'),
(28,'???3\n4?2?\n?134\n3?12'),
(29,'?412\n12?3\n?321\n2?34'),
(30,'1?4?\n43?1\n3??2\n?13?'),
(31,'?124\n?413\n?3?2\n?231'),
(32,'34?1\n2134\n1???\n4???'),
(33,'241?\n??24\n?231\n31?2'),
(34,'24?3\n??4?\n?231\n?12?'),
(35,'321?\n1?2?\n23?1\n4?32'),
(36,'??34\n3?2?\n4?12\n1??3'),
(37,'2???\n1??2\n?124\n4?1?'),
(38,'?21?\n4132\n234?\n142?'),
(39,'?2??\n???2\n?32?\n2413'),
(40,'??13\n1324\n4?32\n??41'),
(41,'?34?\n412?\n1432\n3???'),
(42,'???3\n3?41\n4?12\n2134'),
(43,'42??\n3???\n?4?3\n2314'),
(44,'41?2\n?314\n1???\n3?21'),
(45,'423?\n??4?\n231?\n?423'),
(46,'4312\n2143\n?42?\n?2?4'),
(47,'?134\n3?21\n1243\n4???'),
(48,'3412\n1??4\n?3?1\n412?'),
(49,'?31?\n41??\n3???\n1?32'),
(50,'4213\n?32?\n3?4?\n24?1'),
(51,'123?\n342?\n?3?2\n?143'),
(52,'?243\n3?1?\n432?\n2?3?'),
(53,'2413\n132?\n????\n4?31'),
(54,'2341\n4??3\n1?32\n3?1?'),
(55,'2?14\n?432\n412?\n32?1'),
(56,'?13?\n?412\n12?3\n4?21'),
(57,'?413\n1?24\n31??\n???1'),
(58,'4???\n??14\n1?23\n??41'),
(59,'?34?\n41?3\n3?14\n14?2'),
(60,'324?\n1?23\n2134\n??1?'),
(61,'?4??\n2?41\n?2?4\n4??3'),
(62,'2?13\n??42\n1?2?\n42?1'),
(63,'142?\n???1\n?21?\n?1?2'),
(64,'21?3\n3421\n12?4\n?3?2'),
(65,'?3??\n21?3\n123?\n3421'),
(66,'143?\n32?1\n412?\n231?'),
(67,'?1?3\n?31?\n??34\n3??1'),
(68,'2?3?\n4???\n?2??\n1423'),
(69,'?4?2\n????\n4321\n?134'),
(70,'?12?\n?2??\n?342\n2?31'),
(71,'34?2\n2?34\n?321\n??43'),
(72,'32?4\n41??\n234?\n1432'),
(73,'3214\n???3\n2341\n41??'),
(74,'4321\n?1?4\n?2??\n3?12'),
(75,'2134\n3?1?\n?321\n1?43'),
(76,'12??\n34?1\n?31?\n41?2'),
(77,'?431\n1?4?\n4213\n312?'),
(78,'1342\n?431\n42?3\n?1??'),
(79,'??2?\n1??4\n2?13\n?14?'),
(80,'34?2\n?1??\n4?21\n?2?3'),
(81,'231?\n4??3\n1??2\n?241'),
(82,'132?\n?2?1\n241?\n3142'),
(83,'????\n3?41\n2314\n412?'),
(84,'?231\n?12?\n24?3\n1342'),
(85,'?3?1\n4123\n???4\n3?12'),
(86,'12?3\n?4??\n2?34\n??1?'),
(87,'3241\n14?2\n23?4\n41??'),
(88,'?13?\n321?\n??23\n?34?'),
(89,'14??\n23?1\n???3\n?1?4'),
(90,'214?\n3421\n??3?\n4??2'),
(91,'423?\n13??\n3412\n214?'),
(92,'31??\n243?\n?213\n13?4'),
(93,'2?31\n3???\n4?13\n?3??'),
(94,'3?41\n?432\n??23\n23?4'),
(95,'???3\n34?2\n?134\n?32?'),
(96,'3412\n2??3\n43??\n??34'),
(97,'2?4?\n?3?2\n1?34\n3421'),
(98,'4??3\n231?\n???2\n32?1'),
(99,'1?42\n?213\n2??1\n31?4'),
(100,'23??\n?123\n143?\n?2?4');
解题SQL
WITH
-- 将谜题转换为4x4网格
puzzle_grid AS (
SELECT
id,
SUBSTRING_INDEX(SUBSTRING_INDEX(puzzle, '\n', 1), '\n', -1) AS row1,
SUBSTRING_INDEX(SUBSTRING_INDEX(puzzle, '\n', 2), '\n', -1) AS row2,
SUBSTRING_INDEX(SUBSTRING_INDEX(puzzle, '\n', 3), '\n', -1) AS row3,
SUBSTRING_INDEX(SUBSTRING_INDEX(puzzle, '\n', 4), '\n', -1) AS row4
FROM sudoku4_4
)
-- 生成所有可能的数字组合
,numbers AS (SELECT column_0 AS n FROM (VALUES ROW(1), ROW(2), ROW(3), ROW(4)) AS t),
-- 生成所有可能的位置
positions AS (
SELECT id, `row`, col,
CASE
WHEN `row` = 1 THEN SUBSTRING(row1, col, 1)
WHEN `row` = 2 THEN SUBSTRING(row2, col, 1)
WHEN `row` = 3 THEN SUBSTRING(row3, col, 1)
WHEN `row` = 4 THEN SUBSTRING(row4, col, 1)
END AS val
FROM puzzle_grid
CROSS JOIN (SELECT n AS `row` from numbers) r
CROSS JOIN(SELECT n AS col from numbers) c
)
,
new_cte1 as
(
select * from positions a inner join LATERAL (select n from numbers b where a.val = '?' ) as t on 1=1
where # id = 1 and
not exists(select 1 from positions f where f.id = a.id and t.n=f.val and a.`row`=f.`row` and a.col <> f.col)
and not exists(select 1 from positions f where f.id = a.id and t.n=f.val and a.`col`=f.`col` and a.`row` <> f.`row`)
and not exists(select 1 from positions f where f.id = a.id and t.n=f.val and a.`col`<>f.`col` and a.`row` <> f.`row` and CEILING(a.row/2) = CEILING(f.row/2) and CEILING(a.col/2) = CEILING(f.col/2))
),
new_cte2 as
(SELECT a.id,a.row,a.col,case when a.val='?' then b.n else a.val end as val,CEILING(a.row/2) as row_area,CEILING(a.col/2) as col_area from positions a
left join new_cte1 b on a.id = b.id and a.row= b.row and a.col = b.col
)
-- 验证同一区域只能是1,2,3,4 验证区域[1,1]
,area1_vals AS (SELECT DISTINCT id,`row`,col, row_area,col_area, val FROM new_cte2 WHERE `row`= 1 and col = 1)
,area2_vals AS (SELECT DISTINCT id,`row`,col, row_area,col_area, val FROM new_cte2 WHERE `row`= 1 and col = 2)
,area3_vals AS (SELECT DISTINCT id,`row`,col, row_area,col_area, val FROM new_cte2 WHERE `row`= 2 and col = 1)
,area4_vals AS (SELECT DISTINCT id,`row`,col, row_area,col_area, val FROM new_cte2 WHERE `row`= 2 and col = 2)
-- 验证同一区域只能是1,2,3,4 验证区域[1,2]
,area1_vals2 AS (SELECT DISTINCT id,`row`,col, row_area,col_area, val FROM new_cte2 WHERE `row`= 1 and col = 3)
,area2_vals2 AS (SELECT DISTINCT id,`row`,col, row_area,col_area, val FROM new_cte2 WHERE `row`= 1 and col = 4)
,area3_vals2 AS (SELECT DISTINCT id,`row`,col, row_area,col_area, val FROM new_cte2 WHERE `row`= 2 and col = 3)
,area4_vals2 AS (SELECT DISTINCT id,`row`,col, row_area,col_area, val FROM new_cte2 WHERE `row`= 2 and col = 4)
-- 验证同一区域只能是1,2,3,4 验证区域[2,1]
,area1_vals3 AS (SELECT DISTINCT id,`row`,col, row_area,col_area, val FROM new_cte2 WHERE `row`= 3 and col = 1)
,area2_vals3 AS (SELECT DISTINCT id,`row`,col, row_area,col_area, val FROM new_cte2 WHERE `row`= 3 and col = 2)
,area3_vals3 AS (SELECT DISTINCT id,`row`,col, row_area,col_area, val FROM new_cte2 WHERE `row`= 4 and col = 1)
,area4_vals3 AS (SELECT DISTINCT id,`row`,col, row_area,col_area, val FROM new_cte2 WHERE `row`= 4 and col = 2)
-- 验证同一区域只能是1,2,3,4 验证区域[2,2]
,area1_vals4 AS (SELECT DISTINCT id,`row`,col, row_area,col_area, val FROM new_cte2 WHERE `row`= 3 and col = 3)
,area2_vals4 AS (SELECT DISTINCT id,`row`,col, row_area,col_area, val FROM new_cte2 WHERE `row`= 3 and col = 4)
,area3_vals4 AS (SELECT DISTINCT id,`row`,col, row_area,col_area, val FROM new_cte2 WHERE `row`= 4 and col = 3)
,area4_vals4 AS (SELECT DISTINCT id,`row`,col, row_area,col_area, val FROM new_cte2 WHERE `row`= 4 and col = 4)
,new_cte5 as(
select t.* ,row_number() over(partition by id) as rn1 from
(
SELECT
c1.id,
c1.row as c1r,c1.col as c1c, c2.row as c2r,c2.col as c2c, c3.row as c3r,c3.col as c3c, c4.row as c4r,c4.col as c4c,
c1.val AS val1,
c2.val AS val2,
c3.val AS val3,
c4.val AS val4,
c1.val + c2.val + c3.val+ c4.val AS total1,
c1.val * c2.val * c3.val* c4.val AS total2
,row_number()over(partition by c1.id) as rn
FROM area1_vals c1
CROSS JOIN area2_vals c2 ON c1.id = c2.id
CROSS JOIN area3_vals c3 ON c1.id = c3.id
CROSS JOIN area4_vals c4 ON c1.id = c4.id
#where a.id = c1.id and (a.row = c1.row and a.col = c1.col and a.val = c1.val or a.row = c2.row and a.col = c2.col and a.val = c2.val or a.row = c3.row and a.col = c3.col and a.val = c3.val or a.row = c4.row and a.col = c4.col and a.val = c4.val )
having total1 = 10 and total2 = 24 #and total3 = 10 and total4 = 10 and total5 = 10 and total6 = 10
union all
SELECT
c1.id,
c1.row as c1r,c1.col as c1c, c2.row as c2r,c2.col as c2c, c3.row as c3r,c3.col as c3c, c4.row as c4r,c4.col as c4c,
c1.val AS val1,
c2.val AS val2,
c3.val AS val3,
c4.val AS val4,
c1.val + c2.val + c3.val+ c4.val AS total1,
c1.val * c2.val * c3.val* c4.val AS total2
,row_number()over(partition by c1.id) as rn
FROM area1_vals2 c1
CROSS JOIN area2_vals2 c2 ON c1.id = c2.id
CROSS JOIN area3_vals2 c3 ON c1.id = c3.id
CROSS JOIN area4_vals2 c4 ON c1.id = c4.id
#where a.id = c1.id and (a.row = c1.row and a.col = c1.col and a.val = c1.val or a.row = c2.row and a.col = c2.col and a.val = c2.val or a.row = c3.row and a.col = c3.col and a.val = c3.val or a.row = c4.row and a.col = c4.col and a.val = c4.val )
having total1 = 10 and total2 = 24
union all
SELECT
c1.id,
c1.row as c1r,c1.col as c1c, c2.row as c2r,c2.col as c2c, c3.row as c3r,c3.col as c3c, c4.row as c4r,c4.col as c4c,
c1.val AS val1,
c2.val AS val2,
c3.val AS val3,
c4.val AS val4,
c1.val + c2.val + c3.val+ c4.val AS total1,
c1.val * c2.val * c3.val* c4.val AS total2
,row_number()over(partition by c1.id) as rn
FROM area1_vals3 c1
CROSS JOIN area2_vals3 c2 ON c1.id = c2.id
CROSS JOIN area3_vals3 c3 ON c1.id = c3.id
CROSS JOIN area4_vals3 c4 ON c1.id = c4.id
#where a.id = c1.id and (a.row = c1.row and a.col = c1.col and a.val = c1.val or a.row = c2.row and a.col = c2.col and a.val = c2.val or a.row = c3.row and a.col = c3.col and a.val = c3.val or a.row = c4.row and a.col = c4.col and a.val = c4.val )
having total1 = 10 and total2 = 24
union all
SELECT
c1.id,
c1.row as c1r,c1.col as c1c, c2.row as c2r,c2.col as c2c, c3.row as c3r,c3.col as c3c, c4.row as c4r,c4.col as c4c,
c1.val AS val1,
c2.val AS val2,
c3.val AS val3,
c4.val AS val4,
c1.val + c2.val + c3.val+ c4.val AS total1,
c1.val * c2.val * c3.val* c4.val AS total2
,row_number()over(partition by c1.id) as rn
FROM area1_vals4 c1
CROSS JOIN area2_vals4 c2 ON c1.id = c2.id
CROSS JOIN area3_vals4 c3 ON c1.id = c3.id
CROSS JOIN area4_vals4 c4 ON c1.id = c4.id
#where a.id = c1.id and (a.row = c1.row and a.col = c1.col and a.val = c1.val or a.row = c2.row and a.col = c2.col and a.val = c2.val or a.row = c3.row and a.col = c3.col and a.val = c3.val or a.row = c4.row and a.col = c4.col and a.val = c4.val )
having total1 = 10 and total2 = 24
) as t
)
,cell_data AS (
-- 将每个id对应的4个单元格拆开
SELECT id,rn1, c1r AS r, c1c AS c, val1 AS val FROM new_cte5
UNION ALL
SELECT id,rn1, c2r AS r, c2c AS c, val2 AS val FROM new_cte5
UNION ALL
SELECT id,rn1, c3r AS r, c3c AS c, val3 AS val FROM new_cte5
UNION ALL
SELECT id,rn1, c4r AS r, c4c AS c, val4 AS val FROM new_cte5
)
,
possible_combinations AS (
-- 找出所有可能的4行组合（每行对应一个宫）
SELECT
c1.id ,
c1.rn1 AS id1,
c2.rn1 AS id2,
c3.rn1 AS id3,
c4.rn1 AS id4
FROM (SELECT DISTINCT id,rn1 FROM new_cte5 WHERE (c1r <= 2 AND c1c <= 2)) c1 -- 宫1: 行1-2, 列1-2
CROSS JOIN (SELECT DISTINCT id,rn1 FROM new_cte5 WHERE (c1r <= 2 AND c1c > 2)) c2 on c1.id = c2.id -- 宫2: 行1-2, 列3-4
CROSS JOIN (SELECT DISTINCT id,rn1 FROM new_cte5 WHERE (c1r > 2 AND c1c <= 2)) c3 on c1.id = c3.id -- 宫3: 行3-4, 列1-2
CROSS JOIN (SELECT DISTINCT id,rn1 FROM new_cte5 WHERE (c1r > 2 AND c1c > 2)) c4 on c1.id = c4.id -- 宫4: 行3-4, 列3-4
WHERE c1.rn1 <> c2.rn1 AND c1.rn1 <> c3.rn1 AND c1.rn1 <> c4.rn1
AND c2.rn1 <> c3.rn1 AND c2.rn1 <> c4.rn1
AND c3.rn1 <> c4.rn1
)
,
combination_cells AS (
-- 获取组合的所有单元格
SELECT
pc.id1, pc.id2, pc.id3, pc.id4,pc.id,
cd.r, cd.c, cd.val
FROM possible_combinations pc
JOIN cell_data cd ON cd.rn1 IN (pc.id1, pc.id2, pc.id3, pc.id4) and pc.id = cd.id
)
,
valid_combinations AS (
-- 检查组合是否满足数独条件
SELECT id1, id2, id3, id4,id
FROM combination_cells
GROUP BY id1, id2, id3, id4,id
HAVING
-- 检查每行是否包含1,2,3,4各一次
COUNT(DISTINCT CASE WHEN r = 1 THEN val END) = 4 AND
COUNT(DISTINCT CASE WHEN r = 2 THEN val END) = 4 AND
COUNT(DISTINCT CASE WHEN r = 3 THEN val END) = 4 AND
COUNT(DISTINCT CASE WHEN r = 4 THEN val END) = 4 AND
-- 检查每列是否包含1,2,3,4各一次
COUNT(DISTINCT CASE WHEN c = 1 THEN val END) = 4 AND
COUNT(DISTINCT CASE WHEN c = 2 THEN val END) = 4 AND
COUNT(DISTINCT CASE WHEN c = 3 THEN val END) = 4 AND
COUNT(DISTINCT CASE WHEN c = 4 THEN val END) = 4
)
,
-- 选择第一个有效的组合（id最小的组合）
first_valid_combination AS (
SELECT id,id1, id2, id3, id4,
row_number() over(partition by id) as rn
FROM valid_combinations
group by id
)
,r1 as
(select * from first_valid_combination where rn = 1
)
,r2 as
(
select * from new_cte2 a where exists
(select 1 from new_cte5 c
inner join r1 b on c.id = b.id and c.rn1 in (b.id1,b.id2,b.id3,b.id4)
where a.id = c.id and (a.row = c.c1r and a.col = c1c and a.val = val1 or a.row = c2r and a.col = c2c and a.val = val2 or a.row = c3r and a.col = c3c and a.val = val3 or a.row = c4r and a.col = c4c and a.val = val4 )
)
)
select b.*,group_concat(val order by a.id,`row`,col) from r2 as a
inner join sudoku4_4 as b on a.id = b.id
group by a.id
SQL运行后，答案如下
