Запрос next, prev по алфавиту

Oscar · 04 апр 2006, 03:01

MySQL 4

Таблица:

CREATE TABLE list (

	id int(11) auto_increment,

	name varchar(255),

	PRIMARY KEY  (id)

) TYPE=MyISAM ;

Значения:

Код: Выделить всё

1	C
2	D
3	B
4	A

Проблема:

Как выбрать из таблицы элемент,
следующий за id=3
по алфавитному порядку?

упрощение:

...
следующий за элементом name = 'B'

усложнение:

...
циклический выбор
(За элементом D следует элемент A)

Oscar · 04 апр 2006, 04:04

Так, с самой проблемой разобрался: оператор HANDLER

Таблица:

Код: Выделить всё

CREATE TABLE list (

  id int(11) auto_increment,

  name varchar(255),

  PRIMARY KEY  (id),

  KEY idx_name (name)

) TYPE=MyISAM ;

Доступ к необходимым данным:

Код: Выделить всё

HANDLER list OPEN;

HANDLER list READ idx_name FIRST WHERE id = 3;
HANDLER list READ idx_name NEXT;

HANDLER list READ idx_name FIRST WHERE id = 3;
HANDLER list READ idx_name PREV;

HANDLER list CLOSE;

С цикличностью - сложнее ..

Проверяю на php, вернул ли результат - 0 рядков, и беру FIRST (в случае NEXT), или LAST (для PREV) элемент.

Если кто подскажет, как это сделать на чистом SQL - буду весьма благодарен.

Naeel Maqsudov · 09 июн 2006, 04:55

1)
Пусть известно NAME = X

Next:
select top 1 * from list where name>X order by name
Prev:
select top 1 * from list where name<X order by name desc

(не знаю, поддерживается ли в mysql "top N", но если не поддерживается, то не беда можно эту директиву пропустить, но профетчить только первую строку)

2)
Пусть известно ID = X. Это будет не на много сложнее.

Next:
select top 1 * from list where name>(select name from list where ID=X) order by name
Prev:
select top 1 * from list where name<(select name from list where ID=X) order by name desc

Т.е. находим name по id и сводим задачц к предыдущей.

3)
С циклическим действительно сложнее...
Можно использовать UNION и прилепить копию последней строки в начало, а также первой в конец.

Oscar · 17 сен 2006, 16:52

Naeel Maqsudov,

сорри, что поздно отвечаю, никак руки не доходили переделать уже хоть как-то работающие скрипты.

Перво-наперво, спасибо за ответ!

"top N" действительно нету в MySQL, но что это "не беда" - тоже верно, просто другой синтаксис:

SELECT * FROM list WHERE name > X ORDER BY name LIMIT 0, 1

с этим-то проблем нет.

Другое дело, что MySQL 4 не поддерживает вложенных запросов,
а потому пункт 2 (по ID) прийдётся, похоже, выполнять двумя запросами.

Naeel Maqsudov писал(а):3)
С циклическим действительно сложнее...
Можно использовать UNION и прилепить копию последней строки в начало, а также первой в конец.

Прилепить?

SELECT * FROM (SELECT last UNION all UNION first) ...

я правильно понял термин "прилепить" ?

Как я уже упомянул выше, вложеные запросы для меня - роскошь ..

Naeel Maqsudov · 17 сен 2006, 23:39

Правильно.

вложеные запросы для меня - роскошь

А я вот избалован, понимаешь, ораклами и MS Jet....

Absurd · 18 сен 2006, 13:37

Мжно заджоинить таблицу саму на себя, дальше where:

select l2.id
from list l1, list l2
where ув_букву_на_1(значение l2.name) = l1.name

Как в {sic} MySQL можно ув_букву_на_1() я не знаю, но это не проблема наверно

Oscar · 18 сен 2006, 18:15

Absurd, дай я тебя расцАлую !!! :lol:

Спасибо большое!

Увеличивать букву на 1 - не поможет, поскольку на самом деле в поле name (наверное я неправильно обьяснил) вовсе не обязательно находится лишь одна буква. Там целые слова.

Более того, они отнють не должны идти по алфавиту (какой-нибуть буквы может и не быть в таблице).

Но!

(До сих пор не могу понять, как я сам до этого не додумался сразу ..
вроди и перебирал много вариантов .. )

Спасибо тебе за "заджоинить на саму себя"!

Итак я написал следующее:

Код: Выделить всё

SELECT

prev.id AS prevID,
prev.name AS prevName,

this.id AS id,
this.name AS name,

next.id AS nextID,
next.name AS nextName

FROM

List this

LEFT JOIN List prev ON prev.name < this.name
LEFT JOIN List next ON next.name > this.name

WHERE

this.id = XXX

ORDER BY

	prev.name DESC,
	next.name

LIMIT 0 , 1

Такой простой запрос возвращает результат не только следующую и предидущую инфо одновременно, а ещё и всё содержание текущей!

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

Если кто придумает - буду благодарен, нет, - и так сойдёт.

Oscar · 19 сен 2006, 15:44

та-тааам !!!

Код: Выделить всё

SELECT

IF(ISNULL(prev.id),last.id,prev.id) AS prevID,
IF(ISNULL(prev.name),last.name,prev.name) AS prevName,

this.id AS id,
this.name AS name,

IF(ISNULL(next.id),first.id,next.id) AS nextID,
IF(ISNULL(next.name),first.name,next.name) AS nextName

FROM

list this

LEFT JOIN list prev ON prev.name < this.name
LEFT JOIN list next ON next.name > this.name
JOIN list first
JOIN list last

WHERE

this.id = XXX

ORDER BY

   prev.name DESC,
   next.name,
   first.name,
   last.name DESC

LIMIT 0 , 1

теперь тему можно считать закрытой

P.S. а шоб ты всю жизнь такие запросы писал ...

Oscar · 19 сен 2006, 16:29

На таблице 100 записей получил:

MySQL писал(а):#1030 - Got error 28 from table handler

почитал, говорят ТЕМПа не хватает )))

Заменил на:

Код: Выделить всё

SELECT

IF&#40]

работает, но всё равно - долго ..

а потому вижу следующие варианты:

1. либо заменить всепожирающий JOIN на UNION вот так:

[code](SELECT

prev.id AS prevID,
prev.name AS prevName,

this.id AS id,
this.name AS name,

next.id AS nextID,
next.name AS nextName

FROM

list this

LEFT JOIN list prev ON prev.name < this.name
LEFT JOIN list next ON next.name > this.name

WHERE

this.id = 25

ORDER BY

	prev.name DESC,
	next.name

LIMIT 0,1)

UNION

(SELECT

last.id AS prevID,
last.name AS prevName,

NULL,
NULL,

first.id AS nextID,
first.name AS nextName

FROM

list first,
list last

ORDER BY

	last.name DESC,
	first.name

LIMIT 0,1)

2. либо оставить дополнительный опрос, в случае NULL (ведь не так уж часто это бывает, зачем же лишний раз МуСКЛь напрягать .. )

Absurd · 19 сен 2006, 17:16

Такой трюк не приделать?

http://www.shawnolson.net/a/730/