SQL实现马踏棋盘

/*
关于马踏棋盘的基本过程
国际象棋的棋盘为8*8的方格棋盘,现将马放在任意指定的方格中,按照马走棋的规则将马进行移动,要求每个方格只能进入一次,最终使得马走遍棋盘的64个方格
*/
IF OBJECT_ID('sp_init') IS NOT NULL
DROP PROCEDURE sp_init
GO
IF OBJECT_ID('sp_getDirection') IS NOT NULL
DROP PROCEDURE sp_getDirection
GO
IF OBJECT_ID('sp_getWayCount') IS NOT NULL
DROP PROCEDURE sp_getWayCount
GO
IF OBJECT_ID('sp_print') IS NOT NULL
DROP PROCEDURE sp_print
GO
IF OBJECT_ID('sp_travel') IS NOT NULL
DROP PROCEDURE sp_travel
GO
IF OBJECT_ID('sp_mtqp2') IS NOT NULL
DROP PROCEDURE sp_mtqp2
GO
IF OBJECT_ID('mt_check') IS NOT NULL
DROP FUNCTION mt_check
GO
IF OBJECT_ID('mt_getMin') IS NOT NULL
DROP PROCEDURE mt_getMin
GO


CREATE PROCEDURE sp_init
AS
BEGIN 
	IF OBJECT_ID('mt_count') IS NOT NULL
	DROP  TABLE mt_count
	IF OBJECT_ID('mt_board') IS NOT NULL
	DROP  TABLE mt_board 
	IF OBJECT_ID('mt_waycount') IS NOT NULL
	DROP  TABLE mt_waycount 
	IF OBJECT_ID('mt_direction') IS NOT NULL
	DROP  TABLE mt_direction 
	IF OBJECT_ID('mt_destination') IS NOT NULL
	DROP  TABLE mt_destination 

	CREATE TABLE mt_count
	(
		x INT,
		v INT  
	)
		
	CREATE TABLE mt_destination
	(
		i INT,
		j INT,
		v INT 
	)
	 
	SELECT i,j,0 AS v INTO mt_board FROM 
	(        
		SELECT number AS i FROM master..spt_values WHERE type='p' AND number BETWEEN 0 AND 7
	) a 
	CROSS JOIN 
	(
		SELECT number AS j FROM master..spt_values WHERE type='p' AND number BETWEEN 0 AND 7
	) b 

	SELECT * INTO mt_waycount FROM mt_board 
	 
	SELECT i,j,k,0 AS v INTO  mt_direction
	FROM mt_board a CROSS JOIN (SELECT number AS k FROM master..spt_values WHERE type='p' AND number BETWEEN 0 AND 7)  c 

	INSERT INTO dbo.mt_count
	SELECT number AS k,0 FROM master..spt_values WHERE type='p' AND number BETWEEN 0 AND 7
	 
	INSERT INTO mt_destination VALUES(0,0,1),(0,1,2)
	INSERT INTO mt_destination VALUES(1,0,2),(1,1,1)
	INSERT INTO mt_destination VALUES(2,0,2),(2,1,-1)
	INSERT INTO mt_destination VALUES(3,0,1),(3,1,-2)
	INSERT INTO mt_destination VALUES(4,0,-1),(4,1,-2)
	INSERT INTO mt_destination VALUES(5,0,-2),(5,1,-1)
	INSERT INTO mt_destination VALUES(6,0,-2),(6,1,1)
	INSERT INTO mt_destination VALUES(7,0,-1),(7,1,2)
END 
go
 
CREATE FUNCTION mt_check(@x INT,@y int)
RETURNS BIT
AS
BEGIN 
	IF ((@x <= 7 and @x >= 0) and (@y <= 7 and @y >= 0))
      RETURN 'true'
    RETURN 'false'
END 
go

CREATE  PROCEDURE mt_getMin( @min INT OUTPUT )
AS
BEGIN 
	 
	SET  @min =0
	
	SELECT TOP 1 @min=x FROM dbo.mt_count WHERE v>0 AND v<999 ORDER  BY v ,x DESC 
	UPDATE  mt_count SET v=999 WHERE x= @min 

END 
GO 
 
CREATE PROCEDURE sp_getDirection 
AS
BEGIN
	DECLARE @x INT=0
	DECLARE @y INT=0  
	DECLARE @i INT=0
	DECLARE @x1 INT 
	DECLARE @y1 INT
	DECLARE @min INT
	   
	EXEC sp_getWayCount
	
	
	WHILE @x<8
	BEGIN 
		SET @y=0
		WHILE @y<8
		BEGIN 
			SET @i=0
			WHILE @i<8
			BEGIN
				SELECT @x1=@x+v FROM mt_destination WHERE i=@i AND j=0
				SELECT @y1=@y+v FROM mt_destination WHERE i=@i AND j=1
				IF dbo.mt_check(@x1,@y1)=1
				BEGIN
					update mt_count SET v=(SELECT  v FROM mt_waycount WHERE i=@x1 AND j=@y1) WHERE x=@i 
				END 
				ELSE 
				BEGIN 
					update mt_count SET v=100 WHERE x=@i 
				END 
				SET @i=@i+1
			END 

			SET @i=0
			WHILE @i<8
			BEGIN 
				EXEC mt_getMin @min OUTPUT
				update mt_direction SET v= @min WHERE  i=@x AND j=@y AND k=@i 
				SET @i=@i+1
			END 
                    
			SET @y=@y+1
		END 
		SET @x=@x+1
	END 
END 
go           


CREATE PROCEDURE sp_getWayCount
AS
BEGIN 
	DECLARE @x INT=0
	DECLARE @y INT=0
	DECLARE @i INT =0
	DECLARE @x1 INT 
	DECLARE @y1 INT
	
	WHILE @x<8
	BEGIN 
		SET @y=0 
		WHILE @y<8
		BEGIN
			SET @i=0
			WHILE @i<8
			BEGIN 
				SELECT @x1=@x+v FROM mt_destination WHERE i=@i AND j=0
				SELECT @y1=@y+v FROM mt_destination WHERE i=@i AND j=1
				IF dbo.mt_check(@x1,@y1)=1
				BEGIN
					UPDATE 	mt_waycount SET v=v+1 WHERE i=@x AND j=@y
				END 
				SET @i=@i+1
			END 
			SET @y=@y+1
		END 
		SET @x=@x+1
	END  

END  
GO 


CREATE PROCEDURE sp_print
AS
BEGIN
	SELECT *  FROM mt_board
	PIVOT
	(
		MAX(v)
		FOR j IN ([0],[1],[2],[3],[4],[5],[6],[7])
	) p
END 
GO 


CREATE PROCEDURE sp_travel(@step INT,@no INT,@x INT ,@y INT)
AS
BEGIN 
	DECLARE @i INT=0
	DECLARE @x1 INT 
	DECLARE @y1 INT
  
	WHILE @i<8
	BEGIN 
		IF @step=64
		BEGIN
			EXEC sp_print 
			SET @no=@no+1
			return
		END 
		
		SELECT @x1=@x+v FROM mt_destination WHERE i=(SELECT v FROM mt_direction WHERE i=@x AND j=@y AND k=@i) AND j=0  
		SELECT @y1=@y+v FROM mt_destination WHERE i=(SELECT v FROM mt_direction WHERE i=@x AND j=@y AND k=@i) AND j=1  
        IF dbo.mt_check(@x1,@y1)=1       
        BEGIN 
			IF EXISTS(SELECT 1 FROM mt_board WHERE i=@x1 AND j=@y1 AND v= 0)
			BEGIN 
				SET @step=@step+1
				UPDATE mt_board SET v=@step WHERE i=@x1 AND j=@y1
				IF @no<1
				BEGIN 
					  EXEC dbo.sp_travel @step,@no,@x1,@y1
				END 	
				SET @step=@step-1	  
				UPDATE mt_board SET v=0 WHERE i=@x1 AND j=@y1		
			END 
        END         
		SET @i=@i+1
	END  
END 
GO 

CREATE PROCEDURE sp_mtqp2(@x INT,@y INT)
AS
BEGIN 
	BEGIN TRY
	EXEC sp_init
	EXEC sp_getDirection
	UPDATE mt_board SET v=1 WHERE i=@x AND j=@y 
	EXEC sp_travel 1,0,@x,@y  
	END TRY
	BEGIN CATCH
	END CATCH
	EXEC sp_print
END