これも備忘録として。
想定データ
SELECT
ID,
NAME,
KBN,
PRICE
FROM
TRANSACTION1
;
区分がKBNってダサいよね・・・w
区分(KBN)ごとにまとめる
SELECT
-- ID,
-- NAME,
KBN,
SUM(PRICE),
MAX(PRICE),
MIN(PRICE)
FROM
TRANSACTION1
GROUP BY KBN
ORDER BY KBN
;
区分(KBN)ごとにROW_NUMBERをつける
いつも忘れる「PARTITION BY」。
SELECT
ID,
NAME,
KBN,
PRICE,
ROW_NUMBER() OVER(PARTITION BY KBN ORDER BY PRICE ASC) ROW_NUM
FROM
TRANSACTION1
ORDER BY ID
;
先頭2件だけ取得
SELECT *
FROM(
SELECT
ID,
NAME,
KBN,
PRICE,
ROW_NUMBER() OVER(PARTITION BY KBN ORDER BY PRICE ASC) ROW_NUM
FROM
TRANSACTION1
)
WHERE ROW_NUM <= 2
ORDER BY ID
;
横で取得
SELECT
T1.KBN,
MAX(CASE WHEN T1.ROW_NUM = 1 THEN T1.PRICE END) AS ROW1,
MAX(CASE WHEN T1.ROW_NUM = 2 THEN T1.PRICE END) AS ROW2,
MAX(CASE WHEN T1.ROW_NUM = 3 THEN T1.PRICE END) AS ROW3
FROM (
SELECT
KBN,
PRICE,
ROW_NUMBER() OVER(PARTITION BY KBN ORDER BY PRICE ASC) ROW_NUM
FROM
TRANSACTION1
)T1
GROUP BY T1.KBN
;
ということでちょい適当なSQLになってしまったが、まとめたい情報はまとめたかな。
以上。