MySQL

MySQL　グループの中の最大値のデータを取得

複数のデータがあるとき、グループで区切った、グループごとの最大値のデータを取得したいケースがよくあると思います。
文章で書いてもよくわからないと思うので、例をあげてみます。

ここに2つのテーブルがあります。（いつものですが…）
ninjyaテーブルと、skillテーブルです。
ninjyaテーブルには、忍者の名前、skillテーブルには必殺技の名前が格納されています。
一人の忍者は多くの必殺技を持つという、1対他のデータ構造になっています。
今回は、それぞれの必殺技に、威力(power)というフィールドを追加してみました。

|>|ninjya テーブル|h
|id|name|h
|1|ナルト|
|2|サスケ|

#br

|>|>|>|skill テーブル|h
|id|ninjya_id|name|power|h
|1|1|螺旋丸|100|
|2|1|ナルトキック|10|
|3|2|千鳥|10|
|4|2|天照|110|
|5|1|影分身|3|
|6|2|月読|5|

では、skillテーブルから、それぞれの忍者ごとの最大の威力のデータを取得します。

 SELECT *
 FROM skill
 WHERE power 
 	IN 
 	(SELECT MAX(power) FROM skill GROUP BY ninjya_id)

結果です。

|id|ninjya_id|name|power|h
|1|1|螺旋丸|100|
|4|2|天照|110|

なんだか

 SELECT MAX(power) FROM skill GROUP BY ninjya_id

だけでいけそうですが、上記のSQLを下記のように実行すると、次の通りです。MAX( power )だけ取得してもしょうがないので、他のデータも（*）で取得してみます。

 SELECT * , MAX( power ) FROM skill GROUP BY ninjya_id LIMIT 0 , 30

結果は次の通りです。

|id|ninjya_id|name|power|MAX( power)|h
|1|1|螺旋丸|100|100|
|3|2|千鳥|10|110|

ninjya_idが2の場合の、powerが最大の技の名前が違いますね。
確かに、威力の最大値MAX( power)は正しいですが、そのほかのデータが一緒にとれません。
「グループの中の最大値のデータを取得」したいのであって、「グループの中の最大値を取得」したいわけではないんですね。（紛らわしいですが）

せっかくなので、忍者の名前と連結させておきます。
エイリアスをつける必要があります。

 SELECT * 
 FROM
 (

SELECT *
FROM skill
WHERE power
IN
(SELECT MAX(power) FROM skill GROUP BY ninjya_id)
)

 AS max_power
 JOIN
 	ninjya 
 ON
 	ninjya.id=max_power.ninjya_id

結果は次の通りです。

|id|ninjya_id|name|power|id|name|h
|1|1|螺旋丸|100|1|ナルト|
|4|2|天照|110|2|サスケ|

• max_powerで各忍者の最も強い必殺技が取得できてるのは偶然では？例えば、螺旋丸のpowerが110で、ナルトキックのpowerが200の場合、max_powerで取得されるのは螺旋丸、ナルトキック、天照になるので、どちらがナルトの最も強い必殺技として選ばれるか、保証が無いと思います。 — 匿名 {2015-10-03 (土) 08:47:24}
• 匿名さん、こんにちは。すみません、同じ値が入った時に、ってことですかね？すみませんが、ここでは「グループの中の最大値のデータを取得」のサンプルだけなので、そこまでは考慮していませんでした。ご指摘ありがとうございます。 — 書いた人 {2015-12-21 (月) 19:03:15}

MySQL

PHP＋MySQLのシステムで、PHPで時間がかかっている場合はPHPのエラーメッセージが出るのでわかりますが、MySQLで時間がかかっている場合は、何が時間がかかっているのか分かりにくいですね。

その時のために、どのクエリがどれぐらい時間がかかるか、ログファイルに出力するようにしておきます。

• my.iniなどのMySQLの設定ファイルを開く
• [mysqld]のセクションの中に、次のように記載

 
 log=C:\xampp\mysql\data\query.log

C:\xampp\mysql\dataに、query.logというファイルを作っておく。

• general_log = 1 — nnn {2011-12-11 (日) 17:12:25}

MySQL

山田さんか山崎さんなど、山●という名前を検索したい…などというときは、条件にLIKEを使います。
その時、上記でいえば●の部分は%のワイルドカードを使用します。

 SELECT * FROM test_table WHERE name LIKE '山%';

MySQL

MySQL5.6にバージョンアップしたところ、以前のバージョンでは問題のなかったSQLで下記のようなエラーが発生するようになってしまいました。
#1292 – Incorrect datetime value

原因はMySQL5.6からは、mysql_install_db実行時に/usr/my.cnfというファイルが生成され、sql_modeにSTRICT_TRANS_TABLESが指定されるためです。
MySQL5.5以前ではデフォルトではsql_mode指定はありませんでした。

 sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

STRICT_TRANS_TABLEを指定しない場合は、INSERT/UPDATE時に不正な値を指定しても自動変換してそれなりなデータを格納しますが、STRICT_TRANS_TABLEを指定すると厳密にエラー扱いして変更をロールバックするようになります。

/etc/my.cnfで/usr/my.cnfのsql_modeを上書きすることはできないようで、/usr/my.cnfを書き換える必要がありました。

my.cnfはRPMに含まれるファイルではありません。

my_sql_install_db実行時に/usr/my.cnfを生成するのは不適切な挙動なのではないかという指摘があり、オプション –keep-my-cnf を指定するとテンプレートからmy.cnfを生成しないように修正されました。
http://bugs.mysql.com/bug.php?id=71600

MySQL

いったんパスワード無しでログインできる状態にしてから、パスワードを再設定します。通常、ルートパスワードの再設定にはroot権限が必要です。

• /etc/my.cnfの項目[mysqld]にskip-grant-tablesを追加します。

 [mysqld]
 ...
 skip-grant-tables
 ...

• 再起動すると、パスワード無しでログインできるようになります。

 # service mysqld restart

• mysqladminでログインします。

 # mysql -u root mysql

• rootのパスワードを変更

 mysql> UPDATE mysql.user SET Password=PASSWORD('newpassword') WHERE User='root';
 Query OK, 0 rows affected (0.00 sec)
 Rows matched: 1  Changed: 0  Warnings: 0

• 変更したパスワードを反映させます。

 mysql> flush privileges;
 Query OK, 0 rows affected (0.00 sec)

• 最初に/etc/my.cnfに追加したskip-grant-tablesを削除してmysqlを再起動すると、元通りのパスワード認証が必要な状態で動作するようになります。