Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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
You can’t perform that action at this time.