SQL 自己結合で件数の条件指定

質問

WKテーブルにカラム ID(PK), POSTNO, DATA, FLAGがあります。
POSTID毎にIDで小さい順に並べて2件以上の行にFLAGを1に設定したいです。
以下のようなイメージです。 カンマは実際にはありません。

select * from WK;
ID POSTID DATA FLAG
1 1 aaaa 0
2 1 bbbb 0
3 1 cccc 0 →これを1
4 1 dddd 0 →これを1
5 5 eeee 0
6 5 ffff 0
7 5 gggg 0 →これを1
8 4 hhhh 0
9 4 iiii 0
10 4 jjjj 0 →これを1

まず行単位にPOSTID毎の件数が必要と思い以下のselect文を書いてみました。
更にPOSTID毎に上位2件以外を抽出するにはどうすればいいでしょうか。
またはもっと上手いやり方があればご教示お願いします。

select a.ID, a.POSTID, b.CNT
from WK a,
(select POSTID, ID,
count(POSTID) as CNT
from WK
group by POSTID) b
where b.POSTID = a.POSTID
order by a.POSTID, a.ID;

ID POSTID CNT
1 1 4
2 1 4
3 1 4　この行を抽出したい
4 1 4　この行を抽出したい
8 4 3
9 4 3
10 4 3　この行を抽出したい
5 5 3
6 5 3
7 5 3　この行を抽出したい

回答No.1

yambejp · ベストアンサー

こんな感じでは？

//元データ
create table WK(ID int not null primary key, POSTID int, DATA varchar(20), FLAG tinyint);
insert into WK values(1,1,'aaaa',0),(2,1,'bbbb',0),(3,1,'cccc',0),(4,1,'dddd',0),(5,5,'eeee',0),(6,5,'ffff',0),(7,5,'gggg',0),(8,4,'hhhh',0),(9,4,'iiii',0),(10,4,'jjjj',0);

//アップデート
update WK as t0
inner join (select ID from WK as t1 where
(select count(*) +1 from WK AS t2 WHERE t2.ID < t1.ID and t2.POSTID=t1.POSTID)
>2) as t3 on t0.ID=t3.ID
set FLAG=1
where t3.ID is not null;

//表示
select * from WK;

※考え方
POSTIDごとにID順でランクをつけ、そのランクが2より大きいもののIDをつかって
アップデートする。

お礼

おぉ～出来ました！
update文込みのご回答までいただき感激です。
ランク付けの方法大変参考になります。
SQL道まだまだ精進いたします。
ありがとうございました。