08/04/11 13:16:21
>>499
-- (KINGAKUの絶対値毎にIDの逆順でランク付けした値) ≦ (KINGAKUの絶対値毎の＋と－の個数の差) の
-- 行を選択しているつもり。
-- 誰かもっと短くしてくれ。
select id, kingaku from (
select tbl.id, tbl.kingaku, rank() over (partition by abs(tbl.kingaku) order by id desc) r, valid_rows from (
select * from hoge where kingaku >= 0 order by id desc
) tbl join (
select phoge.kingaku, plus_count - nvl(minus_count,0) valid_rows from (
select kingaku, count(*) plus_count from hoge where kingaku >= 0 group by kingaku
) phoge left join (
select kingaku, count(*) minus_count from hoge where kingaku < 0 group by kingaku
) mhoge on (phoge.kingaku = - mhoge.kingaku)
) ct on (tbl.kingaku = ct.kingaku)
) where r <= valid_rows order by id;
-- まあ、SQLだけでどうにかしようなんて考えない方がいいんじゃね？