XML正規化

XML正規化.sql

**************************************************************************
環境設定（データ準備）
**************************************************************************

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)