teratail header banner
teratail header banner
質問するログイン新規登録

回答編集履歴

3

追記

2016/12/30 14:23

投稿

退会済みユーザー
answer CHANGED
@@ -56,4 +56,59 @@
56
56
  LEFT JOIN abilities a ON m.id=a.member_id
57
57
  LEFT JOIN spec s ON a.spec_id = s.id
58
58
  GROUP BY m.id
59
+ ```
60
+
61
+ #テーブル定義(改)
62
+
63
+ 優先順位の概念を追加
64
+ 本来、正規化して以下のようにすべきかと思います。
65
+
66
+ ```sql
67
+ CREATE TABLE `abilities` (
68
+ `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
69
+ `member_id` int(11) DEFAULT NULL,
70
+ `spec_id` int(11) DEFAULT NULL,
71
+ `priority` int(11) DEFAULT NULL,
72
+ PRIMARY KEY (`id`)
73
+ ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4;
74
+
75
+ SELECT
76
+ m.id
77
+ , m.name
78
+ , group_concat(s.spec ORDER BY a.priority) AS specs
79
+ FROM member m
80
+ LEFT JOIN abilities a ON m.id = a.member_id
81
+ LEFT JOIN spec s ON a.spec_id = s.id
82
+ GROUP BY m.id
83
+ ```
84
+
85
+ #事情を考慮した取得例
86
+
87
+ ```sql
88
+ SELECT
89
+ a.name
90
+ , sp1.spec as spec1
91
+ , sp2.spec as spec2
92
+ , sp3.spec as spec3
93
+ , sp4.spec as spec4
94
+ , sp5.spec as spec5
95
+ , sp6.spec as spec6
96
+ , sp7.spec as spec7
97
+ , sp8.spec as spec8
98
+ , sp9.spec as spec9
99
+ , sp10.spec as spec10
100
+ FROM `member` AS a
101
+ LEFT JOIN `ability` AS b ON b.`id` = a.`ability_id`
102
+ LEFT JOIN `spec` AS sp1 ON sp1.`id` = b.`spec1`
103
+ LEFT JOIN `spec` AS sp2 ON sp2.`id` = b.`spec2`
104
+ LEFT JOIN `spec` AS sp3 ON sp3.`id` = b.`spec3`
105
+ LEFT JOIN `spec` AS sp4 ON sp4.`id` = b.`spec4`
106
+ LEFT JOIN `spec` AS sp5 ON sp5.`id` = b.`spec5`
107
+ LEFT JOIN `spec` AS sp6 ON sp6.`id` = b.`spec6`
108
+ LEFT JOIN `spec` AS sp7 ON sp7.`id` = b.`spec7`
109
+ LEFT JOIN `spec` AS sp8 ON sp8.`id` = b.`spec8`
110
+ LEFT JOIN `spec` AS sp9 ON sp9.`id` = b.`spec9`
111
+ LEFT JOIN `spec` AS sp10 ON sp10.`id` = b.`spec10`
112
+ WHERE `id` = 1
113
+ group by a.id
59
114
  ```

2

追記

2016/12/30 14:23

投稿

退会済みユーザー
answer CHANGED
@@ -45,4 +45,15 @@
45
45
  (2,'PHP'),
46
46
  (3,'LINUX'),
47
47
  (4,'C#');
48
+ ```
49
+
50
+ #SELECT
51
+
52
+ ```sql
53
+ SELECT
54
+ m.id, m.name, group_concat(s.spec) AS specs
55
+ FROM member m
56
+ LEFT JOIN abilities a ON m.id=a.member_id
57
+ LEFT JOIN spec s ON a.spec_id = s.id
58
+ GROUP BY m.id
48
59
  ```

1

修正

2016/12/30 13:31

投稿

退会済みユーザー
answer CHANGED
@@ -1,3 +1,48 @@
1
1
  ability テーブルは
2
2
 
3
- id, member_id, spec_id で構成する方が王道ですね。
3
+ id, member_id, spec_id で構成する方が王道ですね。
4
+
5
+ ---
6
+
7
+ #テーブル定義
8
+
9
+ ```sql
10
+ CREATE TABLE `abilities` (
11
+ `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
12
+ `member_id` int(11) DEFAULT NULL,
13
+ `spec_id` int(11) DEFAULT NULL,
14
+ PRIMARY KEY (`id`)
15
+ ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
16
+
17
+ INSERT INTO `abilities` (`id`, `member_id`, `spec_id`)
18
+ VALUES
19
+ (1,1,1),
20
+ (2,1,2),
21
+ (3,2,1),
22
+ (4,2,3),
23
+ (5,2,4);
24
+
25
+ CREATE TABLE `member` (
26
+ `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
27
+ `name` varchar(641) NOT NULL DEFAULT '',
28
+ PRIMARY KEY (`id`)
29
+ ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
30
+
31
+ INSERT INTO `member` (`id`, `name`)
32
+ VALUES
33
+ (1,'tanaka'),
34
+ (2,'suzuki');
35
+
36
+ CREATE TABLE `spec` (
37
+ `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
38
+ `spec` varchar(64) NOT NULL DEFAULT '',
39
+ PRIMARY KEY (`id`)
40
+ ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
41
+
42
+ INSERT INTO `spec` (`id`, `spec`)
43
+ VALUES
44
+ (1,'MySQL'),
45
+ (2,'PHP'),
46
+ (3,'LINUX'),
47
+ (4,'C#');
48
+ ```