MyWackoSite : Nsu Ts / useful Queries

Это старая версия NsuTs/usefulQueries за 2011-09-25 12:51:24..

Сколько человек в каком туре находится

SELECT o.title as olymp_title, t.title as tour_title, count( o.title )
FROM users
LEFT JOIN online ON users.userid = online.userid
LEFT JOIN olympiad AS o ON users.current_olympiad = o.id
LEFT JOIN tour AS t ON users.selected_tourid = t.tourid
WHERE (
UNIX_TIMESTAMP( NOW( ) ) - UNIX_TIMESTAMP( lasttime )
) <30 *60
GROUP BY o.title

Сколько задач висит в статусе “queue” – 1, “testing” – 2

SELECT s.host, s.filename, s.smtime, u.title as team, o.title as olymp_title, t.title as tour_title
FROM `submits` AS s
JOIN tasks ON tasks.taskid = s.taskid
JOIN tour AS t ON t.tourid = tasks.tourid
JOIN olympiad AS o ON t.olympid = o.id
JOIN users AS u ON u.userid = s.userid
WHERE s.status =2

MyWackoSite: NsuTs/usefulQueries ...