困りました
mysql - Get top n records for each group of grouped results - Stack Overflow http://stackoverflow.com/questions/12113699/get-top-n-records-for-each-group-of-grouped-results
調べました。
Stack Overflowさんありがとう。
bluefeet♦ さんありがとう。
詳しいことはSQLを解読してください。
これのまんまコピペですが分かりやすいようにサンプルも作りました。
PHPでPDOから使用するサンプルがあった方が使いやすいかと思って作りました。
Ansers19
set @num := 0, @group := '';
select person, `group`, age
from
(
select person, `group`, age,
@num := if(@group = `group`, @num + 1, 1) as row_number,
@group := `group` as dummy
from mytable
order by `group`, age desc, person
) as x
where x.row_number <= 2;
自分で読んでて？？？だったので、、、
よくある都道府県を市町村名コードで抽出するサンプルを追加しました。
県コードで縛って、市町村コード(sort)の大きい順に3つ取り出すSQLです。
ついでに並びを昇順に戻して出力しています。
idはオートナンバー的なあれです。
todoufuken
set @num := 0, @pref := '';
select id, pref, sort, name
from
(
select id, pref, sort, name,
@num:=if(@pref=pref, @num+1, 1) as row_number,
@pref:=pref as dummy
from city
where pref in (16,17,18) -- 県コード
order by pref, sort desc
) as x
where x.row_number <= 3
order by pref, sort, id
ソース
PDOはシンプルなラッパークラスを利用しました。
とても楽ちんでした。
新しいバージョンもあるっぽいけど追ってません。
- Simple PHP PDO Wrapper : light, static, easy | ajaxray => Anis Ahmad's Tech Blog http://ajaxray.com/blog/simple-php-pdo-wrapper-light-static-easy-to-use
- ajaxray/static-pdo · GitHub https://github.com/ajaxray/static-pdo
実際にはDB.phpを継承するユーティリティクラスを作成し、$connectMySQLを引数にコンストラクタを呼んでます。コンストラクタ内で初期化もやってるのをぺろっと持ってきたので一部歪な形となっています。
sqltest.php
<?php
require_once './DB.php';
/*
* A simple wrapper for PDO.
* Inspired by the sweet PDO wrapper from http://www.fractalizer.ru
*
* @author Anis uddin Ahmad <anisniit@gmail.com>
* @link http://www.fractalizer.ru/frpost_120/php-pdo-wrapping-and-making-sweet/
* @link http://ajaxray.com
*/
// DB接続情報
unset($connectMySQL);
$connectMySQL = array(
'user' => 'root',
'password' => 'root',
'schema' => 'mytest',
'hostname' => '127.0.0.1',
);
function getRows($sqls)
{
global $connectMySQL;
Db::setConnectionInfo($connectMySQL['schema'], $connectMySQL['user'], $connectMySQL['password'], 'mysql', $connectMySQL['hostname']);
Db::setDriverOptions(array(PDO::MYSQL_ATTR_INIT_COMMAND=>"SET CHARACTER SET `utf8`"));
foreach($sqls as $sql){
unset($rows);
$rows = Db::getResult($sql);
}
return $rows;
}
unset($sql);
$sql[] = 'set @num := 0, @group := \'\'';
$sql[] = <<< SQLHEREDOC
SELECT *
FROM
(
SELECT *,
@num := if(@group = `グループ`, @num + 1, 1) AS row_number,
@group := `グループ` AS dummy
FROM
(
SELECT `id`, `点数`, `名前`, `グループ`
FROM tes
ORDER BY `グループ`, `点数` DESC, `id`
) AS ranking
) AS limitter
where limitter.row_number <= 3
SQLHEREDOC;
$arr = getRows($sql);
// ヘッダー
echo "+-------+-------+-------+-------+-------+-------+\n";
echo "| id | 点数 | 名前 | GROUP | row | dummy |\n";
echo "+-------+-------+-------+-------+-------+-------+\n";
foreach ($arr as $v)
{
foreach ($v as $w)
{
echo '| '.$w."\t";
}
echo "|\n";
}
echo "+-------+-------+-------+-------+-------+-------+\n";
実行結果
CygWinBash
ekaneko@hoge /cygdrive/c/var/www/sqltest
$ php sqltest.php
+-------+-------+-------+-------+-------+-------+
| id | 点数 | 名前 | GROUP | row | dummy |
+-------+-------+-------+-------+-------+-------+
| 5 | 93 | 天響 | A | 1 | A |
| 1 | 91 | 希星 | A | 2 | A |
| 8 | 55 | 葵絆 | A | 3 | A |
| 11 | 99 | 美俺 | B | 1 | B |
| 14 | 80 | 祈愛 | B | 2 | B |
| 10 | 60 | 泡姫 | B | 3 | B |
| 20 | 95 | 杏奴 | C | 1 | C |
| 31 | 92 | 杏奴 | C | 2 | C |
| 32 | 81 | 類 | C | 3 | C |
| 23 | 95 | 姫星 | D | 1 | D |
| 24 | 89 | 宝物 | D | 2 | D |
| 22 | 68 | 黄熊 | D | 3 | D |
+-------+-------+-------+-------+-------+-------+
テーブルデータ
mysql> SELECT * FROM mytest.tes;
+----+------+--------+----------+
| id | 点数 | 名前 | グループ |
+----+------+--------+----------+
| 1 | 91 | 希星 | A |
| 2 | 32 | 絆星 | A |
| 3 | 28 | 来桜 | A |
| 4 | 25 | 月 | A |
| 5 | 93 | 天響 | A |
| 6 | 27 | 緑輝 | A |
| 7 | 12 | 火星 | A |
| 8 | 55 | 葵絆 | A |
| 9 | 37 | 姫星 | A |
| 10 | 60 | 泡姫 | B |
| 11 | 99 | 美俺 | B |
| 12 | 35 | 姫凜 | B |
| 13 | 46 | 恋恋愛 | B |
| 14 | 80 | 祈愛 | B |
| 15 | 33 | 今鹿 | B |
| 16 | 60 | 七音 | B |
| 17 | 56 | 於菟 | C |
| 18 | 76 | 茉莉 | C |
| 19 | 65 | 不律 | C |
| 20 | 95 | 杏奴 | C |
| 21 | 37 | 類 | C |
| 22 | 68 | 黄熊 | D |
| 23 | 95 | 姫星 | D |
| 24 | 89 | 宝物 | D |
| 25 | 35 | 心愛 | D |
| 26 | 56 | 美望 | D |
| 27 | 45 | 姫奈 | D |
| 28 | 30 | 皇帝 | D |
| 29 | 52 | 男 | D |
| 30 | 17 | 本気 | D |
| 31 | 92 | 杏奴 | C |
| 32 | 81 | 類 | C |
+----+------+--------+----------+
32 rows in set (0.00 sec)
mysql>
memo
適当にデータを作りすぎですね。
フィールド名も漢字が分かりやすいだろうと気を利かせたけどSQLが読みにくくなっただけのような気もしますし…。
点数の適当っぷりは乱数UPDATEしたためです。
UPDATE `mytest`.`tes` SET `点数`=FLOOR(1+RAND()*9)*10+FLOOR(RAND()*10);
-- SELECT FLOOR(1+RAND()*9)*10+FLOOR(RAND()*10);