ソースコード
with tgt as (SELECT
    LINE_ID AS LINE,
    min(SEAT_NO) AS SEAT_F,
    max(SEAT_NO) AS SEAT_T
FROM
    SEAT_RESERVE
WHERE
    SEAT_NO IN (1, 2, 3)
GROUP BY
    LINE_ID
HAVING
    sum(RSV_STATUS) = 0

UNION ALL

SELECT
    LINE_ID AS LINE,
    min(SEAT_NO) AS SEAT_F,
    max(SEAT_NO) AS SEAT_T
FROM
    SEAT_RESERVE
WHERE
    SEAT_NO IN (2, 3, 4)
GROUP BY
    LINE_ID
HAVING
    sum(RSV_STATUS) = 0
UNION ALL

SELECT
    LINE_ID AS LINE,
    min(SEAT_NO) AS SEAT_F,
    max(SEAT_NO) AS SEAT_T
FROM
    SEAT_RESERVE
WHERE
    SEAT_NO IN (3, 4, 5)
GROUP BY
    LINE_ID
HAVING
    sum(RSV_STATUS) = 0
UNION ALL

SELECT
    LINE_ID AS LINE,
    min(SEAT_NO) AS SEAT_F,
    max(SEAT_NO) AS SEAT_T
FROM
    SEAT_RESERVE
WHERE
    SEAT_NO IN (4, 5, 6)
GROUP BY
    LINE_ID
HAVING
    sum(RSV_STATUS) = 0
UNION ALL

SELECT
    LINE_ID AS LINE,
    min(SEAT_NO) AS SEAT_F,
    max(SEAT_NO) AS SEAT_T
FROM
    SEAT_RESERVE
WHERE
    SEAT_NO IN (5, 6, 7)
GROUP BY
    LINE_ID
HAVING
    sum(RSV_STATUS) = 0
UNION ALL

SELECT
    LINE_ID AS LINE,
    min(SEAT_NO) AS SEAT_F,
    max(SEAT_NO) AS SEAT_T
FROM
    SEAT_RESERVE
WHERE
    SEAT_NO IN (6, 7, 8)
GROUP BY
    LINE_ID
HAVING
    sum(RSV_STATUS) = 0
UNION ALL

SELECT
    LINE_ID AS LINE,
    min(SEAT_NO) AS SEAT_F,
    max(SEAT_NO) AS SEAT_T
FROM
    SEAT_RESERVE
WHERE
    SEAT_NO IN (7, 8, 9)
GROUP BY
    LINE_ID
HAVING
    sum(RSV_STATUS) = 0
UNION ALL

SELECT
    LINE_ID AS LINE,
    min(SEAT_NO) AS SEAT_F,
    max(SEAT_NO) AS SEAT_T
FROM
    SEAT_RESERVE
WHERE
    SEAT_NO IN (8, 9, 10)
GROUP BY
    LINE_ID
HAVING
    sum(RSV_STATUS) = 0
UNION ALL

SELECT
    LINE_ID AS LINE,
    min(SEAT_NO) AS SEAT_F,
    max(SEAT_NO) AS SEAT_T
FROM
    SEAT_RESERVE
WHERE
    SEAT_NO IN (9, 10, 11)
GROUP BY
    LINE_ID
HAVING
    sum(RSV_STATUS) = 0
UNION ALL

SELECT
    LINE_ID AS LINE,
    min(SEAT_NO) AS SEAT_F,
    max(SEAT_NO) AS SEAT_T
FROM
    SEAT_RESERVE
WHERE
    SEAT_NO IN (10, 11, 12)
GROUP BY
    LINE_ID
HAVING
    sum(RSV_STATUS) = 0
UNION ALL

SELECT
    LINE_ID AS LINE,
    min(SEAT_NO) AS SEAT_F,
    max(SEAT_NO) AS SEAT_T
FROM
    SEAT_RESERVE
WHERE
    SEAT_NO IN (11, 12, 13)
GROUP BY
    LINE_ID
HAVING
    sum(RSV_STATUS) = 0
UNION ALL

SELECT
    LINE_ID AS LINE,
    min(SEAT_NO) AS SEAT_F,
    max(SEAT_NO) AS SEAT_T
FROM
    SEAT_RESERVE
WHERE
    SEAT_NO IN (12, 13, 14)
GROUP BY
    LINE_ID
HAVING
    sum(RSV_STATUS) = 0
UNION ALL

SELECT
    LINE_ID AS LINE,
    min(SEAT_NO) AS SEAT_F,
    max(SEAT_NO) AS SEAT_T
FROM
    SEAT_RESERVE
WHERE
    SEAT_NO IN (13, 14, 15)
GROUP BY
    LINE_ID
HAVING
    sum(RSV_STATUS) = 0)
    
    
    select * from tgt
    order by LINE desc,
    SEAT_F
提出情報
提出日時2024/06/21 13:36:15
コンテスト第13回 SQLコンテスト
問題空席検索
受験者suse.leaf
状態 (詳細)AC
(Accepted: 正答)
メモリ使用量100 MB
メッセージ
テストケース(通過数／総数)
3/3
状態
メモリ使用量
データパターン1
AC
82 MB
データパターン2
AC
96 MB
データパターン3
AC
100 MB