回答編集履歴
2
修正
answer
CHANGED
|
@@ -25,6 +25,7 @@
|
|
|
25
25
|
この場合のインデックスはeiga_id,haiyu_idの複合インデックスが良いでしょう
|
|
26
26
|
|
|
27
27
|
# sample
|
|
28
|
+
- テーブル作成
|
|
28
29
|
|
|
29
30
|
```SQL
|
|
30
31
|
create table eiga(eiga_id int primary key,title_jp varchar(30),title_en varchar(30));
|
|
@@ -44,10 +45,11 @@
|
|
|
44
45
|
(3096,1,3),
|
|
45
46
|
(3097,1,4),
|
|
46
47
|
(3098,1,5);
|
|
47
|
-
|
|
48
|
+
```
|
|
48
49
|
|
|
49
|
-
全データ表示
|
|
50
|
+
- 全データ表示
|
|
51
|
+
|
|
50
|
-
```
|
|
52
|
+
```SQL
|
|
51
53
|
select t1.id,t2.title_jp,t2.title_en,t3.character
|
|
52
54
|
from shukei as t1
|
|
53
55
|
inner join eiga as t2 on t1.eiga_id=t2.eiga_id
|
|
@@ -55,8 +57,8 @@
|
|
|
55
57
|
|
|
56
58
|
```
|
|
57
59
|
|
|
58
|
-
特定の俳優を抽出
|
|
60
|
+
- 特定の俳優を抽出
|
|
59
|
-
```
|
|
61
|
+
```SQL
|
|
60
62
|
select t1.id,t2.title_jp,t2.title_en,t3.character
|
|
61
63
|
from shukei as t1
|
|
62
64
|
inner join eiga as t2 on t1.eiga_id=t2.eiga_id
|
1
追記
answer
CHANGED
|
@@ -22,4 +22,44 @@
|
|
|
22
22
|
3098,1,5
|
|
23
23
|
|
|
24
24
|
をとっておくのが妥当です。
|
|
25
|
-
この場合のインデックスはeiga_id,haiyu_idの複合インデックスが良いでしょう
|
|
25
|
+
この場合のインデックスはeiga_id,haiyu_idの複合インデックスが良いでしょう
|
|
26
|
+
|
|
27
|
+
# sample
|
|
28
|
+
|
|
29
|
+
```SQL
|
|
30
|
+
create table eiga(eiga_id int primary key,title_jp varchar(30),title_en varchar(30));
|
|
31
|
+
insert into eiga values(1,'エアマスター','Air Master');
|
|
32
|
+
|
|
33
|
+
create table haiyu(haiyu_id int primary key,`character` varchar(30));
|
|
34
|
+
insert into haiyu values
|
|
35
|
+
(1,'Tomiko Seriguchi'),
|
|
36
|
+
(2,'Tsukio Taketsugu'),
|
|
37
|
+
(3,'Yoshinori Konishi'),
|
|
38
|
+
(4,'Yuukorin');
|
|
39
|
+
|
|
40
|
+
create table shukei(id int primary key,eiga_id int,haiyu_id int,unique(eiga_id,haiyu_id));
|
|
41
|
+
insert into shukei values
|
|
42
|
+
(3094,1,1),
|
|
43
|
+
(3095,1,2),
|
|
44
|
+
(3096,1,3),
|
|
45
|
+
(3097,1,4),
|
|
46
|
+
(3098,1,5);
|
|
47
|
+
```
|
|
48
|
+
|
|
49
|
+
全データ表示
|
|
50
|
+
```
|
|
51
|
+
select t1.id,t2.title_jp,t2.title_en,t3.character
|
|
52
|
+
from shukei as t1
|
|
53
|
+
inner join eiga as t2 on t1.eiga_id=t2.eiga_id
|
|
54
|
+
inner join haiyu as t3 on t1.haiyu_id=t3.haiyu_id
|
|
55
|
+
|
|
56
|
+
```
|
|
57
|
+
|
|
58
|
+
特定の俳優を抽出
|
|
59
|
+
```
|
|
60
|
+
select t1.id,t2.title_jp,t2.title_en,t3.character
|
|
61
|
+
from shukei as t1
|
|
62
|
+
inner join eiga as t2 on t1.eiga_id=t2.eiga_id
|
|
63
|
+
inner join haiyu as t3 on t1.haiyu_id=t3.haiyu_id
|
|
64
|
+
and t3.character='Yoshinori Konishi'
|
|
65
|
+
```
|