A
ud1
select * from (
select *, win*1.0/(lose + 0.000001) as rate from (
select tab.player_name as pn, (
SELECT COUNT(1) FROM game, game_result r1, game_result r2
WHERE game.id = r1.game_id and r1.player_name = 'Commandos'
and game.id = r2.game_id and r2.player_name = tab.player_name
and r1.position < r2.position
) as win,
(
SELECT COUNT(1) FROM game, game_result r1, game_result r2
WHERE game.id = r1.game_id and r1.player_name = 'Commandos'
and game.id = r2.game_id and r2.player_name = tab.player_name
and r1.position > r2.position
) as lose
from (
select distinct r.player_name from game_result r where r.game_id in (select r2.game_id from game_result r2 where r2.player_name = 'Commandos')
) as tab)
)
order by rate
select *, win*1.0/(lose + 0.000001) as rate from (
select tab.player_name as pn, (
SELECT COUNT(1) FROM game, game_result r1, game_result r2
WHERE game.id = r1.game_id and r1.player_name = 'Commandos'
and game.id = r2.game_id and r2.player_name = tab.player_name
and r1.position < r2.position
) as win,
(
SELECT COUNT(1) FROM game, game_result r1, game_result r2
WHERE game.id = r1.game_id and r1.player_name = 'Commandos'
and game.id = r2.game_id and r2.player_name = tab.player_name
and r1.position > r2.position
) as lose
from (
select distinct r.player_name from game_result r where r.game_id in (select r2.game_id from game_result r2 where r2.player_name = 'Commandos')
) as tab)
)
order by rate
select winrates.pn,
winrates.win,
winrates.lose,
round(winrates.rate, 4) rate,
points.points,
points.place
from (
select *, win * 1.0 / 30 as rate
from (
select tab.player_name as pn,
(
SELECT COUNT(1)
FROM game,
game_result r1,
game_result r2
WHERE game.id = r1.game_id
and r1.player_name = 'karloid'
and game.id = r2.game_id
and r2.player_name = tab.player_name
and r1.position < r2.position
) as win,
(
SELECT COUNT(1)
FROM game,
game_result r1,
game_result r2
WHERE game.id = r1.game_id
and r1.player_name = 'karloid'
and game.id = r2.game_id
and r2.player_name = tab.player_name
and r1.position > r2.position
) as lose
from (
select distinct r.player_name
from game_result r
where r.game_id in (select r2.game_id from game_result r2 where r2.player_name = 'Commandos')
) as tab)
) winrates,
(select p.player_name,
sum(points) points,
row_number() over (ORDER BY sum(points) DESC) place
from (
select gr.player_name, 2 points
from game_result gr
where gr.position == 1
) p
group by p.player_name) points
where winrates.pn == points.player_name
order by points.place
расширил запросик, добавил еще столбцы points, place
