MyWackoSite : NsuTs/usefulQueries

Сколько человек в каком туре находится
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


SELECT count(langid),langid FROM `submits` WHERE host like '10.4.16.27' group by langid;