Выбор из базы по максимальной дате у ID

355

19 января 2017, 06:38

Есть mySQL таблица с списком задач

id | mid | s | e | date 
1  | 15  | 1 | 3 | 2017-01-17 00:00:00 
2  | 15  | 1 | 4 | 2017-01-24 00:00:00 
3  | 20  | 2 | 6 | 2017-01-23 00:00:00 
4  | 20  | 2 | 7 | 2017-02-02 00:00:00 
5  | 20  | 2 | 8 | 2017-02-07 00:00:00 
6  | 34  | 3 | 5 | 2016-12-31 00:00:00

Стоит задача выбрать строки в которых максимальная дата заканчивается в течении двух недель. То есть из варианта выше необходимо получить mid 15 и 34 Критерий выборки максимальная дата у каждого уникального mid не должна превышать сегодня + 2 недели, если максимальная дата у mid выше сегодня + 2 недели то такой mid пропускаем. Надеюсь понятно объяснил.

Подскажите с чего начать, что то ни как не соображу как получить такие данные. Пробовал запросы и с group by и с DISTINCT но все они не делали что нужно.

Или же проще брать все данные и на PHP уже отсеивать?

Answer 1

Оконные функции позволяют добиться нужного результата, правда MySql их до сих пор не поддерживает.

Но может и хорошо, т.к. оконные функции работают дольше, чем несколько запросов. Тут несколько вариантов:

использовать подзапрос
2 запроса поочереди
использовать view или временную таблицу

Я опишу вариант с подзапросом:

SELECT t1.*
FROM `tasks` t1
INNER JOIN (
    SELECT `mid`, MAX(`date`) AS `date`
    FROM `tasks`
    WHERE `DATE` > CURDATE() AND `DATE` < ADDDATE(CURDATE(), 14) 
    GROUP BY `mid`
) t2 ON t1.`mid` = t2.`mid` AND t1.`date` = t2.`date`;

Обязательно использовать индексы для mid и date. В данном примере мы сначала выбираем нужные даты по уникальным mid, а потом получаем все остальные данные.

UPD

Что-бы mid 34 тоже входил в выборку в условии предиката WHERE убираем выражение DATE > CURDATE() AND

UPD2 По замечанию @Akina перечитал вопрос и понял, что результат все таки должен быть другим. В вопросе упоминается максимальная дата группировки, а не просто дата. Правильный запрос:

SELECT t1.*
FROM `tasks` t1
INNER JOIN (
    SELECT `mid`, MAX(`date`) AS `max_date`
    FROM `tasks`
    GROUP BY `mid`
    HAVING `max_date` < ADDDATE(CURDATE(), 14) 
) t2 ON t1.`mid` = t2.`mid` AND t1.`date` = t2.`max_date`;

Answer 2

mysql> create table tasks
    -> (id int,mid int,s int,e int,`date` datetime);
Query OK, 0 rows affected (0.06 sec)
mysql> insert into tasks
    -> (id,mid,s,e,`date`)
    -> values
    -> (1,15,1,3,'2017-01-17 00:00:00'),
    -> (2,15,1,4,'2017-01-24 00:00:00'),
    -> (3,20,2,6,'2017-01-23 00:00:00'),
    -> (4,20,2,7,'2017-02-02 00:00:00'),
    -> (5,20,2,8,'2017-02-07 00:00:00'),
    -> (6,34,3,5,'2016-12-31 00:00:00');
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0
mysql> SELECT t1.*
    -> FROM `tasks` t1
    -> INNER JOIN (
    ->     SELECT `mid`, MAX(`date`) AS `date`
    ->     FROM `tasks`
    ->     WHERE `DATE` > CURDATE() AND `DATE` < ADDDATE(CURDATE(), 14)
    ->     GROUP BY `mid`
    -> ) t2 ON t1.`mid` = t2.`mid` AND t1.`date` = t2.`date`;
+------+------+------+------+---------------------+
| id   | mid  | s    | e    | date                |
+------+------+------+------+---------------------+
|    2 |   15 |    1 |    4 | 2017-01-24 00:00:00 |
|    3 |   20 |    2 |    6 | 2017-01-23 00:00:00 |
+------+------+------+------+---------------------+
2 rows in set (0.03 sec)
mysql> SELECT t1.*
    -> FROM `tasks` t1
    -> INNER JOIN (
    ->     SELECT `mid`, MAX(`date`) AS `date`
    ->     FROM `tasks`
    ->     WHERE /* `DATE` > CURDATE() AND */ `DATE` < ADDDATE(CURDATE(), 14)
    ->     GROUP BY `mid`
    -> ) t2 ON t1.`mid` = t2.`mid` AND t1.`date` = t2.`date`;
+------+------+------+------+---------------------+
| id   | mid  | s    | e    | date                |
+------+------+------+------+---------------------+
|    2 |   15 |    1 |    4 | 2017-01-24 00:00:00 |
|    3 |   20 |    2 |    6 | 2017-01-23 00:00:00 |
|    6 |   34 |    3 |    5 | 2016-12-31 00:00:00 |
+------+------+------+------+---------------------+
3 rows in set (0.00 sec)
mysql> SELECT t1.*
    -> FROM `tasks` t1
    -> INNER JOIN (
    ->     SELECT `mid`, MAX(`date`) AS `date`
    ->     FROM `tasks`
    ->     GROUP BY `mid`
    ->     HAVING /* `DATE` > CURDATE() AND */ MAX(`DATE`) < ADDDATE(CURDATE(), 14)
    -> ) t2 ON t1.`mid` = t2.`mid` AND t1.`date` = t2.`date`;
+------+------+------+------+---------------------+
| id   | mid  | s    | e    | date                |
+------+------+------+------+---------------------+
|    2 |   15 |    1 |    4 | 2017-01-24 00:00:00 |
|    6 |   34 |    3 |    5 | 2016-12-31 00:00:00 |
+------+------+------+------+---------------------+
2 rows in set (0.00 sec)
mysql>