When I'm bored at the office or home, I sleep.
One rainy afternoon, when I can't find the bug on my application, I feel kinda bored but I don't want to sleep because my manager was pissed. So i went over to the next cubicle to chat with my sleepy officemate and found out that she was playing
Sudoku.
I asked her to explain to me how that game works. And I find it interesting.
After playing some 5 or 6 games, I thought I can create problems for this game using t-sql, just create a lot of random numbers then copy-paste the result in excel.
So there I was, creating a sudoku problem script. And here's what I've done.
You can also try it. Just run the script and copy-paste the result in excel and that's it..
/*****Sudoku*******/
declare @tbl table (id int identity, a char(1), b char(1), c char(1), d char(1), e char(1), f char(1), g char(1), h char(1), i char(1))
declare @tblCoordinatesAndValue table (x int, y int, value int)
declare @i int, @tmpNo int, @x int, @y int, @v char(1), @isOkX int, @strValues varchar(50), @ypos int
set @i = 0
insert into @tbl (a, b, c, d, e, f, g, h, i) values ('', '', '', '', '', '', '', '', '')
insert into @tbl (a, b, c, d, e, f, g, h, i) values ('', '', '', '', '', '', '', '', '')
insert into @tbl (a, b, c, d, e, f, g, h, i) values ('', '', '', '', '', '', '', '', '')
insert into @tbl (a, b, c, d, e, f, g, h, i) values ('', '', '', '', '', '', '', '', '')
insert into @tbl (a, b, c, d, e, f, g, h, i) values ('', '', '', '', '', '', '', '', '')
insert into @tbl (a, b, c, d, e, f, g, h, i) values ('', '', '', '', '', '', '', '', '')
insert into @tbl (a, b, c, d, e, f, g, h, i) values ('', '', '', '', '', '', '', '', '')
insert into @tbl (a, b, c, d, e, f, g, h, i) values ('', '', '', '', '', '', '', '', '')
insert into @tbl (a, b, c, d, e, f, g, h, i) values ('', '', '', '', '', '', '', '', '')
declare @min int, @max int
select @min = 1, @max = 9
while @i < 80
begin
set @strValues = ''
select @tmpNo = floor((((@max + 1) - @min) * RAND()) + @min)
set @x = @tmpNo
select @tmpNo = floor((((@max + 1) - @min) * RAND()) + @min)
set @y = @tmpNo
select @tmpNo = floor((((@max + 1) - @min) * RAND()) + @min)
set @v = @tmpNo
if not exists (select * from @tblCoordinatesAndValue where x = @x and y = @y)
begin
insert into @tblCoordinatesAndValue (x, y, value) values (@x, @y, @v)
if @y >= 1 and @y <= 3
set @ypos = 1
if @y >= 4 and @y <= 6
set @ypos = 4
if @y >= 7 and @y <= 9
set @ypos = 7
if @x >= 1 and @x <= 3
select @strValues = @strValues + tblTemp.tmp from
(select top 3 convert(varchar, a) [tmp] from @tbl where id >= @ypos
union all select convert(varchar, b) from @tbl where id >= @ypos
union all select convert(varchar, c) from @tbl where id >= @ypos) tblTemp
if @x >= 4 and @x <= 6
select @strValues = @strValues + tblTemp.tmp from
(select top 3 convert(varchar, d) [tmp] from @tbl where id >= @ypos
union all select convert(varchar, e) from @tbl where id >= @ypos
union all select convert(varchar, f) from @tbl where id >= @ypos) tblTemp
if @x >= 7 and @x <= 9
select @strValues = @strValues + tblTemp.tmp from
(select top 3 convert(varchar, g) [tmp] from @tbl where id >= @ypos
union all select convert(varchar, h) from @tbl where id >= @ypos
union all select convert(varchar, i) from @tbl where id >= @ypos) tblTemp
if charindex(convert(varchar, @v), @strValues) = 0
begin
set @isOkX =
(select (case when @x=1 then a when @x=2 then b when @x=3 then c
when @x=4 then d when @x=5 then e when @x=6 then f
when @x=7 then g when @x=8 then h when @x=9 then i end)
from @tbl
where (case when @x=1 then a when @x=2 then b when @x=3 then c
when @x=4 then d when @x=5 then e when @x=6 then f
when @x=7 then g when @x=8 then h when @x=9 then i end) = @v)
if isnull(@isOkX, 0) = 0
begin
if not exists(select * from @tbl where (a=@v or b=@v or c=@v or d=@v or e=@v or f=@v or g=@v or h=@v or i=@v) and (id = @y))
begin
update @tbl
set a = (case when @x = 1 then @v else a end)
, b = (case when @x = 2 then @v else b end)
, c = (case when @x = 3 then @v else c end)
, d = (case when @x = 4 then @v else d end)
, e = (case when @x = 5 then @v else e end)
, f = (case when @x = 6 then @v else f end)
, g = (case when @x = 7 then @v else g end)
, h = (case when @x = 8 then @v else h end)
, i = (case when @x = 9 then @v else i end)
where id = @y
end
end
end
end
set @i = @i + 1
end
select a, b, c, d, e, f, g, h, i from @tbl
/******************/
If you can optimized the script, the better..
Posted
Sep 05 2006, 05:34 PM
by
clintz