Skip to content

Instantly share code, notes, and snippets.

@brokendish

brokendish/XML正規化パターン.sql

Created January 13, 2014 14:05
Show Gist options
  • Save brokendish/8400839 to your computer and use it in GitHub Desktop.
Save brokendish/8400839 to your computer and use it in GitHub Desktop.
Download ZIP
XML正規化パターン パターン４〜１まで、、１は作成中
Raw
XML正規化パターン.sql
#------------------------------------------------------------------------------------
# パターン ４
#------------------------------------------------------------------------------------
create database xml_normalize;
use xml_normalize
drop table AA;
drop table BB;
#------------------------------------------------
create temporary table AA(
key1 varchar(20),
OBJ varchar(2000),
A1 varchar(20)
);
insert into AA values('1','XXXXXXX','ABC');
#------------------------------------------------
create temporary table BB(
key1 varchar(20),
B1 varchar(20),
B2 varchar(20),
B3 varchar(20),
B4 varchar(20),
B5 varchar(20),
B6 varchar(20),
B7 varchar(20),
B8 varchar(20)
);
insert into BB values('1','A11','A12','A13','A14','A15','A16','A17','A18');
insert into BB values('1','A21','A22','A23','A24','A25','A26','A27','A28');
insert into BB values('2','B11','B12','B13','B14','B15','B16','B17','B18');
insert into BB values('2','B21','B22','B23','B24','B25','B26','B27','B28');
#**************************************************************************
#結果表示
#**************************************************************************
select AA.A1,BB.B1,BB.B2,BB.B3,BB.B4,BB.B5,BB.B6,BB.B7,BB.B8
from BB cross join AA;
+------+------+------+------+------+------+------+------+------+
| A1 | B1 | B2 | B3 | B4 | B5 | B6 | B7 | B8 |
+------+------+------+------+------+------+------+------+------+
| ABC | A11 | A12 | A13 | A14 | A15 | A16 | A17 | A18 |
| ABC | A21 | A22 | A23 | A24 | A25 | A26 | A27 | A28 |
| ABC | B11 | B12 | B13 | B14 | B15 | B16 | B17 | B18 |
| ABC | B21 | B22 | B23 | B24 | B25 | B26 | B27 | B28 |
+------+------+------+------+------+------+------+------+------+
4 rows in set (0.00 sec)
#------------------------------------------------------------------------------------
# パターン ３
#------------------------------------------------------------------------------------
create database xml_normalize;
use xml_normalize
#------------------------------------------------
drop table AA;
create temporary table AA(
key1 varchar(20),
A1 varchar(20),
OBJ varchar(2000),
A2 varchar(20),
A3 varchar(20)
);
insert into AA values('1','A000','XXXXXXX','B000','C000');
#------------------------------------------------
drop table BB;
create temporary table BB(
key1 varchar(20),
B1 varchar(20),
B2 varchar(20),
B3 varchar(20),
B4 varchar(20),
B5 varchar(20),
OBJ varchar(2000)
);
insert into BB values('1','a11','a12','a13','a14','a15','XXXXXXX');
insert into BB values('2','b21','b22','b23','b24','b25','XXXXXXX');
insert into BB values('3','c11','c12','c13','c14','c15','XXXXXXX');
#------------------------------------------------
drop table CC;
create temporary table CC(
key1 varchar(20),
C1 varchar(20),
C2 varchar(20),
C3 varchar(20),
C4 varchar(20),
C5 varchar(20),
C6 varchar(20),
C7 varchar(20),
C8 varchar(20)
);
insert into CC values('1','A11','A12','A13','A14','A15','A16','A17','A18');
insert into CC values('1','A21','A22','A23','A24','A25','A26','A27','A28');
insert into CC values('2','B11','B12','B13','B14','B15','B16','B17','B18');
insert into CC values('2','B21','B22','B23','B24','B25','B26','B27','B28');
insert into CC values('3','C11','C12','C13','C14','C15','C16','C17','C18');
insert into CC values('3','C21','C22','C23','C24','C25','C26','C27','C28');
#**************************************************************************
#結果表示
#**************************************************************************
select AA.A1,BB.B1,BB.B2,BB.B3,BB.B4,BB.B5,CC.C1,CC.C2,CC.C3,CC.C4,CC.C5,CC.C6,CC.C7,CC.C8,AA.A2,AA.A3
from (BB left outer join CC
on BB.key1=CC.key1) cross join AA;
+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+
| A1 | B1 | B2 | B3 | B4 | B5 | C1 | C2 | C3 | C4 | C5 | C6 | C7 | C8 | A2 | A3 |
+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+
| A000 | a11 | a12 | a13 | a14 | a15 | A11 | A12 | A13 | A14 | A15 | A16 | A17 | A18 | B000 | C000 |
| A000 | a11 | a12 | a13 | a14 | a15 | A21 | A22 | A23 | A24 | A25 | A26 | A27 | A28 | B000 | C000 |
| A000 | b21 | b22 | b23 | b24 | b25 | B11 | B12 | B13 | B14 | B15 | B16 | B17 | B18 | B000 | C000 |
| A000 | b21 | b22 | b23 | b24 | b25 | B21 | B22 | B23 | B24 | B25 | B26 | B27 | B28 | B000 | C000 |
| A000 | c11 | c12 | c13 | c14 | c15 | C11 | C12 | C13 | C14 | C15 | C16 | C17 | C18 | B000 | C000 |
| A000 | c11 | c12 | c13 | c14 | c15 | C21 | C22 | C23 | C24 | C25 | C26 | C27 | C28 | B000 | C000 |
+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+
6 rows in set (0.00 sec)
#------------------------------------------------------------------------------------
# パターン ２
#------------------------------------------------------------------------------------
create database xml_normalize;
use xml_normalize
#------------------------------------------------
drop table BB;
create temporary table BB(
key1 varchar(20),
B1 varchar(20),
B2 varchar(20),
B3 varchar(20),
B4 varchar(20),
B5 varchar(20),
OBJ varchar(2000)
);
insert into BB values('1','a11','a12','a13','a14','a15','XXXXXXX');
insert into BB values('1','aa1','aa2','aa3','a14','a15','XXXXXXX');
insert into BB values('2','b21','b22','b23','b24','b25','XXXXXXX');
insert into BB values('3','c11','c12','c13','c14','c15','XXXXXXX');
#------------------------------------------------
drop table CC;
create temporary table CC(
key1 varchar(20),
key2 varchar(20),
C1 varchar(20),
C2 varchar(20),
OBJ varchar(2000)
);
insert into CC values('1','1','A11','A12','XXXXXXX');
insert into CC values('1','2','A21','A22','XXXXXXX');
insert into CC values('2','1','B11','B12','XXXXXXX');
insert into CC values('2','2','B21','B22','XXXXXXX');
insert into CC values('3','1','C11','C12','XXXXXXX');
insert into CC values('3','2','C21','C22','XXXXXXX');
#------------------------------------------------
drop table DD;
create temporary table DD(
key1 varchar(20),
key2 varchar(20),
D1 varchar(20),
D2 varchar(20),
D3 varchar(20),
D4 varchar(20),
D5 varchar(20),
D6 varchar(20)
);
insert into DD values('1','1','A11','A12','A13','A14','A15','A16');
insert into DD values('1','1','A21','A22','A23','A24','A25','A26');
insert into DD values('1','1','A31','A32','A33','A34','A35','A36');
insert into DD values('1','1','A41','A42','A43','A44','A45','A46');
insert into DD values('2','1','B11','B12','B13','B14','B15','B16');
insert into DD values('2','1','B21','B22','B23','B24','B25','B26');
insert into DD values('2','1','B31','B32','B33','B34','B35','B36');
insert into DD values('3','1','C11','C12','C13','C14','C15','C16');
insert into DD values('3','1','C21','C22','C23','C24','C25','C26');
#**************************************************************************
#結果表示
#**************************************************************************
select BB.B1,BB.B2,BB.B3,BB.B4,BB.B5,CC.C1,CC.C2,DD.D1,DD.D2,DD.D3,DD.D4,DD.D5,DD.D6
from (BB left outer join (CC left outer join DD
on CC.key1=DD.key1
and CC.key2=DD.key2)
on BB.key1=CC.key1
);
+------+------+------+------+------+------+------+------+------+------+------+------+------+
| B1 | B2 | B3 | B4 | B5 | C1 | C2 | D1 | D2 | D3 | D4 | D5 | D6 |
+------+------+------+------+------+------+------+------+------+------+------+------+------+
| a11 | a12 | a13 | a14 | a15 | A11 | A12 | A11 | A12 | A13 | A14 | A15 | A16 |
| a11 | a12 | a13 | a14 | a15 | A11 | A12 | A21 | A22 | A23 | A24 | A25 | A26 |
| a11 | a12 | a13 | a14 | a15 | A11 | A12 | A31 | A32 | A33 | A34 | A35 | A36 |
| a11 | a12 | a13 | a14 | a15 | A11 | A12 | A41 | A42 | A43 | A44 | A45 | A46 |
| a11 | a12 | a13 | a14 | a15 | A21 | A22 | NULL | NULL | NULL | NULL | NULL | NULL |
| aa1 | aa2 | aa3 | a14 | a15 | A11 | A12 | A11 | A12 | A13 | A14 | A15 | A16 |
| aa1 | aa2 | aa3 | a14 | a15 | A11 | A12 | A21 | A22 | A23 | A24 | A25 | A26 |
| aa1 | aa2 | aa3 | a14 | a15 | A11 | A12 | A31 | A32 | A33 | A34 | A35 | A36 |
| aa1 | aa2 | aa3 | a14 | a15 | A11 | A12 | A41 | A42 | A43 | A44 | A45 | A46 |
| aa1 | aa2 | aa3 | a14 | a15 | A21 | A22 | NULL | NULL | NULL | NULL | NULL | NULL |
| b21 | b22 | b23 | b24 | b25 | B11 | B12 | B11 | B12 | B13 | B14 | B15 | B16 |
| b21 | b22 | b23 | b24 | b25 | B11 | B12 | B21 | B22 | B23 | B24 | B25 | B26 |
| b21 | b22 | b23 | b24 | b25 | B11 | B12 | B31 | B32 | B33 | B34 | B35 | B36 |
| b21 | b22 | b23 | b24 | b25 | B21 | B22 | NULL | NULL | NULL | NULL | NULL | NULL |
| c11 | c12 | c13 | c14 | c15 | C11 | C12 | C11 | C12 | C13 | C14 | C15 | C16 |
| c11 | c12 | c13 | c14 | c15 | C11 | C12 | C21 | C22 | C23 | C24 | C25 | C26 |
| c11 | c12 | c13 | c14 | c15 | C21 | C22 | NULL | NULL | NULL | NULL | NULL | NULL |
+------+------+------+------+------+------+------+------+------+------+------+------+------+
17 rows in set (0.00 sec)
#------------------------------------------------------------------------------------
# パターン １
#------------------------------------------------------------------------------------
create database xml_normalize;
use xml_normalize
#------------------------------------------------
drop table AA;
create temporary table AA(
key1 varchar(20),
A1 varchar(20),
OBJ varchar(2000),
A2 varchar(20),
A3 varchar(20)
);
insert into AA values('1','A01','XXXXXXX','A02','A03');
#------------------------------------------------
drop table BB;
create temporary table BB(
key1 varchar(20),
B1 varchar(20),
B2 varchar(20),
B3 varchar(20),
B4 varchar(20),
B5 varchar(20),
B6 varchar(20),
OBJ varchar(2000)
);
insert into BB values('1','B11','B12','B13','B14','B15','B16','XXXXXXX');
insert into BB values('2','B21','B22','B23','B24','B25','B26','XXXXXXX');
insert into BB values('3','B31','B12','B13','B14','B15','B36','XXXXXXX');
#------------------------------------------------
drop table CC;
create temporary table CC(
key1 varchar(20),
key2 varchar(20),
C1 varchar(20),
C2 varchar(20),
C3 varchar(20),
C4 varchar(20),
OBJ varchar(2000),
C5 varchar(20),
C6 varchar(20),
C7 varchar(20),
C8 varchar(20),
C9 varchar(20)
);
insert into CC values('1','1','C11','C12','C13','C14','XXXXXXX','C15','C16','C17','C18','C19');
insert into CC values('1','2','c11','c12','c13','c14','XXXXXXX','c15','c16','c17','c18','c19');
insert into CC values('2','1','C21','C22','C23','C24','XXXXXXX','C25','C26','C27','C28','C29');
insert into CC values('2','2','c21','c22','c23','c24','XXXXXXX','c25','c26','c27','c28','c29');
insert into CC values('3','1','C31','C32','C33','C34','XXXXXXX','C35','C36','C37','C38','C39');
insert into CC values('3','2','c31','c32','c33','c34','XXXXXXX','c35','c36','c37','c38','c39');
#------------------------------------------------
drop table DD;
create temporary table DD(
key1 varchar(20),
key2 varchar(20),
D1 varchar(20),
D2 varchar(20),
D3 varchar(20),
D4 varchar(20),
D5 varchar(20),
D6 varchar(20)
);
insert into DD values('1','1','DA11','DA12','DA13','DA14','DA15','DA16');
insert into DD values('1','1','DA21','DA22','DA23','DA24','DA25','DA26');
insert into DD values('1','2','DA31','DA32','DA33','DA34','DA35','DA36');
insert into DD values('1','2','DA41','DA42','DA43','DA44','DA45','DA46');
insert into DD values('2','1','DB11','DB12','DB13','DB14','DB15','DB16');
insert into DD values('2','1','DB21','DB22','DB23','DB24','DB25','DB26');
insert into DD values('2','2','DB31','DB32','DB33','DB34','DB35','DB36');
insert into DD values('3','1','DC11','DC12','DC13','DC14','DC15','DC16');
insert into DD values('3','1','DC21','DC22','DC23','DC24','DC25','DC26');
#------------------------------------------------
drop table EE;
create temporary table EE(
key1 varchar(20),
key2 varchar(20),
E1 varchar(20),
E2 varchar(20),
E3 varchar(20),
E4 varchar(20),
E5 varchar(20),
E6 varchar(20)
);
insert into EE values('1','1','E11','e12','ee13','e014','Ee15','EE16');
insert into EE values('1','1','E21','e22','ee23','e024','Ee25','EE26');
insert into EE values('1','2','E31','e32','ee33','e034','Ee35','EE36');
insert into EE values('1','2','E41','e42','ee43','e044','Ee45','EE46');
insert into EE values('2','1','E51','e52','ee53','e054','Ee55','EE56');
insert into EE values('2','1','E61','e62','ee63','e064','Ee65','EE66');
insert into EE values('2','2','E71','e72','ee73','e074','Ee75','EE76');
insert into EE values('3','1','E81','e82','ee83','e084','Ee85','EE86');
insert into EE values('3','1','E91','e92','ee93','e094','Ee95','EE96');
#**************************************************************************
#結果表示
#**************************************************************************
#select AA.A1,BB.B1,BB.B2,CC.C1,CC.C2,DD.D1,DD.D2 #,EE.E1,EE.E2,AA.A2,AA.A3
select BB.B1,BB.B2,CC.C1,CC.C2,DD.D1,DD.D2,BB.B6 #,EE.E1,EE.E2
from
(BB left outer join (CC left outer join DD
on CC.key1=DD.key1
and CC.key2=DD.key2)
on BB.key1=CC.key1)
# (BB left outer join EE on BB.key1=EE.key1)
;
# cross join AA;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment