Сделал Вы пробуете это: emacs - геометрия 110x58+200+2 &
Найденный в:
Обновление:
Как указал @Steve Kass
, я не заметил, что вам нужны только первые три игры.
Вот обновленная версия:
В SQL Server
, Oracle
и PostgreSQL 8.4
:
SELECT gamename, user
FROM (
SELECT r.gamename, user,
ROW_NUMBER() OVER (PARTITION BY game ORDER BY times_played DESC) rn,
FROM (
SELECT gamename, ROW_NUMBER() OVER (ORDER BY SUM(times_played) DESC) AS game_rn
FROM results
GROUP BY
gamename
) g
JOIN results r
ON r.gamename = g.gamename
WHERE game_rn <= 3
) q
WHERE rn <= 3
ORDER BY
gamename, times_played DESC
В MySQL
:
SELECT ro.gamename, ro.user
FROM (
SELECT gamename, SUM(times_played) AS rank
FROM results
ORDER BY
rank DESC
LIMIT 3
) rd
JOIN results ro
ON ro.gamename >= rd.gamename
AND ro.gamename <= rd.gamename
AND
(ro.times_played, ro.id) <=
(
SELECT ri.times_played, ri.id
FROM results ri
WHERE ri.gamename = rd.gamename
ORDER BY
ri.times_played DESC, ri.id DESC
LIMIT 2, 1
)
ORDER BY
gamename, times_played DESC
Вам понадобится ПЕРВИЧНЫЙ КЛЮЧ
для работы этого запроса при условии, что он называется id
.
Это объясняется более подробно в этой статье в моем блоге:
В PostgreSQL 8.3
и ниже:
SELECT gamename, ((ri)[s]).user
FROM (
SELECT gamename, ri, generate_series(1, 3) AS s
FROM (
SELECT ro.gamename,
ARRAY
(
SELECT ri
FROM results ri
WHERE ri.gamename = ro.gamename
ORDER BY
times_played DESC
LIMIT 3
) AS ri
FROM (
SELECT gamename, SUM(times_played) AS rank
FROM results
ORDER BY
rank DESC
LIMIT 3
) rd
) q
) q2
ORDER BY
gamename, s
I don't think Quassnoi noticed that you asked for the top users only for the top three games (based on total times_played). Here's a query for that (not tested on real data, since no CREATE TABLE and INSERT statements were given). I also include ties, which Quassnoi didn't, just to show you that option.
with GamesPlays(gamename,totalPlays) as (
select
gamename, sum(times_played)
from results
group by gamename
), GamesRanked(gamename,gameRank) as (
select
gamename,
rank() over (
order by totalPlays desc
)
from GamesPlays
), ResultsRanked(gamename,user,userRank) as (
select
gamename,
user,
rank() over (
partition by user
order by times_played desc
)
from results;
)
select
G.gamename, R.user
from ResultsRanked as R
join GamesRanked as G
on G.gamename = R.gamename
where gameRank <= 3
and userRank <= 3
order by
gameRank,userRank;