[SQL] テーブル結合(２)

テーブルを結合して何かするのは難しいです。

CREATE SEQUENCE my_seq_01
 START WITH     1000
 INCREMENT BY   1
 NOCACHE
 NOCYCLE;
 
DROP TABLE AAA_TB_A;
DROP TABLE AAA_TB_B;

-- 同じ構成で２つのテーブルを作る
CREATE TABLE AAA_TB_A (
    ID VARCHAR2(20),
    TYPE VARCHAR2(20)
);
CREATE TABLE AAA_TB_B (
    CID number(20),
    ID VARCHAR2(20),
    TYPE VARCHAR2(20)
);
select * from AAA_TB_A;
select * from AAA_TB_B;

delete from AAA_TB_A;
delete from AAA_TB_B;

INSERT INTO AAA_TB_A values('A001', 'TYPE1');
INSERT INTO AAA_TB_A values('A002', 'TYPE2');
INSERT INTO AAA_TB_A values('A003', 'TYPE3');

INSERT INTO AAA_TB_B values(my_seq_01.NEXTVAL,'B001a', 'TYPE1');
INSERT INTO AAA_TB_B values(my_seq_01.NEXTVAL,'B001b', 'TYPE1');
INSERT INTO AAA_TB_B values(my_seq_01.NEXTVAL,'B001c', 'TYPE1');
INSERT INTO AAA_TB_B values(my_seq_01.NEXTVAL,'B002a', 'TYPE2');
INSERT INTO AAA_TB_B values(my_seq_01.NEXTVAL,'B002b', 'TYPE2');
INSERT INTO AAA_TB_B values(my_seq_01.NEXTVAL,'B004a', 'TYPE4');

-- CIDのMAX
select tba.*, tbb.*, MAX(tbb.CID) over() from AAA_TB_A tba, AAA_TB_B tbb where tba.TYPE = tbb.TYPE;
-- CIDをtypeでグルーピングしたMAX
select tba.*, tbb.*, MAX(tbb.CID) over(partition by tbb.type) from AAA_TB_A tba, AAA_TB_B tbb where tba.TYPE = tbb.TYPE;

-- 以降は今回やりたいこととは関係ない
select * from AAA_TB_A tba INNER JOIN AAA_TB_B tbb on tba.TYPE = tbb.TYPE;
-- OUTER JOIN はどちらかにのテーブルを基準に無い列NULLにするイメージ
select * from AAA_TB_A tba LEFT OUTER JOIN AAA_TB_B tbb on tba.TYPE = tbb.TYPE;
select * from AAA_TB_A tba RIGHT OUTER JOIN AAA_TB_B tbb on tba.TYPE = tbb.TYPE;