DouKaku?beta

Language detail: SQL

Coverage: 28.43%
number of '+' ratings
 contribution for coverage

Unsolved challenges

show all

codes

Feed

Used modules

next >>

UTF-16をUTF-8に変換 (Nested Flatten)

SQL Server 2008 で確認しました。

ToBin 以降は補助文字でも対応できるように書いてありますので、もうちょっとがんばればサロゲートペアにも対応可能です。

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
WITH
  Input(id, txt) AS (
    SELECT 1, '00 41 00 42 00 43'
    UNION ALL SELECT 2, '30 42 30 44 30 46'
  )
, Split(id, i, d16, txt) AS (
    SELECT
        id
      , 0
      , LEFT(txt, 2) + SUBSTRING(txt, 4, 2)
      , SUBSTRING(txt, 7, LEN(txt))
    FROM
        Input
    UNION ALL
    SELECT
        id
      , i + 1
      , LEFT(txt, 2) + SUBSTRING(txt, 4, 2)
      , SUBSTRING(txt, 7, LEN(txt))
    FROM
        Split
    WHERE
        LEN(txt) <> 0
  )
, ToNum(id, i, num) AS (
    SELECT
        id
      , i
      , 1 * CAST(SUBSTRING(d16, 4, 1) AS int) +
        16 * CAST(SUBSTRING(d16, 3, 1) AS int) +
        16 * 16 * CAST(SUBSTRING(d16, 2, 1) AS int) +
        16 * 16 * 16 * CAST(SUBSTRING(d16, 1, 1) AS int)
    FROM
        Split
  )
, ToBin(id, i, num, bin, deci, crnt) AS (
    SELECT
        id
      , i
      , num
      , CAST(CASE
             WHEN num >= POWER(CAST(2 AS bigint), 32)
               THEN '1'
               ELSE '0'
             END AS varchar(max))
      , CASE
        WHEN num >= POWER(CAST(2 AS bigint), 32)
          THEN num - POWER(CAST(2 AS bigint), 32)
          ELSE num
        END
      , 31
    FROM
        ToNum
    UNION ALL
    SELECT
        id
      , i
      , num
      , bin + CASE
              WHEN deci >= POWER(CAST(2 AS bigint), crnt)
                THEN '1'
                ELSE '0'
              END
      , CASE
        WHEN deci >= POWER(CAST(2 AS bigint), crnt)
          THEN deci - POWER(CAST(2 AS bigint), crnt)
          ELSE deci
        END
      , crnt - 1
    FROM
        ToBin
    WHERE
        crnt >= 0
  )
, ToUtf8(id, i, utf8) AS (
    SELECT
        id
      , i
      , CASE
        WHEN num BETWEEN 0x0000000 AND 0x0000007F THEN RIGHT(bin, 8)
        WHEN num BETWEEN 0x0000080 AND 0x000007FF THEN '110' + SUBSTRING(bin, 22, 5) + ' 10' + RIGHT(bin, 6)
        WHEN num BETWEEN 0x0000800 AND 0x0000FFFF THEN '1110' + SUBSTRING(bin, 18, 4) + ' 10' + SUBSTRING(bin, 22, 6) + ' 10' + RIGHT(bin, 6)
        WHEN num BETWEEN 0x0010000 AND 0x001FFFFF THEN '11110' + SUBSTRING(bin, 13, 3) + ' 10' + SUBSTRING(bin, 16, 6) + ' 10' + SUBSTRING(bin, 22, 6) + ' 10' + RIGHT(bin, 6)
        WHEN num BETWEEN 0x0200000 AND 0x03FFFFFF THEN '111110' + SUBSTRING(bin, 8, 2) + ' 10' + SUBSTRING(bin, 10, 3) + ' 10' + SUBSTRING(bin, 16, 6) + ' 10' + SUBSTRING(bin, 22, 6) + ' 10' + RIGHT(bin, 6)
        WHEN num BETWEEN 0x4000000 AND 0x7FFFFFFF THEN '1111110' + SUBSTRING(bin, 3, 1) + ' 10' + SUBSTRING(bin, 4, 2) + ' 10' + SUBSTRING(bin, 10, 3) + ' 10' + SUBSTRING(bin, 16, 6) + ' 10' + SUBSTRING(bin, 22, 6) + ' 10' + RIGHT(bin, 6)
        END
    FROM
        ToBin
    WHERE
        crnt = -1
  )
, Result(id, txt, next_i) AS (
    SELECT
        id
      , utf8
      , 1
    FROM
        ToUtf8
    WHERE
        i = 0
    UNION ALL
    SELECT
        R.id
      , txt + ' ' + U8.utf8
      , next_i + 1
    FROM
        Result R
          INNER JOIN ToUtf8 U8 ON R.id = U8.id AND R.next_i = U8.i
  )
SELECT
    id
  , txt
FROM
    Result P
WHERE
    LEN(txt) = (
      SELECT MAX(LEN(txt)) FROM Result C WHERE P.id = C.id
    )
;
文字列中のアルファベットを大文字にする (Nested Flatten)

SQL Server 2008 で確認しました。

1
SELECT UPPER('abc');
自分自身を表示する (Nested Flatten)

SQL Server 2008 で確認しました。 何の面白みもないです・・・

1
WITH Input(s) AS (SELECT 'WITH Input(s) AS (SELECT #_#) SELECT REPLACE(REPLACE(s, CHAR(35), CHAR(39)), CHAR(95), s) FROM Input;') SELECT REPLACE(REPLACE(s, CHAR(35), CHAR(39)), CHAR(95), s) FROM Input;
バイナリクロック (Nested Flatten)

SQL Server 2008 で確認しました。 6 桁固定で、肝心の 2 進数への変換部分は以前投稿した、 http://ja.doukaku.org/comment/9097/ をそのまま使用しました。

see: LL Golf Hole 6 - 10進数を2進数に基数変換する 

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
DECLARE @now AS datetime = GETDATE(); --'20:18';

WITH
  Input(now_str) AS (
    SELECT LEFT(CONVERT(varchar(max), @now, 8), 5)
  )
, Split(id, n, max_bits) AS (
    SELECT 1, LEFT(now_str, 2), 6 FROM Input
    UNION ALL
    SELECT 2, RIGHT(now_str, 2),6 FROM Input
  )
, ToBin(id, bin, deci, crnt) AS (
    SELECT
        id
      , CAST(CASE
             WHEN n >= POWER(CAST(2 AS bigint), max_bits)
                THEN POWER(CAST(10 AS bigint), max_bits)
                ELSE CAST(0 AS bigint)
             END AS bigint)
      , CASE
        WHEN n >= POWER(CAST(2 AS bigint), max_bits)
           THEN n - POWER(CAST(2 AS bigint), max_bits)
           ELSE n
        END
      , max_bits - 1
    FROM
        Split
    UNION ALL
    SELECT
        id
      , CASE
        WHEN deci >= POWER(CAST(2 AS bigint), crnt)
          THEN POWER(CAST(10 AS bigint), crnt)
          ELSE 0
        END + bin
      , CASE
        WHEN deci >= POWER(CAST(2 AS bigint), crnt)
          THEN deci - POWER(CAST(2 AS bigint), crnt)
          ELSE deci
        END
      , crnt - 1
    FROM
        ToBin
    WHERE
        crnt >= 0
  )
SELECT
    REPLACE(
      REPLACE(
        RIGHT(N'00000' + CAST(bin as nvarchar(6)), 6),
        N'0', N'□'
      ),
      N'1', N'■'
    )
FROM
    ToBin
WHERE
    crnt = -1
ORDER BY
    id
シードを固定した乱数 (Nested Flatten)

SQL Server 2008 で確認しました。

1
SELECT RAND(0);
複数行のコメントアウト (Nested Flatten)

SQL Serverでは範囲コメントは/* ～ */で、入れ子も可能です。

System Message: WARNING/2 (<string>, line 1); backlink

Inline emphasis start-string without end-string.

see: /*...*/ (コメント) (Transact-SQL) 

1
2
/* hoge /* piyo */ foo */
SELECT * FROM SomeTable;
税込み価格への修正 (Nested Flatten)

SQL Server 2008 で確認しました。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
WITH
  Input(draft) AS (
    SELECT N'ダイコン150円、ハクサイ120円、ジャガイモ30円'
  )
, Split(i, is_num, elem, draft) AS (
    SELECT
        1
      , CASE WHEN LEFT(draft, 1) LIKE '[0-9]' THEN 1 ELSE 0 END
      , CAST(LEFT(draft, 1) AS nvarchar(max))
      , SUBSTRING(draft, 2, LEN(draft))
    FROM
        Input
    UNION ALL
    SELECT
        CASE
        WHEN LEFT(draft, 1) LIKE '[0-9]' AND is_num = 1
          THEN i
        WHEN LEFT(draft, 1) NOT LIKE '[0-9]' AND is_num = 0
          THEN i
          ELSE i + 1
        END
      , CASE WHEN LEFT(draft, 1) LIKE '[0-9]' THEN 1 ELSE 0 END
      , CASE
        WHEN LEFT(draft, 1) LIKE '[0-9]' AND is_num = 1
          THEN elem + LEFT(draft, 1)
        WHEN LEFT(draft, 1) NOT LIKE '[0-9]' AND is_num = 0
          THEN elem + LEFT(draft, 1)
          ELSE LEFT(draft, 1)
        END
      , SUBSTRING(draft, 2, LEN(draft))
    FROM
        Split
    WHERE
        draft <> ''
  )
, Elems(i, is_num, elem) AS (
    SELECT
        i
      , is_num
      , elem
    FROM
        Split P
    WHERE
        NOT EXISTS(
          SELECT * FROM Split C
          WHERE P.i = C.i AND LEN(P.elem) < LEN(C.elem)
        )
  )
, TaxIn(i, elem) AS (
    SELECT
        i
      , CASE is_num
        WHEN 1 THEN CAST(FLOOR(CAST(elem AS int) * 1.05) AS nvarchar(max))
               ELSE elem
        END
    FROM
        Elems
  )
, [Concat](i, tax_in, max_i) AS (
    SELECT
        0
      , CAST('' AS nvarchar(max))
      , MAX(i)
    FROM
        TaxIn
    UNION ALL
    SELECT
        i + 1
      , tax_in + (SELECT elem FROM TaxIn WHERE TaxIn.i = [Concat].i + 1)
      , max_i
    FROM
        [Concat]
    WHERE
        i <> max_i
  )
, Result(res) AS (
    SELECT tax_in FROM [Concat] WHERE i = max_i
  )
SELECT * FROM Result
リングノードベンチマーク (Nested Flatten)

SQL Server 2008 で確認しました。

へっぽこノートで 20 秒程度かかりました。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
DECLARE @start AS datetime = GETDATE();

DECLARE @results AS table(id int, n int, m int, msg varchar(max));

WITH
  Input(n, msg, m) AS (
    SELECT 1000, 'hoge', 10
  )
, Nodes(id, next_id, n) AS (
    SELECT
        0
      , 1 % n
      , n - 1
    FROM
        Input
    UNION ALL
    SELECT
        id + 1
      , (id + 2) % (id + 1 + n)
      , n - 1
    FROM
        Nodes
    WHERE
        n <> 0
  )
, LoopNodes(id, n, m, msg) AS (
    SELECT
        id
      , Input.n
      , m
      , msg
    FROM
        Nodes
      , Input
    WHERE
        id = 0
    UNION ALL
    SELECT
        Node.id
      , Own.n
      , CASE Node.id WHEN 0 THEN Own.m - 1 ELSE Own.m END
      , Own.msg
    FROM
        LoopNodes Own
          INNER JOIN Nodes Node ON ((Own.id + 1) % Own.n) = Node.id
    WHERE
        Own.m <> 0
  )
INSERT INTO @results
  SELECT * FROM LoopNodes
  OPTION (MAXRECURSION 32767);

SELECT CONVERT(varchar, GETDATE() - @start, 114);
全ての組み合わせ (Nested Flatten)

SQL Server 2008 で確認しました。

SQL では直積は簡単に取れます。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
WITH
  Input(grp, n) AS (
    -- [3, 1, 4, 1, 5, 9, 2, 6, 5]
              SELECT 1, 1 
    UNION ALL SELECT 1, 2 
    UNION ALL SELECT 1, 3 
    UNION ALL SELECT 1, 4 
    UNION ALL SELECT 2, 1 
    UNION ALL SELECT 2, 2 
    UNION ALL SELECT 2, 3 
  )
SELECT
    I1.n
  , I2.n
FROM
    (SELECT * FROM Input WHERE grp = 1) I1
  , (SELECT * FROM Input WHERE grp = 2) I2
文字列型日時ののN秒後時間取得 (Nested Flatten)

SQL Server 2008 で確認しました。

1
2
3
4
5
6
7
8
WITH
  Input(d, n) AS (
    SELECT '2008-8-27 23:59:25', 40
  )
, DateEx(d) AS (
    SELECT DATEADD(S, COALESCE(n, 0), d) FROM Input 
  )
SELECT d FROM DateEx
隣り合う二項の差 (Nested Flatten)

SQL Server 2008 で確認しました。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
WITH
  Input(i, n) AS (
    -- [3, 1, 4, 1, 5, 9, 2, 6, 5]
              SELECT 1, 3 
    UNION ALL SELECT 2, 1 
    UNION ALL SELECT 3, 4 
    UNION ALL SELECT 4, 1 
    UNION ALL SELECT 5, 5 
    UNION ALL SELECT 6, 9 
    UNION ALL SELECT 7, 2 
    UNION ALL SELECT 8, 6 
    UNION ALL SELECT 9, 5
  )
, Diff(i, n) AS (
    SELECT
        L.i
      , R.n - L.n
    FROM
        Input L
          INNER JOIN Input R ON L.i + 1 = R.i
  )
SELECT * FROM Diff ORDER BY i
仲間はずれの判定 (Nested Flatten)

SQL Server 2008 で確認しました。

集約関数の中で集約関数が使えれば Input_ が要らないのですが、無理なので・・・

= による比較なのに単純 CASE 式を使用していないのは、見やすさを考慮してです。

Docutils System Messages

System Message: ERROR/3 (<string>, line 3); backlink

Unknown target name: "input".
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
WITH
  Input(id, i, n) AS (
    -- [1, 1, 1, 1]
              SELECT 1, 1, 1 
    UNION ALL SELECT 1, 2, 1 
    UNION ALL SELECT 1, 3, 1 
    UNION ALL SELECT 1, 4, 1
    -- [1, 2, 1, 1]
    UNION ALL SELECT 2, 1, 1 
    UNION ALL SELECT 2, 2, 2 
    UNION ALL SELECT 2, 3, 1 
    UNION ALL SELECT 2, 4, 1
    -- [1, 2, 3, 1]
    UNION ALL SELECT 3, 1, 1 
    UNION ALL SELECT 3, 2, 2 
    UNION ALL SELECT 3, 3, 3 
    UNION ALL SELECT 3, 4, 1
  )
, Input_(id, i, n, max_n, min_n) AS (
    SELECT
        id
      , i
      , n
      , (SELECT MAX(n) FROM Input C WHERE P.id = C.id)
      , (SELECT MIN(n) FROM Input C WHERE P.id = C.id)
    FROM
        Input P
  )
, [Check](id, majority, minority) AS (
SELECT
      id
    , CASE
      WHEN MAX(n) = MIN(n)
        THEN MAX(n)
      WHEN COUNT(*) - 1 = SUM(CASE n WHEN max_n THEN 1 ELSE 0 END)
        THEN MAX(n)
      WHEN COUNT(*) - 1 = SUM(CASE n WHEN min_n THEN 1 ELSE 0 END)
        THEN MIN(n)
        ELSE NULL
      END
    , CASE
      WHEN COUNT(*) - 1 = SUM(CASE n WHEN max_n THEN 1 ELSE 0 END)
        THEN MIN(n)
      WHEN COUNT(*) - 1 = SUM(CASE n WHEN min_n THEN 1 ELSE 0 END)
        THEN MAX(n)
        ELSE NULL
      END
  FROM
      Input_ P
  GROUP BY
      id
)
SELECT
    id
  , majority
  , minority
FROM
    [Check]
ORDER BY
    id
LL Golf Hole 8 - 横向きのピラミッドを作る (Nested Flatten)

SQL Server 2008 で確認しました。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
WITH
  Input(id, n) AS (
    SELECT 1, 5
  )
, Pyramid(id, i, str, max_len) AS (
    SELECT
        id
      , 1
      , CAST('*' AS varchar(max))
      , n * 2 - 1
    FROM
        Input
    UNION ALL
    SELECT
        id
      , i + 1
      , CAST(REPLICATE('*', CASE
                            WHEN i <= max_len / 2
                              THEN i + 1
                              ELSE max_len - i
                            END) AS varchar(max))
      , max_len
    FROM
        Pyramid
    WHERE
        i < max_len
  )
SELECT
    str
FROM
    Pyramid
ORDER BY
    id
  , i
比較しないソートの作成 (Nested Flatten)

なんでこれに SQL がないんだろ・・・

ということで、SQL Server 2008 で確認しましたが、共通表式が使える RDBMS ならどれでも同じように動くはずです。

共通表式がない RBDMS でも、なんか適当なテーブルにデータを INSERT して取り出すだけです。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
WITH
  Input(n) AS (
    SELECT -1
    UNION ALL SELECT 9
    UNION ALL SELECT 4
    UNION ALL SELECT 8
    UNION ALL SELECT 9
    UNION ALL SELECT 6
    UNION ALL SELECT 3
    UNION ALL SELECT 9
    UNION ALL SELECT 5
    UNION ALL SELECT 2
  )
SELECT
    n
FROM
    Input
ORDER BY
    n
LL Golf Hole 6 - 10進数を2進数に基数変換する (Nested Flatten)

SQL Server 2008 で確認しました。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
WITH
  Input(id, n, max_bits) AS (
    SELECT 1, 100000, 32
  )
, ToBin(id, bin, deci, crnt) AS (
    SELECT
        id
      , CAST(CASE
             WHEN n >= POWER(CAST(2 AS bigint), max_bits)
                THEN POWER(CAST(10 AS bigint), max_bits)
                ELSE CAST(0 AS bigint)
             END AS bigint)
      , CASE
        WHEN n >= POWER(CAST(2 AS bigint), max_bits)
           THEN n - POWER(CAST(2 AS bigint), max_bits)
           ELSE n
        END
      , max_bits - 1
    FROM
        Input
    UNION ALL
    SELECT
        id
      , CASE
        WHEN deci >= POWER(CAST(2 AS bigint), crnt)
          THEN POWER(CAST(10 AS bigint), crnt)
          ELSE 0
        END + bin
      , CASE
        WHEN deci >= POWER(CAST(2 AS bigint), crnt)
          THEN deci - POWER(CAST(2 AS bigint), crnt)
          ELSE deci
        END
      , crnt - 1
    FROM
        ToBin
    WHERE
        crnt >= 0
  )
, Result(id, val) AS (
    SELECT id, bin FROM ToBin WHERE crnt = -1
  )
SELECT * FROM Result ORDER BY id
IPv4アドレスのマスクの変換 (Nested Flatten)

SQL Server 2008 で確認しました。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
WITH
  Input(id, subnetmask) AS (
    SELECT 1, '255.255.255.0'
    UNION ALL SELECT 2, '255.255.255.128'
    UNION ALL SELECT 3, '255.255.255.255'
  )
, Input_(id, subnetmask) AS (
    SELECT id, subnetmask + '.' FROM Input
  )
, ToNums(id, i, element, input) AS (
    SELECT
        id
      , 1
      , CAST(LEFT(subnetmask, CHARINDEX('.', subnetmask, 1) - 1) AS int)
      , SUBSTRING(subnetmask, CHARINDEX('.', subnetmask, 1) + 1, LEN(subnetmask))
    FROM
        Input_
    UNION ALL
    SELECT
        id
      , i + 1
      , CAST(LEFT(input, CHARINDEX('.', input, 1) - 1) AS int)
      , SUBSTRING(input, CHARINDEX('.', input, 1) + 1, LEN(input))
    FROM
        ToNums
    WHERE
        i < 4
  )
, ToBin(id, i, bin, deci, crnt) AS (
    SELECT
        id
      , i
      , CAST(CASE
             WHEN element >= 128 THEN 10000000
                                 ELSE 0
             END AS bigint)
      , CASE
        WHEN element >= 128 THEN element - 128
                            ELSE element
        END
      , 6
    FROM
        ToNums
    UNION ALL
    SELECT
        id
      , i
      , CASE
        WHEN deci >= POWER(2, crnt) THEN POWER(10, crnt)
                                    ELSE 0
        END + bin
      , CASE
        WHEN deci >= POWER(2, crnt) THEN deci - POWER(2, crnt)
                                    ELSE deci
        END
      , crnt - 1
    FROM
        ToBin
    WHERE
        crnt >= 0
  )
, CountOne(id, result) AS (
    SELECT
        id
      , SUM(LEN(REPLACE(STR(bin, 8), '0', '')))
    FROM
        ToBin
    WHERE
        crnt = -1
    GROUP BY
        id
  )
SELECT id, result FROM CountOne
ミリ秒まで含んだ時刻文字列 (Nested Flatten)

SQL Server 2008 で確認しました。

1
SELECT GETDATE()
文字列の反転 (Nested Flatten)

SQL Server 2008 で確認しました。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
WITH
  Input(id, str) AS (
    SELECT 1, N'Hello'
    UNION ALL SELECT 2, N'こんにちは'
    UNION ALL SELECT 3, N'濁点(だくてん)'
  )
, reverse_string(id, str) AS (
    SELECT id, REVERSE(str) FROM Input
  )
SELECT * FROM reverse_string ORDER BY id
与えられた文字列でピラミッド (Nested Flatten)

SQL Server 2008 で確認しました。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
WITH
  Input(id, str) AS (
    SELECT 1, 'hoge'
    UNION ALL SELECT 2, 'abracadabra'
  )
, ExpandsSrc(id, str, input_str) AS (
    SELECT
        id
      , CAST(LEFT(str, 1) + ' ' AS varchar(max))
      , SUBSTRING(str, 2, LEN(str))
    FROM
        Input
    UNION ALL
    SELECT
        id
      , str + LEFT(input_str, 1) + ' '
      , SUBSTRING(input_str, 2, LEN(input_str))
    FROM
        ExpandsSrc
    WHERE
        input_str <> ''
  )
, Expands(id, str) AS (
    SELECT id, str FROM ExpandsSrc WHERE input_str = ''
  )
, Pyramid(id, i, str, input_str) AS (
    SELECT
        id
      , 1
      , str
      , SUBSTRING(str, 3, LEN(str))
    FROM
        Expands
    UNION ALL
    SELECT
        id
      , i + 1
      , SPACE(i) + input_str
      , SUBSTRING(input_str, 3, LEN(input_str))
    FROM
        Pyramid
    WHERE
        input_str <> ''
  )
SELECT str FROM Pyramid
ORDER BY id, i DESC
リストを逆順に表示 (Nested Flatten)

SQL Server 2008 で確認しました。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
WITH
  Input(i, n) AS (
    SELECT 1, 1
    UNION SELECT 2, 2
    UNION SELECT 3, 3
    UNION SELECT 4, 4
    UNION SELECT 5, 5
  )
SELECT
    n
FROM
    Input
ORDER BY
    i DESC
next >>

Index

Feed

Other

Link

Pathtraq

loading...
Creative Commons License このサイトの内容は、 クリエイティブ・コモンズ・ライセンスの下でライセンスされています。 [詳細]