[SQL] テーブル結合(2)
テーブルを結合して何かするのは難しいです。
CREATE SEQUENCE my_seq_01
START WITH 1000
INCREMENT BY 1
NOCACHE
NOCYCLE;
DROP TABLE AAA_TB_A;
DROP TABLE AAA_TB_B;
-- 同じ構成で2つのテーブルを作る
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;