teratail
回答率
85.48%
質問するログイン新規登録
トップMySQLに関する質問複数テーブルを外部キーで結合する方法

編集履歴

回答編集履歴

3

追記

2016/12/30 14:23

投稿

退会済みユーザー
test CHANGED
@@ -115,3 +115,113 @@
115
115
  GROUP BY m.id
116
116
 
117
117
  ```
118
+
119
+
120
+
121
+ #テーブル定義（改）
122
+
123
+
124
+
125
+ 優先順位の概念を追加
126
+
127
+ 本来、正規化して以下のようにすべきかと思います。
128
+
129
+
130
+
131
+ ```sql
132
+
133
+ CREATE TABLE `abilities` (
134
+
135
+ `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
136
+
137
+ `member_id` int(11) DEFAULT NULL,
138
+
139
+ `spec_id` int(11) DEFAULT NULL,
140
+
141
+ `priority` int(11) DEFAULT NULL,
142
+
143
+ PRIMARY KEY (`id`)
144
+
145
+ ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4;
146
+
147
+
148
+
149
+ SELECT
150
+
151
+ m.id
152
+
153
+ , m.name
154
+
155
+ , group_concat(s.spec ORDER BY a.priority) AS specs
156
+
157
+ FROM member m
158
+
159
+ LEFT JOIN abilities a ON m.id = a.member_id
160
+
161
+ LEFT JOIN spec s ON a.spec_id = s.id
162
+
163
+ GROUP BY m.id
164
+
165
+ ```
166
+
167
+
168
+
169
+ #事情を考慮した取得例
170
+
171
+
172
+
173
+ ```sql
174
+
175
+ SELECT
176
+
177
+ a.name
178
+
179
+ , sp1.spec as spec1
180
+
181
+ , sp2.spec as spec2
182
+
183
+ , sp3.spec as spec3
184
+
185
+ , sp4.spec as spec4
186
+
187
+ , sp5.spec as spec5
188
+
189
+ , sp6.spec as spec6
190
+
191
+ , sp7.spec as spec7
192
+
193
+ , sp8.spec as spec8
194
+
195
+ , sp9.spec as spec9
196
+
197
+ , sp10.spec as spec10
198
+
199
+ FROM `member` AS a
200
+
201
+ LEFT JOIN `ability` AS b ON b.`id` = a.`ability_id`
202
+
203
+ LEFT JOIN `spec` AS sp1 ON sp1.`id` = b.`spec1`
204
+
205
+ LEFT JOIN `spec` AS sp2 ON sp2.`id` = b.`spec2`
206
+
207
+ LEFT JOIN `spec` AS sp3 ON sp3.`id` = b.`spec3`
208
+
209
+ LEFT JOIN `spec` AS sp4 ON sp4.`id` = b.`spec4`
210
+
211
+ LEFT JOIN `spec` AS sp5 ON sp5.`id` = b.`spec5`
212
+
213
+ LEFT JOIN `spec` AS sp6 ON sp6.`id` = b.`spec6`
214
+
215
+ LEFT JOIN `spec` AS sp7 ON sp7.`id` = b.`spec7`
216
+
217
+ LEFT JOIN `spec` AS sp8 ON sp8.`id` = b.`spec8`
218
+
219
+ LEFT JOIN `spec` AS sp9 ON sp9.`id` = b.`spec9`
220
+
221
+ LEFT JOIN `spec` AS sp10 ON sp10.`id` = b.`spec10`
222
+
223
+ WHERE `id` = 1
224
+
225
+ group by a.id
226
+
227
+ ```

2

追記

2016/12/30 14:23

投稿

退会済みユーザー
test CHANGED
@@ -93,3 +93,25 @@
93
93
  (4,'C#');
94
94
 
95
95
  ```
96
+
97
+
98
+
99
+ #SELECT
100
+
101
+
102
+
103
+ ```sql
104
+
105
+ SELECT
106
+
107
+ m.id, m.name, group_concat(s.spec) AS specs
108
+
109
+ FROM member m
110
+
111
+ LEFT JOIN abilities a ON m.id=a.member_id
112
+
113
+ LEFT JOIN spec s ON a.spec_id = s.id
114
+
115
+ GROUP BY m.id
116
+
117
+ ```

1

修正

2016/12/30 13:31

投稿

退会済みユーザー
test CHANGED
@@ -3,3 +3,93 @@
3
3
 
4
4
 
5
5
  id, member_id, spec_id で構成する方が王道ですね。
6
+
7
+
8
+
9
+ ---
10
+
11
+
12
+
13
+ #テーブル定義
14
+
15
+
16
+
17
+ ```sql
18
+
19
+ CREATE TABLE `abilities` (
20
+
21
+ `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
22
+
23
+ `member_id` int(11) DEFAULT NULL,
24
+
25
+ `spec_id` int(11) DEFAULT NULL,
26
+
27
+ PRIMARY KEY (`id`)
28
+
29
+ ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
30
+
31
+
32
+
33
+ INSERT INTO `abilities` (`id`, `member_id`, `spec_id`)
34
+
35
+ VALUES
36
+
37
+ (1,1,1),
38
+
39
+ (2,1,2),
40
+
41
+ (3,2,1),
42
+
43
+ (4,2,3),
44
+
45
+ (5,2,4);
46
+
47
+
48
+
49
+ CREATE TABLE `member` (
50
+
51
+ `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
52
+
53
+ `name` varchar(641) NOT NULL DEFAULT '',
54
+
55
+ PRIMARY KEY (`id`)
56
+
57
+ ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
58
+
59
+
60
+
61
+ INSERT INTO `member` (`id`, `name`)
62
+
63
+ VALUES
64
+
65
+ (1,'tanaka'),
66
+
67
+ (2,'suzuki');
68
+
69
+
70
+
71
+ CREATE TABLE `spec` (
72
+
73
+ `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
74
+
75
+ `spec` varchar(64) NOT NULL DEFAULT '',
76
+
77
+ PRIMARY KEY (`id`)
78
+
79
+ ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
80
+
81
+
82
+
83
+ INSERT INTO `spec` (`id`, `spec`)
84
+
85
+ VALUES
86
+
87
+ (1,'MySQL'),
88
+
89
+ (2,'PHP'),
90
+
91
+ (3,'LINUX'),
92
+
93
+ (4,'C#');
94
+
95
+ ```