SQL实现扫雷游戏

/*
EXEC sp_start 大小
EXEC sp_pailei 行,列,类型

类型
0取消红旗
1插上红旗
2点开方块
 
--开始
EXEC sp_start 10

--排雷
EXEC sp_pailei 1,1,2
EXEC sp_pailei 5,5,2
EXEC sp_pailei 10,1,2
*/
--初始化
CREATE PROCEDURE sp_start(@n INT)
AS
BEGIN 
	 
	CREATE TABLE ##mypailei
	(
		i INT ,
		j INT ,
		r NVARCHAR(10)  
	)

	CREATE TABLE ##mypailei2
	(
		i INT ,
		j INT ,
		r NVARCHAR(10)  
	)

	;WITH ct
	AS
	(
		SELECT number FROM master..spt_values WHERE type='P' AND number BETWEEN 1 AND @n 
	)
	INSERT into ##mypailei
	SELECT a.number AS i,b.number AS j,CONVERT(VARCHAR(10),'') AS r 
	FROM  ct a ,ct b


	;WITH ct
	AS
	(
		SELECT TOP 20 PERCENT  *  FROM 
		##mypailei 
		ORDER BY NEWID()
	)
	UPDATE ct SET ct.r='●'

	INSERT INTO ##mypailei2  
	SELECT i,j,'█' AS r  FROM ##mypailei 

END 
GO 

--排雷
CREATE PROCEDURE sp_pailei (@i INT,@j INT ,@type CHAR(1))
AS
BEGIN 
	DECLARE @cnt INT 
	DECLARE @row INT
	DECLARE @ii INT 
	DECLARE @jj INT 
	DECLARE @n INT 
	   
	IF OBJECT_ID('tempdb..##mypailei') IS  NULL 
	BEGIN 
		SELECT '请重新开始'
		RETURN 
	END 
	
	SELECT @n =MAX(i) FROM  ##mypailei
	
	IF @type='1'
	BEGIN 
		UPDATE ##mypailei2 SET r='Δ' WHERE i=@i AND j=@j  
		AND r='█' 
	END 
	ELSE IF @type=0
	BEGIN
		UPDATE ##mypailei2 SET r='█' WHERE i=@i AND j=@j AND r='Δ'  
	END 	
	ELSE IF @type='2' 
	BEGIN 
		IF EXISTS(SELECT 1 FROM ##mypailei2 WHERE i=@i AND j=@j AND r<>'█')
		RETURN 
		IF EXISTS(SELECT *  FROM ##mypailei WHERE i=@i AND j=@j AND r='●')
		BEGIN 
			UPDATE ##mypailei2 SET r='*' WHERE i=@i AND j=@j  
		END 	 
		ELSE 
		BEGIN 
			SELECT i,j,IDENTITY(INT,1,1) AS  k 
			INTO ##mypailei3
			FROM ##mypailei WHERE i IN(@i,@i-1,@i+1) AND j IN (@j,@j-1,@j+1) 	
			 
			WHILE @@ROWCOUNT>0
			BEGIN 		
				SELECT TOP 1 @ii=i,@jj=j FROM ##mypailei3
				ORDER BY  k 
 
				SELECT @cnt=COUNT(1) FROM ##mypailei WHERE i IN(@ii,@ii-1,@ii+1) AND j IN (@jj,@jj-1,@jj+1) AND r='●'
				IF @cnt=0
				BEGIN 
					IF EXISTS(SELECT  1 FROM ##mypailei WHERE i=@ii AND  j=@jj AND r<>'●')
					UPDATE ##mypailei2 SET r='' WHERE i=@ii AND  j=@jj AND r='█' 
					INSERT INTO ##mypailei3
					SELECT i,j FROM ##mypailei2 a WHERE i IN(@ii,@ii-1,@ii+1) AND j IN (@jj,@jj-1,@jj+1) AND r='█'
					AND NOT EXISTS(SELECT 1 FROM ##mypailei3 WHERE i=a.i AND j=a.j)
				END 
				ELSE 
				IF EXISTS(SELECT  1 FROM ##mypailei WHERE i=@ii AND  j=@jj AND r<>'●')
					UPDATE ##mypailei2 SET r=@cnt WHERE i=@ii AND  j=@jj AND r='█' 
				
				DELETE FROM ##mypailei3 WHERE i=@ii AND j=@jj 
			END 
			
			DROP TABLE ##mypailei3
		END  			
	END 
	
	EXEC sp_show @n
END 
go

--显示结果 
CREATE  PROCEDURE sp_show(@n INT)
AS
BEGIN 
	DECLARE @SQL VARCHAR(max)=''
	DECLARE @rst BIT = 0
	DECLARE @cnt INT 
	DECLARE @sum INT   
	IF NOT EXISTS(SELECT 1 FROM ##mypailei2 where r='█')
	BEGIN 
		SET @rst=1
	END 
	ELSE IF EXISTS(SELECT 1 FROM ##mypailei2 where r='*')
	BEGIN 
		SET @rst=1
	END 
	
	IF @rst=1
	BEGIN
		UPDATE a SET r=CASE  
			WHEN a.r='Δ' AND b.r='●' THEN '√'
			ELSE '?'
			END
		FROM  ##mypailei2 a INNER JOIN ##mypailei b  ON  a.i = b.i AND a.j=b.j
		WHERE a.r='Δ'
		
		IF EXISTS(SELECT 1 FROM ##mypailei2 where r='*')
			SELECT '你踩雷啦' AS  rst
		ELSE IF EXISTS(SELECT 1 FROM ##mypailei2 where r='?')
		BEGIN 
			SELECT @sum=COUNT(1)  FROM 	##mypailei WHERE r='●'
			SELECT @cnt=COUNT(1)  FROM 	##mypailei2 WHERE r='?'
			SELECT '总共:'+CONVERT(VARCHAR(10),@sum)+'个地雷,标错'+CONVERT(VARCHAR(10),@cnt)+'个'
		END	
	END 
	
	SET @SQL=(
	SELECT ','+QUOTENAME(number)  FROM master..spt_values WHERE type='p' AND  number BETWEEN 1 AND @n
	ORDER  BY number
	FOR XML PATH('')
	)
	
	SET @SQL='
	SELECT *  FROM ##mypailei2 
	PIVOT
	(
		MAX(r)
		FOR  j IN ('+STUFF(@SQL,1,1,'')+')
	) b
	order by  i
	'
	EXEC(@sql)
	
	IF @rst=1
	BEGIN 
		DROP TABLE IF EXISTS ##mypailei
		DROP TABLE IF EXISTS ##mypailei2
	END 
END 
go