mysqlでの集計&ランキングに関して

Question

下記のようなテーブルがある場合

rankingテーブル
no dat name　　tensu
1 2018-10-10 田中 50
2　2018-10-10 鈴木 50
3　2018-10-10 高橋 65
4 2018-10-20 田中 95
5　2018-10-20 鈴木 90
6　2018-10-20 高橋 85

例えば下記のような順位を出したい時
1位　高橋 合計 150
２位　田中 合計 145
3位　鈴木 合計 140

合計しない場合は下記の方法でできたのですが同じ名前の人の合計の順位がわかりませんでした。
SELECT (SELECT COUNT(*)+1 from ranking as r1 WHERE r1.tensu > ranking.tensu) as rank

どのようなsql文だと可能でしょうか

Answer 1

CREATE 文

CREATE TABLE `score` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(11) unsigned NOT NULL,
  `score` int(11) unsigned DEFAULT NULL,
  `create_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Sample データ

INSERT INTO `score` (`id`, `user_id`, `score`, `create_at`)
VALUES
	(1,1,30,'2018-10-13 00:39:43'),
	(2,1,70,'2018-10-13 00:39:43'),
	(3,2,68,'2018-10-13 00:39:43'),
	(4,2,43,'2018-10-13 00:39:43'),
	(5,3,66,'2018-10-13 00:39:43'),
	(6,2,10,'2018-10-13 00:39:43'),
	(7,4,89,'2018-10-13 00:39:43'),
	(8,3,129,'2018-10-13 00:39:43'),
	(9,4,32,'2018-10-13 00:39:43');

ランキングを返すSQL

SELECT S.user_id, max(S.score) AS score, DATE(create_at) AS date, count(A.score) + 1 AS rank FROM (
    SELECT user_id, sum(score) AS score, create_at FROM score
    GROUP BY user_id
    ORDER BY score DESC
) S
LEFT JOIN (
    SELECT sum(score) AS score FROM score
    GROUP BY user_id
) A ON S.score < A.score
GROUP BY S.user_id
ORDER BY score DESC;

Comments

[u-sukesan]

ありがとうございます。一度試してみます

Answer 2

こんな漢字で、

SELECT
    (
        SELECT
            COUNT(*)
        FROM
            r r2
        WHERE
            r2.total <= r.total
    ) AS rank
    ,r.name
    ,r.total
FROM
    (
        SELECT
            name
            ,SUM(tensu) AS total
        FROM
            ranking
        GROUP BY
            name
    ) r
ORDER BY
    rank
;

Comments

[u-sukesan]

ありがとうございます。一度試してみます

Answer 3

select
        (
          select count(*)
          from (select sum(tensu) as score from ranking group by name) as t2 
          where score <= t1.score
        ) as rank
      , t1.*
from (select name, sum(tensu) as score from ranking group by name) as t1 
order by 1

同点数は同位となります。

Answer 4

CREATE TABLEや現状のデータのINSERTが提示されていないので、実行していません。

SELECT
    A.NAME
  , A.GOKEI
FROM(
    SELECT
        NAME
      , SUM(TENSU) AS GOKEI
    FROM RANKING
    GRUOP BY NAME
    ) A
ORDER BY A.GOKEI ;

追記

MySQLのSELECT結果に行番号を振る
が使えるでしょうか？

Comments

[u-sukesan]

ありがとうございます。説明足らずで申し訳ございません。
ORDER BYで合計点の高い順にソートされますが、順位を返したいと考えております。

目的としては前日との順位の比較をしたいと考えております

[Orlofsky]

回答を追記しました。

[u-sukesan]

ありがとうございます。理想的な結果だったのですがORDER BYを入れると、行番号が先に振られた後にORDER BYでソートがかかってしまうようですね。行番号がORDER BYのソートの結果次第で反転してしまいます