Original post:
http://anothermysqldba.blogspot.com/2014/05/mysql-magic-square-4x4-routines-loop.html
我得到了今天突發奇想用魔方玩，認為這可能是一個很好的機會，讓使用MySQL例程，循環和if檢查的一個例子。 所以，如果你不知道的幻方是什麼我已經包含了幾個環節。 它可能會為您節省了谷歌搜索，但其他認為數獨是一個例子。
同樣，這更多的是如何使用的例程和loops和中頻檢查的一個例子，所以我限制了幻方以一個4x4的方形。 我傳遞的值4到例程來說明如何這樣做。 這是一個不必要的步驟，因為我是硬編碼的廣場上4，但無論如何，它的工作原理的例子。
DROP PROCEDURE IF EXISTS magic_sq;
DROP TABLE IF EXISTS `magic_sq` ;
delimiter //
CREATE PROCEDURE magic_sq( N int(11))
BEGIN
DECLARE nXn INT;
DECLARE SQ_SUM INT;
DECLARE _passfail_ INT;
DECLARE min INT;
DECLARE max INT;
-- DRAW THE TEMPLATE FOR THE SQUARE MAX of 8 FOR NOW
DROP TABLE IF EXISTS `magic_sq` ;
CREATE TABLE `magic_sq` (
`xy_id` int(11) NOT NULL AUTO_INCREMENT,
`1` int(11) NULL DEFAULT NULL,
`2` int(11) NULL DEFAULT NULL,
`3` int(11) NULL DEFAULT NULL,
`4` int(11) NULL DEFAULT NULL,
PRIMARY KEY (`xy_id`) ,
UNIQUE KEY `Y1` (`1`),
UNIQUE KEY `Y2` (`2`),
UNIQUE KEY `Y3` (`3`),
UNIQUE KEY `Y4` (`4`)
) ENGINE= MEMORY;
-- n X n
SET @nXn = N + N;
-- SQ_SUM This is the formula for what the total should equal
SET @SQ_SUM = ( N * (POW(N,2) + 1) ) / 2;
-- MIN Value
SET @min=1;
-- MAX Value
SET @max=POW(N,2);
-- BUILD THE SQUARE
WHILE ( @min <= @nXn )
DO
-- TEST VALUES
SET _passfail_ = IF ( (@min + (@min +1) + ( @max - 1) +@max) = @SQ_SUM ,1 , 0 ) ;
-- IF VALID RESULTS THEN SAVE THEM
IF _passfail_ = 1 THEN
INSERT INTO magic_sq VALUES (NULL,@min ,(@min +1),( @max - 1) , @max );
END IF;
-- CONTINUE
SET @min= @min +2;
SET @max= @max -2;
END WHILE;
END//
delimiter ;
現在，我建立了常規，我能得到有效的結果？ 請記住，這是不是所有可能的選項，這可能會奏效。只是選擇924選擇一個4x4正方形存在。
CALL magic_sq(4);
select * from magic_sq;
+-------+------+------+------+------+
| xy_id | 1 | 2 | 3 | 4 |
+-------+------+------+------+------+
| 1 | 1 | 2 | 15 | 16 |
| 2 | 3 | 4 | 13 | 14 |
| 3 | 5 | 6 | 11 | 12 |
| 4 | 7 | 8 | 9 | 10 |
+-------+------+------+------+------+
+-------+---------+
| xy_id | per_row |
+-------+---------+
| 1 | 34 |
| 2 | 34 |
| 3 | 34 |
| 4 | 34 |
+-------+---------+
不！ 他們的工作為行而不是列。 雖然這顯示了如何使用程序，循環和IF語句，示例失敗，它並沒有提供我想要的東西。 因此，我不得不返工插入更多的IF檢查以及掉期幾號的結尾。
DROP PROCEDURE IF EXISTS magic_sq;
DROP TABLE IF EXISTS `magic_sq` ;
delimiter //
CREATE PROCEDURE magic_sq( N int(11))
BEGIN
DECLARE nXn INT;
DECLARE SQ_SUM INT;
DECLARE _passfail_ INT;
DECLARE _io_ INT;
DECLARE min INT;
DECLARE max INT;
-- DRAW THE TEMPLATE FOR THE SQUARE MAX of 8 FOR NOW
DROP TABLE IF EXISTS `magic_sq` ;
CREATE TABLE `magic_sq` (
`xy_id` int(11) NOT NULL AUTO_INCREMENT,
`1` int(11) NULL DEFAULT NULL,
`2` int(11) NULL DEFAULT NULL,
`3` int(11) NULL DEFAULT NULL,
`4` int(11) NULL DEFAULT NULL,
PRIMARY KEY (`xy_id`) ,
UNIQUE KEY `Y1` (`1`),
UNIQUE KEY `Y2` (`2`),
UNIQUE KEY `Y3` (`3`),
UNIQUE KEY `Y4` (`4`)
) ENGINE= MEMORY;
-- n X n
SET @nXn = N + N;
-- SQ_SUM This is the formula for what the total should equal
SET @SQ_SUM = ( N * (POW(N,2) + 1) ) / 2;
-- MIN Value
SET @min=1;
-- MAX Value
SET @max=POW(N,2);
-- insert_options
SET _io_ =0;
-- BUILD THE SQUARE
WHILE ( @min <= @nXn )
DO
-- TEST VALUES
SET _passfail_ = IF ( (@min + (@min +1) + ( @max - 1) +@max) = @SQ_SUM ,1 , 0 ) ;
-- IF VALID RESULTS THEN SAVE THEM
IF _passfail_ = 1 THEN
IF _io_ = 0 THEN
INSERT INTO magic_sq VALUES (NULL,@min ,(@min +1),( @max - 1) , @max );
SET _io_ =1;
ELSEIF _io_ = 1 THEN
INSERT INTO magic_sq VALUES (NULL,( @max - 1),@max , @min , (@min +1) );
SET _io_ =2;
ELSEIF _io_ = 2 THEN
INSERT INTO magic_sq VALUES (NULL,@max ,(@min +1) , ( @max - 1) , @min );
SET _io_ =4;
ELSEIF _io_ = 4 THEN
INSERT INTO magic_sq VALUES (NULL, (@min +1) , @max , @min ,( @max - 1) );
SET _io_ =0;
END IF;
END IF;
-- CONTINUE
SET @min= @min +2;
SET @max= @max -2;
END WHILE;
SELECT @x3y2 := `2` FROM magic_sq WHERE xy_id = 3;
SELECT @x3y3 := `3` FROM magic_sq WHERE xy_id = 3;
SELECT @x4y2 := `2` FROM magic_sq WHERE xy_id = 4;
SELECT @x4y3 := `3` FROM magic_sq WHERE xy_id = 4;
UPDATE magic_sq SET `2` = @x4y3 , `3` = @x4y2 WHERE xy_id = 3;
UPDATE magic_sq SET `2` = @x3y3 , `3` = @x3y2 WHERE xy_id = 4;
select * from magic_sq;
select SUM(`1`),SUM(`2`),SUM(`3`),SUM(`4`) from magic_sq;
select xy_id, SUM(`1` +`2` +`3` + `4`) as per_row from magic_sq GROUP BY xy_id;
END//
delimiter ;
現在工作的呢？
CALL magic_sq(4);
+-------+------+------+------+------+
| xy_id | 1 | 2 | 3 | 4 |
+-------+------+------+------+------+
| 1 | 1 | 2 | 15 | 16 |
| 2 | 13 | 14 | 3 | 4 |
| 3 | 12 | 7 | 10 | 5 |
| 4 | 8 | 11 | 6 | 9 |
+-------+------+------+------+------+
4 rows in set (0.22 sec)
+----------+----------+----------+----------+
| SUM(`1`) | SUM(`2`) | SUM(`3`) | SUM(`4`) |
+----------+----------+----------+----------+
| 34 | 34 | 34 | 34 |
+----------+----------+----------+----------+
1 row in set (0.22 sec)
+-------+---------+
| xy_id | per_row |
+-------+---------+
| 1 | 34 |
| 2 | 34 |
| 3 | 34 |
| 4 | 34 |
+-------+---------+
好吧，我騙了一點只是走動的列，但你的想法。
.jpg&usg=ALkJrhhreWhAySFp7jpneB4pXzdQGxNi-Q){kind=link}