paiza.ioでmysql その2

# 概要
paiza.ioでmysqlやってみた。
sql見つけたので、やってみた。

# 参考にしたページ

https://qiita.com/tlokweng/items/5c1fa7d2ef9170108e88


# サンプルコード

```

set session cte_max_recursion_depth = 2000;

WITH RECURSIVE
msize(size_x, size_y) AS (SELECT 45, 15),
moves(move_x, move_y) AS (SELECT +2,  0 UNION ALL 
    SELECT  0, +2 UNION ALL
    SELECT -2,  0 UNION ALL 
    SELECT  0, -2 UNION ALL
    SELECT NULL, NULL),
cells (id, x, y) AS (SELECT 1, MOD(1, size_x) + 1, CEIL(1 / size_x)
    FROM   msize
    UNION ALL
    SELECT id + 1, MOD(id + 1, size_x) + 1, CEIL((id + 1) / size_x)
    FROM   cells, msize
    WHERE  id + 1 <=  size_x * size_y),
mazedig (x, y, cx, cy, action, path) AS (SELECT x, y, x, y, 0, cast(concat(x, '/', y) as char(4000))
    FROM  (SELECT x, y, ROW_NUMBER() OVER (ORDER BY RAND()) rn
           FROM   cells
           WHERE  MOD(x, 2) = 0 and MOD(y, 2) = 0) c
    WHERE rn = 1
    UNION ALL
    SELECT s.x, s.y, s.cx, s.cy,
        CASE WHEN s.x is null THEN 1 ELSE 0 END,
        CASE WHEN s.x is null THEN path ELSE CONCAT(path, ',', s.x, '/', s.y) END
    FROM msize m, mazedig r, LATERAL ( 
        SELECT i.x, i.y, i.x - (i.x - i.nx) / 2 cx, i.y - (i.y - i.ny) / 2 cy, ROW_NUMBER() OVER (ORDER BY SIGN(x) desc, RAND()) rn
        FROM (
                SELECT r.x + move_x x, r.y + move_y y, r.x nx, r.y ny
                FROM   moves
                WHERE  r.action = 0
                UNION ALL
                SELECT b.x + move_x x, b.y + move_y y, b.x nx, b.y ny
                FROM moves m, (SELECT CAST(REPLACE(REGEXP_SUBSTR(r.path, '[0-9]+[/]', 1, id), '/', '') AS SIGNED) x, CAST(REPLACE(REGEXP_SUBSTR(r.path, '[/][0-9]+', 1, id), '/', '') AS SIGNED) y
                FROM cells
    WHERE id <= (LENGTH(r.path) - LENGTH(REPLACE(r.path, ',', ''))) + 1) b
    WHERE m.move_x IS NOT NULL AND r.action > 0) i
    WHERE (NOT FIND_IN_SET(CONCAT(i.x, '/', i.y), r.path) AND i.x > 1 AND i.y > 1 AND i.x < size_x AND i.y < size_y) OR i.x IS NULL) s
    WHERE s.rn = 1)
    
SELECT MAZE FROM (
    SELECT GROUP_CONCAT(c ORDER BY x SEPARATOR '') maze, y
    FROM (SELECT c.x, c.y,
            CASE WHEN p.x IS NOT NULL THEN ' ' ELSE '#' END c
        FROM cells c
            LEFT JOIN (SELECT  x,  y FROM mazedig UNION SELECT cx, cy FROM mazedig) p
            ON  c.x = p.x AND c.y = p.y) g
    GROUP BY y
    UNION ALL
    SELECT CONCAT('SIZE: ', size_x , 'x', size_y), 0 from msize) m
    ORDER BY y;





```

# 実行結果

```
MAZE
SIZE: 45x15
#############################################
#     #     #   #   # #     #     #   #     #
# ### # ### # # # # # # ### ##### # ####### #
#   # #   # # # # # # #   #       #   #     #
# # ##### # # # # # # # # ####### # # # #####
# #   #   # # #   #   # #   #   # # # # #   #
# ### ##### # ### ##### ### ### # # # # ### #
#   #       # # #     #   #   # # # #       #
### ######### # ##### ### ### # # ##### #####
# #             #       #   #   #     #     #
# ######### ##### ##### ### ######### ##### #
#         #     # #   #   # # #   #   #     #
# ### ######### # # # ### # # # # # ### ### #
#   #           #   #   #   #   #       #   #
#############################################
```

# 成果物

https://paiza.io/projects/2f1tnR9wkz72u69SL9kpSw

以上。