次の行から前の行を引いた値が全て正の場合のみ抽出したい

CREATE TABLE IF NOT EXISTS t1 (
id int(11) NOT NULL,
name,
PRIMARY KEY(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO t1 (id, name) VALUES (1, 'a');
INSERT INTO t1 (id, name) VALUES (2, 'b');
INSERT INTO t1 (id, name) VALUES (3, 'c');
INSERT INTO t1 (id, name) VALUES (4, 'd');

CREATE TABLE IF NOT EXISTS t2 (
id int(11) NOT NULL,
t1_id INT,
price INT,
PRIMARY KEY(id),
FOREIGN KEY (t1_id) REFERENCES t1(id) on update cascade on delete cascade
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO t2 (id, t1_id, price) VALUES (1, 1, 10);
INSERT INTO t2 (id, t1_id, price) VALUES (2, 1, 50);
INSERT INTO t2 (id, t1_id, price) VALUES (3, 1, 30);
INSERT INTO t2 (id, t1_id, price) VALUES (4, 1, 100);
INSERT INTO t2 (id, t1_id, price) VALUES (5, 2, 1000);
INSERT INTO t2 (id, t1_id, price) VALUES (6, 2, 3000);
INSERT INTO t2 (id, t1_id, price) VALUES (7, 3, 500);

このようなテーブル構造で、
t2テーブルの同じt1_idの次のpriceから前のpriceを引いた値が全て正
のt1_idを抽出したいのですが、どのようなSQL文にすれば良いのでしょうか？
例えば、
t1が1のidだと
50-10=40で正なのでまずはOK
30-50=-20で負なのでNG
t1が2のidだと
3000-1000=2000で正なのでOK　→　抽出へ
t1が3のidは
1つしかないのでNG
t1が4のidは
t2に行が存在しないのでNG

質問者からの補足コメント

• テーブルt1は
  CREATE TABLE IF NOT EXISTS t1 (
  id int(11) NOT NULL,
  name VARCHAR(1),
  PRIMARY KEY(id)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  でした。
  訂正します。

    補足日時：2020/12/21 19:06

No.3 回答者： yambejp 回答日時： 2020/12/21 19:31

普通に応用してください

select t_1.*,t_3.id,t_3.price-t_1.price as diff from t2 as t_1
left join(
select *,(select count(*)+1 from t2 where t1_id=t_2.t1_id and id<t_2.id) as rank from t2 as t_2
) as t_3 on t_1.t1_id=t_3.t1_id and (select count(*)+1 from t2 where t1_id=t_1.t1_id and id<t_1.id)=t_3.rank-1
where t_3.price>t_1.price

この回答へのお礼

ご回答ありがとうございます。
何度もすみません、たしかに
＞ where t_3.price>t_1.price
で各々の正の結果だけ抽出できたのですが、
そうするとt1テーブルのidが2の行だけを取得するという目的が達成できなくなります・・・
つまりt1テーブルのidが1の行も取得されてしまうのです。
ここからどのように応用すれば「t1テーブルのidが2」の行だけ

id　t1_id　price　id　diff
5　2　1000　6　2000

だけを取得できるのでしょうか？

お礼日時：2020/12/21 21:04

No.2 回答者： yambejp 回答日時： 2020/12/21 17:01

たとえばこう

CREATE TABLE t2 (
id int(11) NOT NULL primary key,
t1_id INT,
price INT
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO t2 (id, t1_id, price) VALUES
(1, 1, 10),
(2, 1, 50),
(3, 1, 30),
(4, 1, 100),
(5, 2, 1000),
(6, 2, 3000),
(7, 3, 500);

select t_1.*,t_3.id,t_1.price-t_3.price as diff from t2 as t_1
left join(
select *,(select count(*)+1 from t2 where t1_id=t_2.t1_id and id<t_2.id) as rank from t2 as t_2
) as t_3 on t_1.t1_id=t_3.t1_id and (select count(*)+1 from t2 where t1_id=t_1.t1_id and id<t_1.id)=t_3.rank+1

この回答へのお礼

ご回答ありがとうございます。
大変申し訳ございません、
yambejpさんのご回答で今気づいたのですが、
自分の中では
「この条件に合致するt1テーブルの行を取得したい」
と考えていたのですが、
＞t1_idを抽出したいのですが
と伝えてしまって、せっかくご回答頂いたのですが、
自分が希望する結果と違うものになってしまいました・・・
例えばこの場合だと、合致するt1テーブルの行はidが2の行なので
その行を取得したいのです。
直した質問です。

条件に合致するt1テーブルの行を取得したい。
その条件とは、
t2テーブルの同じt1_idで
t2テーブルのid順にidの大きいpriceからidの小さいpriceを引いた値が
それぞれの区間で全て正の場合のt1テーブルの行を取得する。
例えば、
t1が1のidだと
50-10=40で正なのでまずはOK
30-50=-20で負なのでNG
t1が2のidだと
3000-1000=2000で正なのでOK　→　このt1_idに該当するt1テーブルのidが2の行を取得へ
t1が3のidは
1つしかないのでNG
t1が4のidは
t2に行が存在しないのでNG

お手数ではございますが、もう一度この条件で
ご回答いただけないでしょうか？
よろしくお願いいたします。

お礼日時：2020/12/21 19:24

No.1 回答者： yambejp 回答日時： 2020/12/21 14:11

SQLに前後のデータという考え方はありません。

もしやるなら「t2のid順」にというルールが必要になります。
また先頭ので０他は前のpriceが存在しないので例外処理が必要です

この回答へのお礼

ご返信ありがとうございます。
なるほど、ではt2のid順で質問の条件での
SQL文を教えて頂けないでしょうか？
よろしくお願いいたします。

お礼日時：2020/12/21 15:43