Language detail: SQL
|
number of '+' ratings |
contribution for coverage |
Unsolved challenges
- 文字列で+を表示する (Nested Flatten)
- 年賀はがきの当せん番号 (Nested Flatten)
- 箱詰めパズルの判定 (Nested Flatten)
- 関数やメソッドのソースの平均行数 (Nested Flatten)
- コレクションの実装 (Nested Flatten)
codes
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;
|
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
|
SQL Serverでは範囲コメントは/* ~ */で、入れ子も可能です。
see: /*...*/ (コメント) (Transact-SQL)
1 2 | /* hoge /* piyo */ foo */
SELECT * FROM SomeTable;
|
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
|
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);
|
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
|
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
|
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
|
SQL Server 2008 で確認しました。
集約関数の中で集約関数が使えれば Input_ が要らないのですが、無理なので・・・
= による比較なのに単純 CASE 式を使用していないのは、見やすさを考慮してです。
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
|
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
|
なんでこれに 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
|
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
|
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
|
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
|
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
|
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
|


bleis-tift
#9566()
[
SQL
]
Rating0/0=0.00
SQL Server 2008 で確認しました。
ToBin 以降は補助文字でも対応できるように書いてありますので、もうちょっとがんばればサロゲートペアにも対応可能です。
Rating0/0=0.00-0+
1 reply [ reply ]