My SQL(MariaDB）同じテーブル同士を結合する

開発　備忘録

2024.06.05

はじめに

+—-+———-+——+——-+————+
| id | order_id | side | price | related_id |
+—-+———-+——+——-+————+
|  1 |        1 | buy  |   100 |          2 |
|  2 |        2 | sell |   150 |       NULL |
|  3 |        3 | buy  |   200 |          4 |
|  4 |        4 | sell |   150 |       NULL |
+—-+———-+——+——-+————+

上のようなテーブルがあったとして、これを購入時のカラムと売却時のカラムからなるように、抽出したい。

+—-+——–+———–+———+————+
| id | buy_id | buy_price | sell_id | sell_price |
+—-+——–+———–+———+————+
|  1 |      1 |       100 |       2 |        150 |
|  3 |      3 |       200 |       4 |        150 |
+—-+——–+———–+———+————+

仮想通貨や株の売買の管理に使うことを想定しています。

テーブルの作成

create table test01(id int auto_increment,order_id bigint, side varchar(16), price int, related_id int, index(id));

id,order_id,side,price,retated_id のカラムからなるテーブルを作成します。
idはオートインクリメントを設定し、自動的に採番されるようにします。order_id は注文idです。sideは売りか買いの区分、priceは価格です。
related_id　関連idですが、これが重要です。buy と sell 2つのレコードがあったとして、buyのrelated_idに sell のidを入れておくことで、buyのレコードとsellのレコードを結合することが出来るようになります。

データを幾つか挿入してみます。

–挿入
insert into test01 (order_id, side,price) values(1,‘buy’,100);
insert into test01 (order_id, side,price) values(2,‘sell’,150);

–更新
update test01 set related_id=1 where id=2;

結果を出力します。

select * from test01;

+—-+———-+——+——-+————+
| id | order_id | side | price | related_id |
+—-+———-+——+——-+————+
|  1 |        1 | buy  |   100 |          2 |
|  2 |        2 | sell |   150 |       NULL |
+—-+———-+——+——-+————+

自己結合

–自己結合
 select A.id,A.order_id,A.side,A.price,B.order_id,B.side,B.price 
 from test01 as A left join test01 as B on A.related_id = B.id;

from以下で　test01というテーブルに対して A B という別名をつけてます。
A.related_id と一致する B.idを探して結合します。Aを左、Bを右に配置します。

/*結果
+—-+———-+——+——-+———-+——+——-+
| id | order_id | side | price | order_id | side | price |
+—-+———-+——+——-+———-+——+——-+
|  1 |        1 | buy  |   100 |        2 | sell |   150 |
|  2 |        2 | sell |   150 |     NULL | NULL |  NULL |
+—-+———-+——+——-+———-+——+——-+
*/

NULLがでてくるのが嫌なので、NULLの行を消します。
where B.order_id is not null
で、右側 B.order_id　が　nullの行を出力しません。

select A.id,A.order_id,A.side,A.price,B.order_id,B.side,B.price 
from test01 as A left join test01 as B on A.related_id = B.id where B.order_id is not null;

/*結果
+—-+———-+——+——-+———-+——+——-+
| id | order_id | side | price | order_id | side | price |
+—-+———-+——+——-+———-+——+——-+
|  1 |        1 | buy  |   100 |        2 | sell |   150 |
+—-+———-+——+——-+———-+——+——-+
*/

同じカラムがあって分かりにくいので、左側と右側のカラムに別名を付けます。

select A.id,A.side,A.order_id as buy_id,A.price as buy_price,
B.order_id as sell_id,B.price as sell_price 
from test01 as A left join test01 as B on A.related_id = B.id where B.order_id is not null;

A　sideはBuy 位置で言うと左側の A.order_id を buy_idに A.priceを buy_priceに
B.order_idを sell_idに B.priceをsell_priceに別名定義します。

/*結果
+—-+——+——–+———–+———+————+
| id | side | buy_id | buy_price | sell_id | sell_price |
+—-+——+——–+———–+———+————+
|  1 | buy  |      1 |       100 |       2 |        150 |
+—-+——+——–+———–+———+————+
*/

SQL関係でよく見るサイト

MySQLの使い方

MySQL は世界中で広く利用されているリレーショナルデータベース管理システム（RDBMS）の一つです。ここでは MySQL 入門として MySQL をこれから使われる方を対象として MySQL をローカル環境にインストールする方法や、 MySQL でデータベース、テーブル、ユーザー、トリガなどを作成する方法に関して、...