paiza.ioでmysql その5

概要

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

参考にしたページ

サンプルコード

WITH RECURSIVE hanoi (tiles, ln, orig, free, dest) AS (
    SELECT  
        3, 0, 
        CAST('Left'   as char(10)),
        CAST('Center' as char(10)),
        CAST('Right'  as char(10))
    UNION ALL
    SELECT 
        r.tiles - 1,
        r.ln * 2 + m.n,
        CASE WHEN n = 0 THEN orig ELSE free END,
        CASE WHEN n = 0 THEN dest ELSE orig END,
        CASE WHEN n = 0 THEN free ELSE dest END
    FROM 
        hanoi r, 
        (SELECT 0 n UNION ALL SELECT 1) m
    WHERE
        r.tiles > 1
),
pile(n, s, m) AS (
    SELECT 
        1, CAST('1-' AS CHAR(100)), MAX(tiles)
    FROM hanoi
    UNION ALL
    SELECT 
        n + 1, CONCAT(s, n + 1, '-'), m
    FROM pile
    WHERE n < m
),
display (step, left_, center_, right_, target) AS (
    SELECT 
        0,
        s,
        CAST('' AS CHAR(100)),
        CAST('' AS CHAR(100)),
        CAST('' AS CHAR(100))
    FROM pile
    WHERE n = m
    UNION ALL
    SELECT
        p.step,
        CASE 
        WHEN p.orig = 'Left'   THEN CAST(REGEXP_REPLACE(left_,   '^[0-9]+-', '') AS CHAR(100))
        WHEN p.dest = 'Left'   THEN CONCAT(p.tile_no, '-', left_)
        ELSE left_
        END,
        CASE 
        WHEN p.orig = 'Center' THEN CAST(REGEXP_REPLACE(center_, '^[0-9]+-', '') AS CHAR(100))
        WHEN p.dest = 'Center' THEN CONCAT(p.tile_no, '-', center_)
        ELSE center_
        END,
        CASE 
        WHEN p.orig = 'Right'  THEN CAST(REGEXP_REPLACE(right_,  '^[0-9]+-', '') AS CHAR(100))
        WHEN p.dest = 'Right'  THEN CONCAT(p.tile_no, '-', right_)
        ELSE right_
        END,
        p.dest
    FROM display d,
        (SELECT POWER(2, tiles - 1) + ln * POWER(2, tiles) step, tiles tile_no, orig, dest, ln FROM hanoi) p
    WHERE d.step + 1 = p.step
)
SELECT
        step,
        LPAD(CONCAT(CASE WHEN target = 'Left'   THEN '*' else '' END, TRIM('-' from left_)),   21, ' ') left_,
        LPAD(CONCAT(CASE WHEN target = 'Center' THEN '*' else '' END, TRIM('-' from center_)), 21, ' ') center_,
        LPAD(CONCAT(CASE WHEN target = 'Right'  THEN '*' else '' END, TRIM('-' from right_)),  21, ' ') right_
FROM display
ORDER BY step;

実行結果

step	left_	center_	right_
0	                1-2-3	                     	                     
1	                  2-3	                     	                   *1
2	                    3	                   *2	                    1
3	                    3	                 *1-2	                     
4	                     	                  1-2	                   *3
5	                   *1	                    2	                    3
6	                    1	                     	                 *2-3
7	                     	                     	               *1-2-3

成果物

以上。