【SQL】条件付きで検索しつつROW_NUMBERで全体における連番をふりたい

Question

実現したいこと

post_idで合致したデータ内の連番を、この条件で取得する際に同時に持ってくることは可能でしょうか。

掲示板サイトでよく見られる、コメントの連番を表示し返信も対応したいです。
・idは主キーでオートインクリメント
・henshin_idはどのコメントに返信したか記録
・thread_idはどのスレッド（コメントを投稿しているトピック）のコメントかを記録

通常はthread_idを条件にして、トピックごとのコメントを取得し一覧にして表示しています。
返信コメントがあった場合、thread_idとhenshin_idを条件に検索します。その際、通常のthread_idを条件にして取得されるデータの連番も取得したいです。

postsテーブル

id	henshin_id	thread_id	name	body
1	null	1	田中	1コメント
2	null	1	斎藤	こんにちは
3	2	1	鈴木	>>2さんこん
4	null	1	佐藤	やっほー
5	2	1	飯田	>>2こん
6	null	2	村田	あつい

試したSQL

select ROW_NUMBER() OVER(ORDER BY id ASC) num, name, body 
from posts 
where thread_id = 1 and henshin_id = 2;

Comments

[yambejp]

えたい結果は？
nameやbodyはどこからきたのでしょうか？

[退会済みユーザー]

すみません、テーブルにnameとbodyを入れるのを忘れていました。
where thread_id= 1のみで得られるデータにつけた連番を、where thread_id = 1 and henshin_id = 2で得られる結果でも取りたい感じです。
thread内におけるコメント全体の連番を、返信コメントだけ取得した際にも適応できないかと考えています。

Answer 1

row_numberはwhere後のデータに対してしかできないから入れ子がいいかな

select *
from (
select ROW_NUMBER() OVER(partition by thread_id ORDER BY id ASC) num, name, body, henshin_id, thread_id 
from posts 
where thread_id = 1 /*and henshin_id = 2;*/
) as X
where X.henshin_id = 2

Answer 2

ちょっとよくわかりませんが

create table posts(id int primary key,henshin_id int null,thread_id int,name varchar(20),body varchar(100));
insert into posts values
(1,2,1,'田中','1コメント'),
(2,null,1,'斎藤','こんにちは'),
(3,2,1,'鈴木','>>1さんこん'),
(4,null,1,'佐藤','やっほー'),
(5,2,1,'飯田','>>1こん'),
(6,null,2,'村田','>>1こん');

select *,ROW_NUMBER() OVER(ORDER BY id ASC) as num
from posts 
where thread_id = 1 and henshin_id = 2;

Comments

[退会済みユーザー]

説明が上手くできず申し訳ないです。
where句の条件がthread_id = 1のみのときにつけられる連番を、henshin_idを条件に加えた際にも表示出来たらと考えていまして。

[yambejp]

具体的にほしい結果をテーブルで例示ください

[退会済みユーザー]

ROW_NUMBER() OVER(ORDER BY id ASC) num,henshin_id,thread_id,name,bodyです。

[yambejp]

そうじゃなくて・・・
具体的な結果がどういうデータなのか提示くださいということなんですが
こちらが提示したSQLの結果と質問者さんが想定する結果が違うんですよね？