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;