ability テーブルは
id, member_id, spec_id で構成する方が王道ですね。
#テーブル定義
sql
1CREATE TABLE `abilities` (
2 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
3 `member_id` int(11) DEFAULT NULL,
4 `spec_id` int(11) DEFAULT NULL,
5 PRIMARY KEY (`id`)
6) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
7
8INSERT INTO `abilities` (`id`, `member_id`, `spec_id`)
9VALUES
10 (1,1,1),
11 (2,1,2),
12 (3,2,1),
13 (4,2,3),
14 (5,2,4);
15
16CREATE TABLE `member` (
17 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
18 `name` varchar(641) NOT NULL DEFAULT '',
19 PRIMARY KEY (`id`)
20) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
21
22INSERT INTO `member` (`id`, `name`)
23VALUES
24 (1,'tanaka'),
25 (2,'suzuki');
26
27CREATE TABLE `spec` (
28 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
29 `spec` varchar(64) NOT NULL DEFAULT '',
30 PRIMARY KEY (`id`)
31) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
32
33INSERT INTO `spec` (`id`, `spec`)
34VALUES
35 (1,'MySQL'),
36 (2,'PHP'),
37 (3,'LINUX'),
38 (4,'C#');
#SELECT
sql
1SELECT
2 m.id, m.name, group_concat(s.spec) AS specs
3FROM member m
4LEFT JOIN abilities a ON m.id=a.member_id
5LEFT JOIN spec s ON a.spec_id = s.id
6GROUP BY m.id
#テーブル定義(改)
優先順位の概念を追加
本来、正規化して以下のようにすべきかと思います。
sql
1CREATE TABLE `abilities` (
2 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
3 `member_id` int(11) DEFAULT NULL,
4 `spec_id` int(11) DEFAULT NULL,
5 `priority` int(11) DEFAULT NULL,
6 PRIMARY KEY (`id`)
7) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4;
8
9SELECT
10 m.id
11 , m.name
12 , group_concat(s.spec ORDER BY a.priority) AS specs
13FROM member m
14LEFT JOIN abilities a ON m.id = a.member_id
15LEFT JOIN spec s ON a.spec_id = s.id
16GROUP BY m.id
#事情を考慮した取得例
sql
1SELECT
2 a.name
3 , sp1.spec as spec1
4 , sp2.spec as spec2
5 , sp3.spec as spec3
6 , sp4.spec as spec4
7 , sp5.spec as spec5
8 , sp6.spec as spec6
9 , sp7.spec as spec7
10 , sp8.spec as spec8
11 , sp9.spec as spec9
12 , sp10.spec as spec10
13FROM `member` AS a
14LEFT JOIN `ability` AS b ON b.`id` = a.`ability_id`
15LEFT JOIN `spec` AS sp1 ON sp1.`id` = b.`spec1`
16LEFT JOIN `spec` AS sp2 ON sp2.`id` = b.`spec2`
17LEFT JOIN `spec` AS sp3 ON sp3.`id` = b.`spec3`
18LEFT JOIN `spec` AS sp4 ON sp4.`id` = b.`spec4`
19LEFT JOIN `spec` AS sp5 ON sp5.`id` = b.`spec5`
20LEFT JOIN `spec` AS sp6 ON sp6.`id` = b.`spec6`
21LEFT JOIN `spec` AS sp7 ON sp7.`id` = b.`spec7`
22LEFT JOIN `spec` AS sp8 ON sp8.`id` = b.`spec8`
23LEFT JOIN `spec` AS sp9 ON sp9.`id` = b.`spec9`
24LEFT JOIN `spec` AS sp10 ON sp10.`id` = b.`spec10`
25WHERE `id` = 1
26group by a.id
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。
2016/12/30 14:19