こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

SQLでrow_numberとoverを使って抽出

SQLServer2012で、row_number()とover()を使って抽出を行っています。
以下の様なテーブルがあるとして

-------------------------------------------
ID NO distination expensen rnak flg
-------------------------------------------
A001 001 広尾 1000 1 1
A001 002 品川 1000 2 1
A002 001 大森 500 3 0
A003 001 品川 1500 2 0
A001 003 新宿 2000 4 0
A001 004 渋谷 1200 5 0
A003 002 新宿 3000 4 1
A004 003 広尾 4000 1 0
A005 002 大森 500 3 1
A003 002 品川 1000 2 1
A003 003 池袋 1300 6 0
A005 001 大森 500 3 1


これに対して
row_number()とover()を使って、
(1)IDとflgでグループ化し
(2)flgの降順(”1”が優先)
(3)flgが”1”の場合、rankの降順、NOの昇順
(4)flgが”0”の場合、rankの昇順、NOの昇順
(5)ID毎の連番(行番号)を割り当てる

これを以下に様なID毎の連番で結果を得たい。
row_number ID NO distination expensen rank flg
-------------------------------------------------------
1 A001 002 品川 1000 2 1
2 A001 001 広尾 1000 1 1
3 A001 003 新宿 2000 4 0
4 A001 004 渋谷 1200 5 0
1 A002 001 大森 500 3 0
1 A003 002 新宿 3000 4 1
2 A003 002 品川 1000 2 1
3 A003 001 品川 1500 2 0
4 A003 003 池袋 1300 6 0
1 A004 003 広尾 4000 1 0
1 A005 001 大森 500 3 1
2 A005 002 大森 500 3 1

と1回のSQLで抽出したいのですが、SQLを教えて下さい。

投稿日時 - 2014-05-22 12:24:11

QNo.8605857

困ってます

質問者が選んだベストアンサー

ANO1です。
PARTITION BY ID,flg毎に連番がふられるの意味は
これ
↓ rank* ID NO distination expensen rank flg

1 -2 A001 002 品川 1000 2 1
2 -1 A001 001 広尾 1000 1 1
1 4 A001 003 新宿 2000 4 0
2 5 A001 004 渋谷 1200 5 0
1 3 A002 001 大森 500 3 0
1 -4 A003 002 新宿 3000 4 1
2 -2 A003 002 品川 1000 2 1
1 2 A003 001 品川 1500 2 0
2 6 A003 003 池袋 1300 6 0
1 1 A004 003 広尾 4000 1 0
1 -3 A005 001 大森 500 3 1
2 -3 A005 002 大森 500 3 1

2つめのrank*がcase when flg = 1 then -1 else 1 end * rankの結果です。
これ順で、同じならNo順に連番が振られます。
で、
PARTITION BY ID,flg毎にというのは、
IDがA001でflgが、1のデータに対して、1から連番。
IDがA001でflgが、0のデータに対して、1から連番。
IDがA002でflgが、1のデータに対して、1から連番。
・・・
という意味です。

Order by句の中にいれる項目や式は、select句と関係しないので、
以下に様なID毎の連番で結果を得たいのなら、
select句の中に別の記述をすればいいことになります。
ROW_NUMBER() OVER(
PARTITION BY ID
ORDER BY flg desc,
case when flg = 1 then -1 else 1 end * rank,
no)
というような記述でいいはず。

投稿日時 - 2014-05-24 01:13:09

お礼

ありがとうございます。
勉強になります。

投稿日時 - 2014-05-26 08:25:04

このQ&Aは役に立ちましたか?

0人が「このQ&Aが役に立った」と投票しています

回答(2)

ANo.1

select ・・・
order by ID,flg desc,
ROW_NUMBER() OVER(
PARTITION BY ID,flg
ORDER BY case when flg = 1 then -1 else 1 end * rank,no)
で出ると思いますが。


PARTITION BY ID,flg・・・ID,flgごとに連番を取得する。
ORDER BY
flg = 1のとき、-rankの昇順=rankの降順
他 rankの昇順
で、rankが同じ値ならnoの昇順
で、ID,flgごとに1から連番を振られるのこれをorder by句で使えばいい。

投稿日時 - 2014-05-23 01:38:28

お礼

回答ありがとうございます。
スキル不足ですみません。

>ORDER BY case when flg = 1 then -1 else 1 end * rank,no)
の意味が分かりません。

「PARTITION BY ID,flg・・・ID,flgごとに連番を取得する。」
以降の内容がわかりません。
もう少し説明いただけないでしょうか。

投稿日時 - 2014-05-23 08:40:03

あなたにオススメの質問