この記事は約2分15秒で読むことができます。

sql oracle test


SELECT
    *
FROM
    (SELECT LEVEL AS rn FROM dual CONNECT BY LEVEL <=10)
MODEL
DIMENSION BY (rn)
MEASURES(
            1 AS val_asc
            ,CAST(NULL AS VARCHAR2(4000)) AS ope_asc
            ,row_number () OVER (ORDER BY rn DESC) AS rnn
            ,MAX(rn) OVER () AS val_desc
            ,CAST(NULL AS VARCHAR(4000)) AS ope_desc
            )
RULES(
        val_asc[rn] ORDER BY rn ASC = nvl(val_asc[cv() - 1],1) * cv(rn)
        ,ope_asc[rn] ORDER BY rn ASC = to_char(nvl(ope_asc[cv() - 1],NULL)) || CASE WHEN to_char(nvl(ope_asc[cv() - 1],NULL)) IS NULL THEN NULL ELSE ' * ' END || to_char(cv(rn))
        ,val_desc[rn] ORDER BY rnn DESC = nvl(val_desc[cv() - 1],1) * rnn[cv()]
        ,ope_desc[rn] ORDER BY rnn DESC = to_char(nvl(ope_desc[cv() - 1],NULL)) || CASE WHEN to_char(nvl(ope_desc[cv() - 1],NULL)) IS NULL THEN NULL ELSE ' * ' END || to_char(rnn[cv()])
        )
ORDER BY rn
;

Leave a Reply

Your email address will not be published. Required fields are marked *