MySQL 複数のコラムをGROUP BYして条件にしたレコードを取得したい

MySQL 5.7 以上でGROUP BYしてレコードをまるっと取ってくる方法は分かりました。 複数のカラムをGROUP BYして検索条件にしてレコードを取ってきたいと思います。

テーブル table_a

id	name	typeID
1	星宮いちご	1
2	霧矢あおい	2
3	紫吹蘭	3
4	神崎美月	3
5	星宮いちご	1

nameとtypeIDが重複しているデータのレコードを取得したい

重複しているデータの取得するのはONLY_FULL_GROUP_BY、つまりGROUP BYで使うカラムだけ表示するのは特に難しくありません。

SELECT typeID, name FROM table_a
GROUP BY typeID, name
HAVING COUNT(typeID) >= 2 AND COUNT(name) >= 2;

+--------+-----------------+
| typeID | name            |
+--------+-----------------+
|      1 | 星宮いちご        |
+--------+-----------------+

idカラムを含めたレコード全てのカラムを表示するには、GROUP BYで取得するデータをサブクエリにする必要があります。
今回GROUP BYしているクエリがtypeID, nameの2カラム返すのでWHEREの条件もtypeIDとnameがマッチするようにしなければなりません。

WHEW (カラム名, カラム名, ...) in (値, 値, ... ) の形式にする

値が複数になる時は、マッチさせるカラム名側も()で囲い,区切りで指定すればOK

SELECT * FROM table_a
WHERE (typeID, name) IN (
  SELECT typeID, name FROM table_a
  GROUP BY typeID, name
  HAVING COUNT(typeID) >= 2 AND COUNT(name) >= 2
);

+----+-----------------+--------+
| id | name            | typeID |
+----+-----------------+--------+
|  1 | 星宮いちご      |      1 |
|  5 | 星宮いちご      |      1 |
+----+-----------------+--------+

WHEREのカラムが合ってないと...

SELECT * FROM table_a
WHERE typeID IN (
  SELECT typeID, name FROM table_a
  GROUP BY typeID, name
  HAVING COUNT(typeID) >= 2 AND COUNT(name) >= 2
);

Operand should contain 1 column(s) エラー

WHEREのカラム名を()で囲ってないと...

SELECT * FROM table_a
WHERE typeID, name IN (
  SELECT typeID, name FROM table_a
  GROUP BY typeID, name
  HAVING COUNT(typeID) >= 2 AND COUNT(name) >= 2
);

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near... シンタックスエラーになります。

複数のGROUP BYを条件にしたSQLを書いたことがなかったのでチョットハマりました。 単純にWHEW 〜 IN 〜 で複数の条件を扱う方法を知っていればONLY_FULL_GROUP_BYオプションがONでも大した問題ではなかったですね…

---

[参考]

• 備忘録:SQLのINを複数列指定で使う - Qiita
• SQLのIN句で複数の条件を指定する - しょぼんメモリ （´・ω・｀）

基礎からのMySQL 第3版 (基礎からシリーズ)

MySQL GROUP BYしたらエラーになった

お久しぶりのMySQL。
MySQLで重複してるデータを取ろうとしました。

SELECT * FROM table_a
GROUP BY name
HAVING COUNT(*) >= 2;

Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.table_a.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

MySQL 5.7からデフォルトでONLY_FULL_GROUP_BYオプションがONになっており、GROUP BYするデータだけを取ってきなさいよ。という事でGROUP BYで使っているカラム以外もSELECTしているとエラーになるというもののようです。(MySQL 5.7から...ずいぶんSQL文かいてなかったのがバレますﾈ...)

一部のカラムでGROUP BYして、全部のデータを取ってきたいなら、GROUP BYしたデータサブクエリにしてWHEREの条件にすればOKっぽいです。

SELECT * FROM table_a
WHERE name in (
  SELECT name FROM table_a
  GROUP BY name
  HAVING COUNT(*) >= 2
);

SQL文長くなりましたが、nameカラムが重複してるレコードのカラム全部を表示することができました！
ONLY_FULL_GROUP_BYオプションをOFFにする方法もあるようですが、何かしら理由あっての事だと思うので慣れたいと思います。

---

[参考]

• MySQLでGROUP BY句を使ったSQL文がエラーになる原因と対応 | jMatsuzaki
• 重複しているレコードを検索するSQL(大量データも対応) - Qiita

これからはじめる MySQL入門

docker内のMySQLに接続したい

docker-compose upで立ち上げたアプリで使用してるMySQLにアクセスしたメモ。
尚dockerの事はよくわかってない。

アプリの起動

$ docker-compose up

MySQLのホストとポート番号の確認

$ docker ps

現在起動中のコンテナの一覧が出力されるのでmysqlのコンテナを探す。

259d8b05f8ec  mysql:5.6  ... 127.0.0.1:3306->3306/tcp

こんな感じだった。
ホストは127.0.0.1、ポートは3306

MySQLにアクセス

$ mysql -h 127.0.0.1 -P 3306 -u root -p

ホストはlocalhostと指定するとローカルマシンのmysqlソケットを探しに行くのでエラーになる。
ポート番号の指定は大文字で-P

Sequelとかで接続するときはTCP/IPのタブにして同じように設定を入力すればOK

　
dockerを覚えなければ…

---

[参考]

• docker内のMySQLサーバーに接続するときの注意点 - Qiita
• Docker & Docker-Composeの基本的な使い方 - Qiita

Docker/Kubernetes 実践コンテナ開発入門