こんにちは、技術統括部の豊田です。
今日は、SQLに関する記事を書きたいと思います。

現在、cakePHPを使用した案件の開発を行なっています。
cakePHPに限らず、多くのフレームワークはDBの扱いを抽象化してくれます。
基本的なCRUD操作について言えば、ほぼSQLフリーでプログラムが書けてしまします。

そんな中、最近、業務でランキングデータを作成するために、
こちょこちょSQLを書いたりしました。

例えば、こんなテーブルを、

+—-+————+——+——-+
| id 　| date       　　| name | score |
+—-+————+——+——-+
|  1 　| 2009-02-01 | A    |   100 |
|  2 　| 2009-02-01 | B    |    50 |
|  3 　| 2009-02-01 | C    |    10 |
|  4 　| 2009-02-01 | D    |     5 |
|  5 　| 2009-02-02 | A    |    50 |
|  6 　| 2009-02-02 | B    |    10 |
|  7 　| 2009-02-02 | C    |    50 |
|  8 　| 2009-02-02 | D    |    15 |
+—-+————+——+——-+

これを、nameと、scoreの合計で集計して、scoreの高い順に並べるには、

SELECT name, SUM(score) AS total FROM results GROUP BY name ORDER BY total DESC;

+——+——-+
| name | total |
+——+——-+
| A    |   150 |
| B    |    60 |
| C    |    60 |
| D    |    20 |
+——+——-+

これに順位をつけるのですが、注意すべきは、同一順位がいた場合の扱いですね。
上の場合、BさんとCさんが同じ得点なので、2位が2人。Dさんは4位とすると。
上のSELECT文を2回使用して、

SELECT

(SELECT
COUNT(T1.total)
FROM (SELECT name, SUM(score) AS total FROM results GROUP BY name) AS T1
WHERE T1.total > results.total) + 1 AS ranking,

name,
total
FROM
(SELECT results.name, SUM(results.score) AS total FROM results GROUP BY name) as results
ORDER BY results.total DESC;

+———+——+——-+
| ranking | name | total |
+———+——+——-+
|       1 | A    |   150 |
|       2 | B    |    60 |
|       2 | C    |    60 |
|       4 | D    |    20 |
+———+——+——-+

でいいでしょうか。(長いな。。)
ちょっと、直感的にすぐピンとはこないんですが。
何となくやっていることはわかります。

(SELECT
COUNT(T1.total)
FROM (SELECT name, SUM(score) AS total FROM results GROUP BY name) AS T1
WHERE T1.total > results.total) + 1 AS ranking,

この部分、自分より上の順位の人の数を数えているんですよね。。

さて、では、Dさんを3位としたい場合はどうでしょうか。。
・・・。

それはまた今度。

I ONLY DREAD ONE DAY AT A TIME !