回答編集履歴
1
修正版
answer
CHANGED
|
@@ -1,39 +1,30 @@
|
|
|
1
|
+
# 修正版
|
|
1
2
|
```SQL
|
|
2
3
|
create table items(id int primary key,title varchar(10));
|
|
3
4
|
insert into items values
|
|
4
|
-
(1,'
|
|
5
|
+
(1,'シーフードカレー'),
|
|
5
|
-
(2,'
|
|
6
|
+
(2,'肉じゃが'),
|
|
6
7
|
(3,'品物C');
|
|
7
8
|
|
|
8
|
-
create table category_item(id int,item_id int,category_id int,unique(item_id,category_id));
|
|
9
|
+
create table category_item(id int primary key,item_id int,category_id int,unique(item_id,category_id));
|
|
9
10
|
insert into category_item values
|
|
10
11
|
(1,1,1),
|
|
11
|
-
(
|
|
12
|
+
(2,1,2),
|
|
12
|
-
(
|
|
13
|
+
(3,1,3),
|
|
13
|
-
(
|
|
14
|
+
(4,2,1),
|
|
14
|
-
(
|
|
15
|
+
(5,2,2);
|
|
15
16
|
|
|
16
17
|
create table categories(id int primary key,category_name varchar(10));
|
|
17
18
|
insert into categories values
|
|
18
|
-
(1,'肉'),
|
|
19
|
+
(1,'肉類'),
|
|
19
|
-
(2,'魚'),
|
|
20
|
-
(
|
|
20
|
+
(2,'野菜類'),
|
|
21
|
+
(3,'魚介類');
|
|
21
22
|
```
|
|
22
|
-
|
|
23
|
+
抽出
|
|
23
24
|
```SQL
|
|
25
|
+
select * from items as i
|
|
26
|
+
inner join(
|
|
24
|
-
|
|
27
|
+
select item_id as id,min(category_id) as category_id from category_item
|
|
25
|
-
|
|
28
|
+
group by item_id
|
|
26
|
-
|
|
29
|
+
) as tmp using(id)
|
|
27
30
|
```
|
|
28
|
-
を実行すると
|
|
29
|
-
|id|title|category_id|
|
|
30
|
-
|:--|:--|--:|
|
|
31
|
-
|1|品物A|1|
|
|
32
|
-
|1|品物A|2|
|
|
33
|
-
|1|品物A|3|
|
|
34
|
-
|1|品物A|1|
|
|
35
|
-
|1|品物A|2|
|
|
36
|
-
|2|品物B|NULL|
|
|
37
|
-
|3|品物C|NULL|
|
|
38
|
-
|
|
39
|
-
こうなるため命題が成り立っていません
|