回答編集履歴
4
idの組がユニークでない場合の考慮を追加
test
CHANGED
|
@@ -2,8 +2,18 @@
|
|
|
2
2
|
|
|
3
3
|
```SQL
|
|
4
4
|
|
|
5
|
-
SELECT product_id FROM `table`
|
|
5
|
+
SELECT product_id FROM `table` GROUP BY product_id
|
|
6
6
|
|
|
7
|
-
|
|
7
|
+
HAVING GROUP_CONCAT(DISTINCT category_id ORDER BY category_id) = '1,2';
|
|
8
8
|
|
|
9
9
|
```
|
|
10
|
+
|
|
11
|
+
|
|
12
|
+
|
|
13
|
+
---
|
|
14
|
+
|
|
15
|
+
追記：
|
|
16
|
+
|
|
17
|
+
IDの組み合わせがユニークであるという前提は確かに記載されていませんでしたので、DISTINCT指定を追加しました。
|
|
18
|
+
|
|
19
|
+
主キーやユニークキーになっているなど各IDの組み合わせが一意に定まっているのあれば、DISTINCTは不要かと思います。
|
3
長いので改行
test
CHANGED
|
@@ -2,6 +2,8 @@
|
|
|
2
2
|
|
|
3
3
|
```SQL
|
|
4
4
|
|
|
5
|
+
SELECT product_id FROM `table`
|
|
6
|
+
|
|
5
|
-
|
|
7
|
+
GROUP BY product_id HAVING GROUP_CONCAT(category_id ORDER BY category_id) = '1,2';
|
|
6
8
|
|
|
7
9
|
```
|
2
順番考慮
test
CHANGED
|
@@ -2,6 +2,6 @@
|
|
|
2
2
|
|
|
3
3
|
```SQL
|
|
4
4
|
|
|
5
|
-
SELECT product_id FROM `table` GROUP BY product_id HAVING GROUP_CONCAT(category_id) = '1,2';
|
|
5
|
+
SELECT product_id FROM `table` GROUP BY product_id HAVING GROUP_CONCAT(category_id ORDER BY category_id) = '1,2';
|
|
6
6
|
|
|
7
7
|
```
|
1
関数記載漏れ
test
CHANGED
|
@@ -2,6 +2,6 @@
|
|
|
2
2
|
|
|
3
3
|
```SQL
|
|
4
4
|
|
|
5
|
-
SELECT product_id FROM `table` GROUP BY product_id HAVING (category_id) = '1,2';
|
|
5
|
+
SELECT product_id FROM `table` GROUP BY product_id HAVING GROUP_CONCAT(category_id) = '1,2';
|
|
6
6
|
|
|
7
7
|
```
|