Group By при учете Order BY из двух таблиц

[ScDewt]

Доброго времени суток!

Есть вопрос по order by и group by - вопрос изъезженный вдоль и поперек, но работающего решения не придумал для себя. Итак к сути:

2 таблицы:

table: train_plan (план тренировок в спортзале)
fields: id, id_exercise, data (номер записи, номер упражнения, дата на которую упражнение запланировано - timestamp)

+---+---------------+------+
| id | id_exercise | data |
+---+---------------+------+
| 0 | 2 | даааавно |
| 1 | 1 | понедельник |
| 2 | 2 | понедельник |
| 3 | 2 | вторник |
| 4 | 3 | вторник |
| 5 | 2 | четверг |



table: train_result (результаты тренировок)
fields: id_train_plan, id_try, value (номер записи из таблицы train_plan, номер подхода(попытки), значение - поднятый вес к примеру)

+-----------------+-------+--------+
| id_train_plan | id_try | value |
+-----------------+-------+--------+
| 0 | 1 | x |
| 0 | 2 | x |
| 2 | 1 | x |
| 2 | 2 | x |
| 4 | 1 | x |
| 4 | 2 | x |
| 5 | 1 | x |
| 5 | 2 | x |

Задача: есть дата "среда", есть упражнения 2 и 3. Надо на данную дату(среда) по каждому упражнению (2,3) вывести список всех подходов(id_try) + значения по этим подходам(value) ЗА предыдущий раз занятия этим упражнением. Т.е. получить нечто вроде:

+----------------+--------+--------+---------------+
| id_train_plan | id_try | value | id_exercise |
+----------------+--------+--------+---------------+
| 2 | 1 | x | 2
| 2 | 2 | x | 2
| 4 | 1 | x | 3
| 4 | 2 | x | 3

Проблемы:
- предыдущие результаты занятия каждым упражнением могут быть в разных датах, даже если упражнения запланированы на одни и теже дни - т.е. какие то данные пользователю лень было вносить или он тупо не сделал упражнение, хоть оно и запланировано. К примеру упражнения 2 и 3 у нас запланированы во вторник, но по упражнению 2 на вторник у нас нет результатов, зато они есть в понедельнике.
- надо выводить все попытки последнего занятия.
- структуру таблиц крайне тяжело менять - много завязано всего
- пользователь может заполнять\планировать дни в хаотичном порядке - потому id_train_plan не хронологичен


Решения (ошибочные ):
Вначале сджойнить обе таблички и определить ближайшие последние даты занятия каждым упражнением, затем по этим датам и упражнениям вытащить всю остальную информацию.

SELECT tr.*, tp.id_exercise
FROM train_result AS tr
INNER JOIN train_plan AS tp ON (tp.id=tr.id_train_plan)
WHERE 
    tp.date IN (
        SELECT MAX(tp.date)
        FROM train_result AS tr
        INNER JOIN train_plan AS tp ON (tp.id=tr.id_train_plan)
        WHERE tp.id_user=4
            AND tp.id_exercise IN (2,3)
	    AND tp.date < "среда"
        GROUP BY id_exercise) 
    AND tp.id_user=4
    AND tp.id_exercise IN (2,3)

тут ошибка будет, т.к. во вложенном селекте вытягиваются только даты, без привязки к конкретным урпжанениям, т.е. в некоторых случаях я буду получать дублированные упражнения но со старой датой.

Думал вытягивать данные по последним годным номерам записей в планах:

SELECT tr.*, tp.id_exercise
FROM train_result AS tr
INNER JOIN train_plan AS tp ON (tp.id=tr.id_train_plan)
WHERE 
    tp.id IN (
        SELECT MAX(tr.id_train_plan)
        FROM train_result AS tr
        INNER JOIN train_plan AS tp ON (tp.id=tr.id_train_plan)
        WHERE tp.id_user=4
            AND tp.id_exercise IN (2,3)
        GROUP BY id_exercise
    )

но не факт что MAX(tr.id_train_plan) - будет отображать действительно ближайший предыдущий результат, т.к. id_train_plan не хронологичен.

вопрос: какие есть еще варианты вытащить эти чертовы данные? в каком направлении копать?

p.s. Уже голова в 3 ночи не варит, потому прошу прощения если тут все непонятно.

 

[prolis]

мне тоже лень думать в пятницу, поэтому сначала решим задачу в лоб, без оптимизации
1.Выбираем последние результаты по каждому упражнению

select id_exercise,max(data) as maxdate 
from train_plane p,train_result r
where p.id=r.id_train_plan
group by id_exercise

2. теперь предпоследние

select p.id_exercise, max(p.data) as prev, max(maxdate) as last from
(select id_exercise,max(data) as maxdate 
from train_plane p,train_result r
where p.id=r.id_train_plan
group by id_exercise) as last_train,
train_plane p,train_result r
where p.id=r.id_train_plan
and last_train.id_exercise=p.id_exercise
and last_train.maxdate>p.data
group by p.id_exercise

теперь мы знаем упражнение и две даты - последняя и предпоследняя, по которым по данному упражнению были результаты. Осталось залинковать (по номеру упражнения и дате) этот селект с таблицей результатов для показа номеров попыток и результатов в эти дни

 

[ScDewt]

prolis спасибо за направление в верном пути

Допилил ваш запрос под свои нужды и получил:

SELECT tp.id_exercise, tr.* 
FROM
    ( 
    SELECT tp.id_exercise, max(tp.date) AS maxdate 
    FROM train_plan AS tp
    INNER JOIN train_result AS tr ON (tp.id = tr.id_train_plan)
    WHERE 
        tp.id_exercise IN (2,3)
        AND tp.date < #среда#
    GROUP BY tp.id_exercise
    ) 
AS lt
INNER JOIN train_plan AS tp ON tp.id_exercise = lt.id_exercise AND tp.date = lt.maxdate
INNER JOIN train_result AS tr ON tp.id = tr.id_train_plan

Дает мне то что надо.

p.s. про использование вложенного запроса в from я что то сразу не подумал, и как оказалось зря.