Возможно, кому-то эта задача покажется пустяковой, но лично я потратил на неё несколько часов, израсходовав подсказки «мнение зала» и «звонок другу». Зачем я это решал? Ответ прост: мне действительно нужно было реализовать такой подход для моего небольшого сайтика Одио.ру. Если вкратце, то там публикуются записи с самых разных сайтов, стягиваемые по RSS. Сложность в том, что даты в этих записях могут полностью совпадать (даже в рамках одной ленты), при этом последовательность ID имеет смысл только в рамках одной ленты, но никак не влияет на весь поток записей. Итак, давайте перейдем к условиям задачи.
Поскольку это блог о MySQL, то приведу сразу
Теперь заполняем её тестовыми данными:
В итоге получаем следующую таблицу (
Как видно, сейчас таблица отсортирована по ID и даты идут в неправильном порядке.
Теперь, собственно, сама задача: на входе имеем одну из записей (то есть, нам известны ID и DATE), нужно получить ID соседних записей (предыдущей и следующей), при этом, если DATE совпадает, тогда у предыдущей записи будет ID меньше текущей, а у следующей — больше.
Наблюдательный читатель сразу поймет, что если сделать просто (для предыдущей записи):
Чтобы упростить, давайте будем искать только предыдущую запись. Начинаем с 5 записи, имеем ID=5 и DATE='2010-03-01 14:00:00'. Нужно получить запись 3, и далее получаем уже для условий ID=3 и DATE='2010-03-01 13:00:00', и так далее…
Уточню сразу, задача имеет как минимум одно решение ;) Один запрос — одна предыдущая запись, тот же запрос с другими параметрами — следующая предыдущая запись. То есть, вариант «получить всё и ходить по этому» не подходит. Также не подходит вариант «добавить колонку ORDER_NUM и перестраивать его для всей таблицы при добавлении новой записи». Ещё больше не подходит вариант «записывать ID уже показанных записей и исключать их из выборки».
В общем, нужен «честный» запрос, который по ID и DATE текущей записи вернёт настоящий ID настоящей предыдущей записи.
Дабы избежать лишнего флейма в комментариях, отвечаю сразу: да, я сознательно повесил ссылку на адрес своего сайта, потому что он является живым примером применения решения данной задачи. Ну и, естественно, как и любому другому сайту, ему хочется, чтобы на него почаще заходили ;)
Также, я оставляю за собой право использовать ваш запрос, если он окажется лучше того, что придумал я ;) Естественно, с вашего разрешения…
Если в условиях задачи что-то непонятно, я с удовольствием уточню в комментариях.
И, пожалуйста, не нужно писать: «Что за бред? Используйте %framework_name%, он всё сделает за вас...» — мне это не интересно, да и блог о MySQL, задача на сортировку в MySQL, так что всё делаем в рамках MySQL.
UPDATE Найдено 1 решение:
Прислал: SabMakc
Есть и другие решения, пожалуй сложнее представленного, но, тем не менее, вы можете продолжить поиски…
UPDATE 2 Привожу «сложное» трёхэтажное решение этой задачи:
Это моё решение. По сути, оно делает то же самое, что и предыдущее решение SabMakc, но немного через другие ворота…
Поскольку это блог о MySQL, то приведу сразу
SHOW CREATE TABLE
для тестовой таблицы нашей задачи:CREATE TABLE `test` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`date` datetime NOT NULL,
`content` varchar(255) NOT NULL,
PRIMARY KEY (`id`),
KEY `date` (`date`)
) ENGINE=MyISAM;
Теперь заполняем её тестовыми данными:
INSERT INTO `test` (`id` ,`date` ,`content`)
VALUES (NULL , '2010-03-01 11:00:00', 'Test 1'),
(NULL , '2010-03-01 12:00:00', 'Test 2'),
(NULL , '2010-03-01 13:00:00', 'Test 4'),
(NULL , '2010-03-01 12:00:00', 'Test 3'),
(NULL , '2010-03-01 14:00:00', 'Test 5');
В итоге получаем следующую таблицу (
SELECT * FROM `test` ORDER BY `id`
):| 1 | 2010-03-01 11:00:00 | Test 1 |
| 2 | 2010-03-01 12:00:00 | Test 2 |
| 3 | 2010-03-01 13:00:00 | Test 4 |
| 4 | 2010-03-01 12:00:00 | Test 3 |
| 5 | 2010-03-01 14:00:00 | Test 5 |
Как видно, сейчас таблица отсортирована по ID и даты идут в неправильном порядке.
Теперь, собственно, сама задача: на входе имеем одну из записей (то есть, нам известны ID и DATE), нужно получить ID соседних записей (предыдущей и следующей), при этом, если DATE совпадает, тогда у предыдущей записи будет ID меньше текущей, а у следующей — больше.
Наблюдательный читатель сразу поймет, что если сделать просто (для предыдущей записи):
SELECT `id` FROM `test` WHERE `date` <= $date AND `id` != $id ORDER BY `date` DESC, `id` DESC LIMIT 1
, тогда мы «зациклимся» между записями 2 и 4, потому что у них одинаковые DATE — то есть, 2 будет предыдущей для 4, а 4 будет предыдущей для 2. При этом важно, чтобы у крайних записей (1 и 5) в качестве соседних (соответственно, предыдущей для 1 и следующей для 5) ничего не возвращалось.Чтобы упростить, давайте будем искать только предыдущую запись. Начинаем с 5 записи, имеем ID=5 и DATE='2010-03-01 14:00:00'. Нужно получить запись 3, и далее получаем уже для условий ID=3 и DATE='2010-03-01 13:00:00', и так далее…
Уточню сразу, задача имеет как минимум одно решение ;) Один запрос — одна предыдущая запись, тот же запрос с другими параметрами — следующая предыдущая запись. То есть, вариант «получить всё и ходить по этому» не подходит. Также не подходит вариант «добавить колонку ORDER_NUM и перестраивать его для всей таблицы при добавлении новой записи». Ещё больше не подходит вариант «записывать ID уже показанных записей и исключать их из выборки».
В общем, нужен «честный» запрос, который по ID и DATE текущей записи вернёт настоящий ID настоящей предыдущей записи.
Дабы избежать лишнего флейма в комментариях, отвечаю сразу: да, я сознательно повесил ссылку на адрес своего сайта, потому что он является живым примером применения решения данной задачи. Ну и, естественно, как и любому другому сайту, ему хочется, чтобы на него почаще заходили ;)
Также, я оставляю за собой право использовать ваш запрос, если он окажется лучше того, что придумал я ;) Естественно, с вашего разрешения…
Если в условиях задачи что-то непонятно, я с удовольствием уточню в комментариях.
И, пожалуйста, не нужно писать: «Что за бред? Используйте %framework_name%, он всё сделает за вас...» — мне это не интересно, да и блог о MySQL, задача на сортировку в MySQL, так что всё делаем в рамках MySQL.
UPDATE Найдено 1 решение:
SELECT `id`
FROM `test`
WHERE `date` < $date or (`date` = $date and `id` < $id)
ORDER BY `date` DESC, `id` DESC LIMIT 1
Прислал: SabMakc
Есть и другие решения, пожалуй сложнее представленного, но, тем не менее, вы можете продолжить поиски…
UPDATE 2 Привожу «сложное» трёхэтажное решение этой задачи:
SELECT `id`, `date`, IF (`date` = $date AND id < $id, 0, 1) AS `ordr`
FROM `test`
WHERE `date` <= $date AND `id` != $id
HAVING `date` < IF (`ordr` = 1, $date, NOW())
ORDER BY `ordr`, `date` DESC, `id` DESC
LIMIT 1
Это моё решение. По сути, оно делает то же самое, что и предыдущее решение SabMakc, но немного через другие ворота…