teratail
回答率
85.47%
質問するログイン新規登録
トップMySQL Workbenchに関する質問[SQL] 縦持ちのテーブルから横持ちのデータとして検索する

編集履歴

回答編集履歴

4

調整

2019/09/05 09:31

投稿

yambejp
yambejp

スコア114903

test CHANGED
@@ -212,6 +212,64 @@
212
212
 
213
213
 
214
214
 
215
+ # 実際にprocedureが発行しているSQL文
216
+
217
+ 上記procedureが発行しているSQL文はこうです
218
+
219
+ ```SQL
220
+
221
+ select
222
+
223
+ t1.name as name1,
224
+
225
+ t1.price as price1,
226
+
227
+ t1.bikou as bikou1,
228
+
229
+ t2.name as name1,
230
+
231
+ t2.price as price1,
232
+
233
+ t2.bikou as bikou1,
234
+
235
+ t3.name as name1,
236
+
237
+ t3.price as price1,
238
+
239
+ t3.bikou as bikou1,
240
+
241
+ t4.name as name2,
242
+
243
+ t4.price as price2,
244
+
245
+ t4.bikou as bikou2
246
+
247
+ from
248
+
249
+ (select * from tbl as t0 where (select count(*)+1 from tbl where id=t0.id and pid<t0.pid)=1) as t1
250
+
251
+ left join
252
+
253
+ (select * from tbl as t0 where (select count(*)+1 from tbl where id=t0.id and pid<t0.pid)=2) as t2
254
+
255
+ on t1.id=t2.id
256
+
257
+ left join
258
+
259
+ (select * from tbl as t0 where (select count(*)+1 from tbl where id=t0.id and pid<t0.pid)=3) as t3
260
+
261
+ on t1.id=t3.id
262
+
263
+ left join
264
+
265
+ (select * from tbl as t0 where (select count(*)+1 from tbl where id=t0.id and pid<t0.pid)=4) as t4
266
+
267
+ on t1.id=t4.id
268
+
269
+ ```
270
+
271
+
272
+
215
273
  # 結果
216
274
 
217
275
  |name1|price1|bikou1|name2|price2|bikou2|name3|price3|bikou3|name4|price4|bikou4|

3

調整

2019/09/05 09:31

投稿

yambejp
yambejp

スコア114903

test CHANGED
@@ -209,3 +209,17 @@
209
209
  call myproc;
210
210
 
211
211
  ```
212
+
213
+
214
+
215
+ # 結果
216
+
217
+ |name1|price1|bikou1|name2|price2|bikou2|name3|price3|bikou3|name4|price4|bikou4|
218
+
219
+ |:--|--:|:--|:--|--:|:--|:--|--:|:--|:--|--:|:--|
220
+
221
+ |あか|100|aa|あお|200|bb|むらさき|600|xx|みずいろ|700|yy|
222
+
223
+ |きいろ|300|aa|みどり|400|bb|ちゃいろ|800|zz|NULL|NULL|NULL|
224
+
225
+ |おれんじ|500|aa|NULL|NULL|NULL|NULL|NULL|NULL|NULL|NULL|NULL|

2

procedure

2019/09/05 09:19

投稿

yambejp
yambejp

スコア114903

test CHANGED
@@ -77,3 +77,135 @@
77
77
 
78
78
 
79
79
  これを右方向に自動的に伸ばしていきたいならプロシージャが必要になります。
80
+
81
+
82
+
83
+ # 右方向にどんどん伸ばす
84
+
85
+
86
+
87
+ - 元データ
88
+
89
+ ```SQL
90
+
91
+ create table tbl(
92
+
93
+ pid int primary key,
94
+
95
+ id int,
96
+
97
+ name varchar(10),
98
+
99
+ price int,
100
+
101
+ bikou varchar(30)
102
+
103
+ );
104
+
105
+
106
+
107
+ insert into tbl values
108
+
109
+ (1,1,'あか',100,'aa'),
110
+
111
+ (2,1,'あお',200,'bb'),
112
+
113
+ (3,2,'きいろ',300,'aa'),
114
+
115
+ (4,2,'みどり',400,'bb'),
116
+
117
+ (5,3,'おれんじ',500,'aa'),
118
+
119
+ (6,1,'むらさき',600,'xx'),
120
+
121
+ (7,1,'みずいろ',700,'yy'),
122
+
123
+ (8,2,'ちゃいろ',800,'zz');
124
+
125
+ ```
126
+
127
+
128
+
129
+ - procedure作成
130
+
131
+ ```SQL
132
+
133
+ drop procedure if exists myproc;
134
+
135
+ delimiter //
136
+
137
+ create procedure myproc()
138
+
139
+ begin
140
+
141
+ select max(cnt) into @max from (
142
+
143
+ select id,count(*) cnt from tbl group by id
144
+
145
+ ) as sub;
146
+
147
+ set @sql="select ";
148
+
149
+ set @c=1;
150
+
151
+ while @c<=@max do
152
+
153
+ if @c>1 then
154
+
155
+ set @sql=concat(@sql,",");
156
+
157
+ end if;
158
+
159
+ set @sql=concat(@sql,"t",@c,".name as name",@c,",t",@c,".price as price",@c,",t",@c,".bikou as bikou",@c);
160
+
161
+ set @c=@c+1;
162
+
163
+ end while;
164
+
165
+ set @c=1;
166
+
167
+ while @c<=@max do
168
+
169
+ if @c=1 then
170
+
171
+ set @sql=concat(@sql," from");
172
+
173
+ else
174
+
175
+ set @sql=concat(@sql," left join");
176
+
177
+ end if;
178
+
179
+ set @sql=concat(@sql,"(select * from tbl as t0 where (select count(*)+1 from tbl where id=t0.id and pid<t0.pid)=",@c,") as t",@c);
180
+
181
+ if @c>1 then
182
+
183
+ set @sql=concat(@sql," on t1.id=t",@c,".id");
184
+
185
+ end if;
186
+
187
+ set @c=@c+1;
188
+
189
+ end while;
190
+
191
+ prepare stmt from @sql;
192
+
193
+ execute stmt;
194
+
195
+ end
196
+
197
+ //
198
+
199
+ delimiter ;
200
+
201
+ ```
202
+
203
+ ※procedureは一回作成すればあとはデータが変化してもcallするだけでよいです
204
+
205
+ - procedure実行
206
+
207
+ ```SQL
208
+
209
+ call myproc;
210
+
211
+ ```

1

調整

2019/09/05 09:15

投稿

yambejp
yambejp

スコア114903

test CHANGED
@@ -1,3 +1,79 @@
1
1
  普通にやると同じidに順位が付かないのでどちらがname1/name2なのか確定できないので
2
2
 
3
3
  命題のような処理は不可能です。
4
+
5
+
6
+
7
+ # sample
8
+
9
+ 無理やりやるならこんな感じ
10
+
11
+ - 元データ
12
+
13
+ ```SQL
14
+
15
+ create table tbl(
16
+
17
+ pid int primary key,
18
+
19
+ id int,
20
+
21
+ name varchar(10),
22
+
23
+ price int,
24
+
25
+ bikou varchar(30)
26
+
27
+ );
28
+
29
+ insert into tbl values
30
+
31
+ (1,1,'あか',100,'aa'),
32
+
33
+ (2,1,'あお',200,'bb'),
34
+
35
+ (3,2,'きいろ',300,'aa'),
36
+
37
+ (4,2,'みどり',400,'bb'),
38
+
39
+ (5,3,'おれんじ',500,'aa');
40
+
41
+ ```
42
+
43
+ - 抽出
44
+
45
+ ```
46
+
47
+ select
48
+
49
+ t2.name as name1,
50
+
51
+ t2.price as price1,
52
+
53
+ t2.bikou as bikou1,
54
+
55
+ t4.name as name2,
56
+
57
+ t4.price as price2,
58
+
59
+ t4.bikou as bikou2
60
+
61
+ from (select *
62
+
63
+ from tbl as t1
64
+
65
+ where (select count(*)+1 from tbl where id=t1.id and pid<t1.pid)=1) as t2
66
+
67
+ left join (select *
68
+
69
+ from tbl as t3
70
+
71
+ where (select count(*)+1 from tbl where id=t3.id and pid<t3.pid)=2) as t4
72
+
73
+ on t2.id=t4.id
74
+
75
+ ```
76
+
77
+
78
+
79
+ これを右方向に自動的に伸ばしていきたいならプロシージャが必要になります。