group_conactしたidをwhere句で条件として使いたい - MySQL
やりたいこと
SELECT *
FROM `table_1`
WHERE `id` IN(
SELECT GROUP_CONCAT(`id`)
FROM `table_1`
GROUP BY `group_id`
);
こんな感じで、カンマ区切りになった値をIN句の条件指定に使えるのかな~?
やってみた
とりあえず、こんなテーブルで
mysql> SELECT * FROM `table_1`;
+------+----------+
| id | group_id |
+------+----------+
| 1 | 1 |
| 2 | 1 |
| 3 | 2 |
| 4 | 2 |
+------+----------+
4 rows in set (0.00 sec)
実行!
mysql> SELECT *
-> FROM `table_1`
-> WHERE `id` IN(
-> SELECT GROUP_CONCAT(`id`)
-> FROM `table_1`
-> GROUP BY `group_id`
-> );
+------+----------+
| id | group_id |
+------+----------+
| 1 | 1 |
| 3 | 2 |
+------+----------+
2 rows in set (0.00 sec)
惨敗。。。
全部くっつけちゃえ
GROUP_CONCAT
で更にぎゅっとしてFIND_IN_SET
にぶち込む
mysql> SELECT *
-> FROM `table_1`
-> WHERE FIND_IN_SET(id, (
-> SELECT GROUP_CONCAT(t1.ids)
-> FROM (
-> SELECT GROUP_CONCAT(`id`) ids
-> FROM `table_1`
-> GROUP BY `group_id`
-> ) t1
-> ));
+------+----------+
| id | group_id |
+------+----------+
| 1 | 1 |
| 2 | 1 |
| 3 | 2 |
| 4 | 2 |
+------+----------+
4 rows in set (0.01 sec)
出来た!!!
データ量を増やしてやってみる
mysql> INSERT INTO table_1(ID, GROUP_ID) VALUES
-> (5, 2),
-> (6, 1),
~~~~~ 省略 ~~~~~
-> (14999, 1),
-> (15000, 1);
Query OK, 14996 rows affected (0.34 sec)
Records: 14996 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM `table_1` WHERE FIND_IN_SET(id, (SELECT GROUP_CONCAT(t1.ids) FROM (SELECT GROUP_CONCAT(`id`) ids FROM `table_1` GROUP BY `group_id`) t1));
+-------+----------+
| id | group_id |
+-------+----------+
| 1278 | 5 |
| 11918 | 1 |
~~~~~~~ 省略 ~~~~~~~
| 12781 | 1 |
| 12783 | 1 |
+-------+----------+
171 rows in set, 6 warnings (0.37 sec)
ハァ~イッ!!
そんなに長い文字列覚えられないよね、、ごめんなさい。
完全解決してないけど今日はここまで!