Create a gist now

Instantly share code, notes, and snippets.

XML正規化
**************************************************************************
環境設定(データ準備)
**************************************************************************
bkdh@brokendish:~$ mysql -u root
mysql> create database xml_normalize;
mysql> use xml_normalize
Database changed
mysql>
drop table AA;
drop table BB;
drop table CC;
drop table DD;
#------------------------------------------------
create temporary table AA(
key1 varchar(20),
A varchar(20)
);
insert into AA values('1','135');
#------------------------------------------------
create temporary table BB(
key1 varchar(20),
B1 varchar(20),
B2 varchar(20),
OBJ varchar(2000)
);
insert into BB values('1','0123','AAAB','XXXXXXX');
insert into BB values('2','0124','BBBA','XXXXXXX');
#------------------------------------------------
create temporary table CC(
key1 varchar(20),
key2 varchar(20),
C1 varchar(20),
C2 varchar(20),
C3 varchar(20),
OBJ varchar(2000)
);
insert into CC values('1','1','1000','50','1/6','XXXXXXX');
insert into CC values('1','2','1020','40','1/7','XXXXXXX');
insert into CC values('1','3','1030','30','1/8','XXXXXXX');
insert into CC values('2','1','1000','50','1/6','XXXXXXX');
#------------------------------------------------
create temporary table DD(
key1 varchar(20),
key2 varchar(20),
D1 varchar(20)
);
insert into DD values('1','1','AAA01');
insert into DD values('1','1','AAA02');
insert into DD values('1','2','BBB01');
insert into DD values('1','2','BBB02');
insert into DD values('1','3','CCC01');
insert into DD values('1','3','CCC02');
insert into DD values('2','1','XXX01');
insert into DD values('2','1','XXX02');
**************************************************************************
データ表示
**************************************************************************
------------------------------------------------
select * from AA;
+------+------+
| key1 | A |
+------+------+
| 1 | 135 |
+------+------+
1 row in set (0.00 sec)
select * from BB;
+------+------+------+---------+
| key1 | B1 | B2 | OBJ |
+------+------+------+---------+
| 1 | 0123 | AAAB | XXXXXXX |
| 2 | 0124 | BBBA | XXXXXXX |
+------+------+------+---------+
2 rows in set (0.00 sec)
select * from CC;
+------+------+------+------+------+---------+
| key1 | key2 | C1 | C2 | C3 | OBJ |
+------+------+------+------+------+---------+
| 1 | 1 | 1000 | 50 | 1/6 | XXXXXXX |
| 1 | 2 | 1020 | 40 | 1/7 | XXXXXXX |
| 1 | 3 | 1030 | 30 | 1/8 | XXXXXXX |
| 2 | 1 | 1000 | 50 | 1/6 | XXXXXXX |
+------+------+------+------+------+---------+
4 rows in set (0.00 sec)
select * from DD;
+------+------+-------+
| key1 | key2 | D1 |
+------+------+-------+
| 1 | 1 | AAA01 |
| 1 | 1 | AAA02 |
| 1 | 2 | BBB01 |
| 1 | 2 | BBB02 |
| 1 | 3 | CCC01 |
| 1 | 3 | CCC02 |
| 2 | 1 | XXX01 |
| 2 | 1 | XXX02 |
+------+------+-------+
8 rows in set (0.00 sec)
**************************************************************************
結果表示
**************************************************************************
select BB.B1,BB.B2,CC.C1,CC.C2,CC.C3,DD.D1,AA.A
from (BB left join (CC left join DD
on CC.key1=DD.key1
and CC.key2=DD.key2
)
on BB.key1=CC.key1
)
cross join AA;
+------+------+------+------+------+-------+------+
| B1 | B2 | C1 | C2 | C3 | D1 | A |
+------+------+------+------+------+-------+------+
| 0123 | AAAB | 1000 | 50 | 1/6 | AAA01 | 135 |
| 0123 | AAAB | 1000 | 50 | 1/6 | AAA02 | 135 |
| 0123 | AAAB | 1020 | 40 | 1/7 | BBB01 | 135 |
| 0123 | AAAB | 1020 | 40 | 1/7 | BBB02 | 135 |
| 0123 | AAAB | 1030 | 30 | 1/8 | CCC01 | 135 |
| 0123 | AAAB | 1030 | 30 | 1/8 | CCC02 | 135 |
| 0124 | BBBA | 1000 | 50 | 1/6 | XXX01 | 135 |
| 0124 | BBBA | 1000 | 50 | 1/6 | XXX02 | 135 |
+------+------+------+------+------+-------+------+
8 rows in set (0.00 sec)
**************************************************************************
SQLのパーツ検証
**************************************************************************
select * from DD cross join AA;
+------+------+-------+------+------+
| key1 | key2 | D1 | key1 | A |
+------+------+-------+------+------+
| 1 | 1 | AAA01 | 1 | 135 |
| 1 | 1 | AAA02 | 1 | 135 |
| 1 | 2 | BBB01 | 1 | 135 |
| 1 | 2 | BBB02 | 1 | 135 |
| 1 | 3 | CCC01 | 1 | 135 |
| 1 | 3 | CCC02 | 1 | 135 |
| 2 | 1 | XXX01 | 1 | 135 |
| 2 | 1 | XXX02 | 1 | 135 |
+------+------+-------+------+------+
8 rows in set (0.00 sec)
select * from CC left join DD on CC.key1=DD.key1 and CC.key2=DD.key2;
+------+------+------+------+------+---------+------+------+-------+
| key1 | key2 | C1 | C2 | C3 | OBJ | key1 | key2 | D1 |
+------+------+------+------+------+---------+------+------+-------+
| 1 | 1 | 1000 | 50 | 1/6 | XXXXXXX | 1 | 1 | AAA01 |
| 1 | 1 | 1000 | 50 | 1/6 | XXXXXXX | 1 | 1 | AAA02 |
| 1 | 2 | 1020 | 40 | 1/7 | XXXXXXX | 1 | 2 | BBB01 |
| 1 | 2 | 1020 | 40 | 1/7 | XXXXXXX | 1 | 2 | BBB02 |
| 1 | 3 | 1030 | 30 | 1/8 | XXXXXXX | 1 | 3 | CCC01 |
| 1 | 3 | 1030 | 30 | 1/8 | XXXXXXX | 1 | 3 | CCC02 |
| 2 | 1 | 1000 | 50 | 1/6 | XXXXXXX | 2 | 1 | XXX01 |
| 2 | 1 | 1000 | 50 | 1/6 | XXXXXXX | 2 | 1 | XXX02 |
+------+------+------+------+------+---------+------+------+-------+
8 rows in set (0.00 sec)
select * from BB left outer join CC on BB.key1=CC.key1;
+------+------+------+---------+------+------+------+------+------+---------+
| key1 | B1 | B2 | OBJ | key1 | key2 | C1 | C2 | C3 | OBJ |
+------+------+------+---------+------+------+------+------+------+---------+
| 1 | 0123 | AAAB | XXXXXXX | 1 | 1 | 1000 | 50 | 1/6 | XXXXXXX |
| 1 | 0123 | AAAB | XXXXXXX | 1 | 2 | 1020 | 40 | 1/7 | XXXXXXX |
| 1 | 0123 | AAAB | XXXXXXX | 1 | 3 | 1030 | 30 | 1/8 | XXXXXXX |
| 2 | 0124 | BBBA | XXXXXXX | 2 | 1 | 1000 | 50 | 1/6 | XXXXXXX |
+------+------+------+---------+------+------+------+------+------+---------+
4 rows in set (0.00 sec)
select * from DD cross join AA;
+------+------+-------+------+------+
| key1 | key2 | D1 | key1 | A |
+------+------+-------+------+------+
| 1 | 1 | AAA01 | 1 | 135 |
| 1 | 1 | AAA02 | 1 | 135 |
| 1 | 2 | BBB01 | 1 | 135 |
| 1 | 2 | BBB02 | 1 | 135 |
| 1 | 3 | CCC01 | 1 | 135 |
| 1 | 3 | CCC02 | 1 | 135 |
| 2 | 1 | XXX01 | 1 | 135 |
| 2 | 1 | XXX02 | 1 | 135 |
+------+------+-------+------+------+
8 rows in set (0.00 sec)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment