Предположим, у меня есть игра, в которую могут играть 2, 3 или 4 игрока. Я отслеживаю такую игру в своей базе данных (MySQL 5.1) в трех таблицах, приведенных ниже. Я надеюсь, что поля не требуют пояснений:
create table users (id int, login char(8));
create table games (id int, stime datetime, etime datetime);
create table users_games (uid int, gid int, score int);
[Два времени, отслеживаемые в таблице игр, - это время начала и окончания]
Вот некоторые фиктивные данные для заполнения таблиц:
insert into games values
(1, '2011-12-01 10:00:00', '2011-12-01 13:00:00'),
(2, '2011-12-02 11:00:00', '2011-12-01 14:00:00'),
(3, '2011-12-03 12:00:00', '2011-12-01 15:00:00'),
(4, '2011-12-04 13:00:00', '2011-12-01 16:00:00');
insert into users_games values
(101, 1, 10),
(102, 1, 11),
(101, 2, 12),
(103, 2, 13),
(104, 2, 14),
(102, 3, 15),
(103, 3, 16),
(104, 3, 17),
(105, 3, 18),
(102, 4, 19),
(104, 4, 20),
(105, 4, 21);
Теперь мне нужно создать отчет в следующем формате:
gid p1 p2 p3 p4 started ended
1 101 102 [g1] [g1]
2 101 103 104 [g2] [g2]
3 102 103 104 105 [g3] [g3]
4 102 104 105 [g4] [g4]
То есть отчет, который показывает всех игроков, сыгравших в игру, в одном ряду. Мне также нужны их очки и некоторая другая информация из таблицы пользователей, но это уже второй этап. :-)
Я начал с этого:
select g.id, g.stime, g.etime, ug1.uid, ug2.uid, ug3.uid, ug4.uid
from games g, users_games ug1, users_games ug2, users_games ug3, users_games ug4
where
g.id = ug1.gid and
ug1.gid = ug2.gid and
ug1.uid < ug2.uid and
ug2.gid = ug3.gid and
ug2.uid < ug3.uid and
ug3.gid = ug4.gid and
ug3.uid < ug4.uid
Это дает мне все игры, в которых все четыре места были заняты (т.е. только ID игры 3 в вышеприведенных фиктивных данных). Но это только часть нужных мне данных.
Вот моя вторая попытка:
select g.id, g.stime, g.etime, ug1.uid, ug2.uid,
ifnull(ug3.uid, ''), ifnull(ug4.uid, '')
from ( games g, users_games ug1, users_games ug2 )
left join users_games ug3 on ug2.gid = ug3.gid and ug2.uid < ug3.uid
left join users_games ug4 on ug3.gid = ug4.gid and ug3.uid < ug4.uid
where
g.id = ug1.gid and
ug1.gid = ug2.gid and
ug1.uid < ug2.uid
Это дает мне 14 строк с приведенными выше фиктивными данными. Я попытался устранить один источник ошибок, привязав ug1 к записи для игрока с наименьшим UID:
select g.id, g.stime, g.etime, ug1.uid, ug2.uid,
ifnull(ug3.uid, ''), ifnull(ug4.uid, '')
from
( games g, users_games ug1, users_games ug2,
(select gid as g, min(uid) as u from users_games group by g) as xx
)
left join users_games ug3 on ug2.gid = ug3.gid and ug2.uid < ug3.uid
left join users_games ug4 on ug3.gid = ug4.gid and ug3.uid < ug4.uid
where
g.id = xx.g and
ug1.uid = xx.u and
g.id = ug1.gid and
ug1.gid = ug2.gid and
ug1.uid < ug2.uid
Теперь я сократил количество строк до 9, но у меня все еще много ложных данных. Я вижу проблему - например, в игре 3, когда ug1 привязан к пользователю 102, все еще есть три игрока, к которым можно привязать ug2. И так далее. Но я не могу найти способ решить эту головоломку - как я могу в конечном итоге добиться запроса, который выведет 4 строки с игроками в правильном порядке и количестве?
Мне кажется, это должно быть решаемой проблемой в других контекстах. Буду признателен за любую помощь здесь.