Добро пожаловать в форум, Guest >> Войти | Регистрация | Поиск | Правила | | В избранное | Подписаться | ||
Все форумы / MySQL |
![]() ![]() |
mahoune Member Откуда: Moscow Сообщений: 5653 |
Задача: Пронумеровать строки в запросе Достаточно часто возникает необходимость пронумеровать возвращаемые MySQL строки средствами самой базы данных. Решение: SELECT @i := @i + 1 AS row_number, your_table.* FROM your_table, (select @i:=0); В ряде случаев вы можете получить следующий ответ от MySQL: Every derived table must have its own alias Тогда необходимо добавить алиас (псевдоним): SELECT @i := @i + 1 AS row_number, your_table.* FROM your_table, (select @i:=0) AS z; или воспользоваться механизмом присвоением нулевого значения переменной @i перед запросом, если такая возможность присутствует. Т.к. в случае если эти два запроса буду выполнены в разных подключениях, толку будет ноль, а вернее NULL. SET @id := 0; SELECT @id := @id + 1 AS row_number, your_table.* FROM your_table; .mahoune. Сообщение было отредактировано: 22 янв 10, 11:39 |
2 авг 09, 19:10 [7488722] Ответить | Цитировать Сообщить модератору |
mahoune Member Откуда: Moscow Сообщений: 5653 |
Выборка нескольких последних записей в неких группах (а.к.а TOP N queries in categories). Допустим, имеется следуюшая задача: Появилась необходимость получить несколько строк, отсортированных по какому-то признаку и сгруппированных по какому-либо полю. Например, поиск 5 последних сообщений по каждой теме. Пробовал получать вот таким запросом: SELECT * FROM (SELECT DISTINCT type_a FROM table) AS t1 INNER JOIN table AS t2 ON t2.id IN (SELECT id FROM table WHERE type_a=t1.type_a ORDER BY date_a DESC LIMIT 5) но почему-то он выдает ошибку, что LIMIT в подзапросах не поддерживается (вроде это очень простая операция, не понимаю почему ее нет?). Единственный выход какой я вижу - это UNION, но при большом количестве типов это будет не очень удобно. Как быть в данном случае, может есть какой-то вариант получше? Даже если ЛИМИТ проходит, последний запрос будет выпонятся для каждого значения колонки "type_a". О производительности здесь говорить не приходится. Решение с использованием переменных: 1. отсортировать таблицу по "type_a" и по "date_a DESC" 2. пронумеровать записи отдельно для кaждого значения "type_a" 3. выбрать первые 5 записи в каждой группе Реализация: 1. отсортировать таблицу по "type_a" и по "date_a DESC". Добавим селект для задания переменных @rownum -- для хранения текушего счетчика @typex -- для хранения текушего значения "type_a" select * from tableA ta, (select @rownum:=1, @typex:='_') zz order by type_a, date_a DESC 2. пронумеровать записи отдельно для кaждого значения "type" select ta.*, if(@typex=ta.type_a, -- если "type_a" не изменился @rownum:=@rownum+1, -- то увеличить счетчик @rownum:=1+least(0,@typex:=ta.type_a)) AS rown -- иначе скинуть счетчик на 1 -- и занести новое значение в @typex from tableA ta, (select @rownum:=1, @typex:='_') zz order by type_a, date_a DESC 3. выбрать первые 5 записи в каждой группе select * from ( select ta.*, if(@typex=ta.type_a, @rownum:=@rownum+1, @rownum:=1+least(0,@typex:=ta.type_a)) rown from tableA ta, (select @rownum:=1, @typex:='_') zz order by type_a, date_a DESC ) yy where rown < 6 Примечания: @rownum:=1+least(0,@typex:=ta.type) гарантирует обновление @typex после проверки "if(@typex=ta.type..." Спасибо ![]() |
2 авг 09, 23:57 [7489069] Ответить | Цитировать Сообщить модератору |
Alex_Ustinov Member [заблокирован] Откуда: Nickel Сообщений: 3703 |
Пронумеровать поле таблицы "по-порядку"update your_table set field1 =(select @a:= @a + 1 from (select @a:= 0) s) order by field2, field3 ... Сообщение было отредактировано: 3 авг 09, 11:08 |
3 авг 09, 09:17 [7489347] Ответить | Цитировать Сообщить модератору |
magicprinc Member Откуда: Сообщений: 127 |
Подробный разбор всех техник работы с переменными в запросе Advanced MySQL user variable techniques http://www.xaprb.com/blog/2006/12/15/advanced-mysql-user-variable-techniques/ Финальный вариант X элементов из группы у него выглядит так: select *, @num from fruits where (@num := if(type = @type, @num + 1, 1)) is not null and (@type := type) is not null and (@num <= 2); Сообщение было отредактировано: 26 ноя 09, 21:46 |
26 ноя 09, 21:45 [7984809] Ответить | Цитировать Сообщить модератору |
Богдан Гоцкий Member Откуда: Львов Сообщений: 504 |
Где гарантия, что порядок присвоений переменных @num и @type будет именно в том порядке как написано в запросе? По-моему этот запрос вообще недетерминирован и возвращает нужную нумерацию чисто случайно. |
||
22 фев 10, 12:07 [8378615] Ответить | Цитировать Сообщить модератору |
an0nym Member Откуда: Сообщений: 7076 |
Богдан Гоцкий, в принципе любая (за очень редким исключением) работа с переменными в резалт-сете, содержащем более одной записи, недетерминированна. |
22 фев 10, 12:27 [8378678] Ответить | Цитировать Сообщить модератору |
javajdbc Member Откуда: Montreal Сообщений: 17715 |
Действительно, порядок выполнения в СЕЛЕКТ и в WHERE групе не определен -- теоретически. Практически я не видел доказаного случая неверного порядка. Етот вопрос известен и решается, правда через задницу, но все же решается. Посмотри второй пост в етой ветке, в самом конце:
|
||||
22 фев 10, 17:23 [8379530] Ответить | Цитировать Сообщить модератору |
javajdbc Member Откуда: Montreal Сообщений: 17715 |
Странная сентенция.... если делать правильно, то все будет детерминировано.... если неправильно делать, то можно и пенис сломать... |
||
22 фев 10, 17:29 [8379549] Ответить | Цитировать Сообщить модератору |
an0nym Member Откуда: Сообщений: 7076 |
javajdbc, я примерно это и имел в виду. :) |
22 фев 10, 17:50 [8379605] Ответить | Цитировать Сообщить модератору |
miksoft Member Откуда: Сообщений: 38702 |
|
|
27 окт 11, 12:51 [11508269] Ответить | Цитировать Сообщить модератору |
tanglir Member Откуда: Сообщений: 28966 |
|
||
27 окт 11, 15:31 [11509914] Ответить | Цитировать Сообщить модератору |
Arhat109 Member Откуда: из СССР Сообщений: 3428 |
tanglir, Вот понадобилось... а оно... :) Подход с переменными работает только при отсутствии сортировки в запросе, отличной от "по умолчанию" и нумерации только одной таблицы (без join!) Столкнулся с тем, что при задании сортировки в режиме DESC - нумерация переменными или совсем не производится, или пересортировывается также по DESC. Задача: оставить для каждого пользователя форума только по 10 ПОСЛЕДНИХ сообщений... то есть сообщения надо сгруппировать по пользователям, отсортировать в обратном порядке по дате и пронумеровать в прямом порядке (обратно дате!) и те, которые имеют номер в группе больше 10 - удалить нафиг... ну и ещё повеселило: Если таблица сообщений - сборка джойном из нескольких, то управление переменными в блоке where делается столько раз, сколько джойнов в сборке. То есть при двух таблицах (один джойн) - получаем только четные значения, при двух джойнах - кратные трем... и т.д. лечится включением всей сборки и сортировки во вложенный подселект. Но проблема в том, что у меня при записях "сообщений" больше 1млн и с десяток тысяч "пользователей"... внутренний подселект без временной таблицы - валится напрочь "табличка повреждена..." или как оно там... запись во временную табличку - около 2 минут... |
4 апр 12, 19:36 [12367029] Ответить | Цитировать Сообщить модератору |
javajdbc Member Откуда: Montreal Сообщений: 17715 |
Arhat109, да... переменные в мускл-е (и вообше в СКЛ) -- дело идеологически инородное... и требует осторожности в использовании... 1. было показано, что в зависимости от использовании-или-неиспользовании индексов на джоинте переменные евалюировались по разному. Т.е. для чистого СКЛ-а работа оптимизатора не влияет на результат, но может повлиять на вычисление переменные 2. единственый прямой способ добится однозначного порядка -- как вы отметили выше -- использование подселектов -- соединять таблицы внутри под-селекта но работать с переменными в следуюшем селекте. типа: 2a select ....переменные....from (select * from A,B where ...ORDER BY ...) 2b select ....переменные....from (select * from A,B where ....) ORDER BY ... наверное 2б будет правильнее 3. В сложных случаях -- подселекты являются проблемой ибо на подселекты нет индексов -- т.е. лефт джоинт на подселект будет медленым. 4. Выборка ТОП-Н всегда предпологает двойной проход: нумерация а потом отсечение -- я наблюдал случаи когда по нехватки памяти такие квери падали на хард-диск. А ето тормозило запрос на несколько порядков... На милионах записей это реально (я видел срыв на 400-600К на домашнем компе) 5. рекомендую создать новый топик по теме, поставить задачу, выдать пример данных, описать таблицы и индексы 6. Переменные -- не панацея, вполне может случится что работа через промежуточнуе таблицы будет быстрее... или с переменными но по 100К за раз (что б все в памяти)... ...или...надо посмотреть.. :-) |
8 апр 12, 04:16 [12382612] Ответить | Цитировать Сообщить модератору |
Arhat109 Member Откуда: из СССР Сообщений: 3428 |
javajdbc, не вижу смысла создавать отдельную тему. Надо оставить мой пост и ваш коммент "для ограничения применения", дабы народ не впадал в маразм, наступая на одни грабли. Этот пост можно потереть за ненадобностью. Решается всё достаточно легко предварительным созданием временной таблицы на базе подселектов только с нужными полями и навешиванием нужного количества индексов для последующей быстрой выборки с переменными... больше миллиона записей пролетает на удаление (суммарно с созданием временной таблицы) за 30-60 секунд... для разового запроса на чистку базы - мелочь. Даже если делать раз в день. Просто табличку мне надо было сразу создавать правильную, а не SELECT * FROM... :) Вполне достаточно только id,fkey,last_date |
8 апр 12, 15:01 [12383180] Ответить | Цитировать Сообщить модератору |
Между сообщениями интервал более 1 года. |
alex564657498765453 Member Откуда: Сообщений: 1925 |
select ta.*, if(@typex=ta.type_a, -- если "type_a" не изменился @rownum:=@rownum+1, -- то увеличить счетчик @rownum:=1+least(0,@typex:=ta.type_a)) AS rown -- иначе скинуть счетчик на 1 -- и занести новое значение в @typex from tableA ta, (select [color=red]@rownum:=1[/color], @typex:='_') zz order by type_a, date_a DESC и что никого не смутила ошибка??? первая група начнёт нумероваться с 2 а не с 1!!! и логически ясно, и лично только что проверил на мускле. |
|
22 май 13, 14:02 [14332883] Ответить | Цитировать Сообщить модератору |
javajdbc Member Откуда: Montreal Сообщений: 17715 |
alex564657498765453, по идее, первая група пройдет на строке: @rownum:=1+least(0,@typex:=ta.type_a)) AS rown -- иначе скинуть счетчик на 1 ...и нумерация пойдет с единицы. Двойка будет если самая первая група будет иметь название '_'. Если у вас по другому, приведите полный тест-кейс. Бывают случаи когда жоинты сбивали порядок вывода на блок СЕЛЕКТ. |
22 май 13, 16:06 [14334014] Ответить | Цитировать Сообщить модератору |
alex564657498765453 Member Откуда: Сообщений: 1925 |
имеется ТАБЛИЦА - записи покупокCREATE TABLE `sales` ( `idsale` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'PRIMARY KEY', `fk_idgood` INT(11) NOT NULL COMMENT 'FOREIGN KEY goods', `fk_iduser` INT(11) NOT NULL COMMENT 'FOREIGN KEY users' -- ..... other information fields ); ЗАДАЧА Получить список самых активных покупателей и число их покупок. (если есть несколько человек, купивших больше всех, вывести нужно каждого) ожидаемый результат
при этом, не существует пользователя купившего больше чем 120. РЕШЕНИЕ без переменных SELECT t1.fk_iduser, t1.total FROM ( -- получим список всех пользователей с числом покупок каждого -- --№юзер Кол-во покупок -- -- 1 32 -- 2 12 -- ... -- 23 120 -- 24 95 -- 25 100 -- ... -- 41 120 -- ... -- 77 120 -- ... SELECT fk_iduser, COUNT(fk_idgood) as 'total' FROM sales GROUP BY fk_iduser ) t1 JOIN ( -- получим любого одного активного покупателя -- нам нужно лишь определить это самое число - 120, -- максимальное кол-во покупок -- --№юзер Кол-во покупок -- -- 23 120 -- SELECT fk_iduser, COUNT(fk_idgood) as 'total' FROM sales GROUP BY fk_iduser ORDER BY COUNT(fk_idgood) DESC -- самой первой окажется запись с максимальным значением COUNT(fk_idgood) LIMIT 1 -- только её и возьмём -- благодаря соединению, из общего списка отберём только те, у -- кого число покупок равно максимальному - 120 ) t2 ON t1.total=t2.total -- результат -- 23 120 -- 41 120 -- 77 120 РЕШЕНИЕ с использованием ПЕРЕМЕННЫХ SELECT id_shopper, total FROM ( -- по сути если убрать все переменные, получаемая таблица идентична t1 -- из первого решения -- SELECT id_shopper,@m:=COUNT(idgood) AS 'total',IF(@k<@m,@k:=@m,@k) FROM sales join (SELECT @k:=0) k -- инициализируем значение переменой @k -- псевдоним используем лишь для того, что бы избежать -- возможного ругательства об обязательности каждому подзапросу дать свой псевдоним GROUP BY id_shopper --сначала будет обработана секция from, что даёт исходную таблицу + инициализированную --переменную -- --затем отработает группировка и выполняться будет секция select -- на каждой строке, будет посчитано значение COUNT(idgood), -- также это значение будет присвоено переменой @m -- так же будет проверено, если текущее значение @k меньше @m, то -- обновляем значение @k -- --таким образом после полной отработки вложенного запроса, --@k будет хранить максимальное значение COUNT(idgood) -- в первом решении мы это получали отдельным под-запросом с группировкой - t2 ) t1 WHERE -- єто запрос просто отфильтрует из t1, все записи -- где сумма покупок не равна максимальной, 120 total = @k; -- итого получим тот же результат. ПРЕИМУЩЕСТВА быстродействие - мы не делаем лишний запрос с группировкой и сортировкой. даже сама сортировка более медленный процесс, чем определение максимального значения пробежав по записям и обновляя значение переменной. Сообщение было отредактировано: 6 июн 14, 19:27 |
6 июн 14, 16:54 [16134068] Ответить | Цитировать Сообщить модератору |
Между сообщениями интервал более 1 года. |
Ulibka Member Откуда: Сообщений: 2 |
Нашел упоминания когда рассматривалась одноранговая нумерация. Возникла необходимость внутри группы пронумеровать по возрастанию. Есть таблица сообщений (messages_wt), в этой таблице есть поле тем -TopicID_XF и поле сообщений - message_id, id1 - primary key Внутри одного топика надо пронумеровать сообщения по возрастанию message_id. Задачу решил так: (запрос работающий) UPDATE messages_wt m, (SELECT @row_num:=0) x, (SELECT @prev_value:=0) y, (SELECT @row_num:=IF(@prev_value = TopicID_XF, @row_num + 1, 0) AS RR, TopicID_XF, id1, message_id, @prev_value:=TopicID_XF FROM messages_wt, (SELECT @row_num:=0) x, (SELECT @prev_value:=0) y ORDER BY TopicID_XF , message_id) z SET m.rr = z.RR WHERE (m.id1 = z.id1) Но у меня есть подспудное чуство что зада решена очень коряво. Меня смущает мощный подзапрос. Подзапрос понадобился потому что в set не удается запихнуть присваивание. Собственно вопрос такой - можно ли решить эту задачу без подзапроса ? |
5 янв 18, 06:33 [21082685] Ответить | Цитировать Сообщить модератору |
Ulibka Member Откуда: Сообщений: 2 |
UPD: предыдущий запрос оказался нерабочим. Правильное решение без подзапроса: set @row_num := 0; set @prev_value := 0; update messages_wt m set m.RR= @row_num := IF(@prev_value=TopicID_XF,@row_num+1,0) +least(0,@prev_value := TopicID_XF) order by TopicID_XF,message_id |
5 янв 18, 06:51 [21082706] Ответить | Цитировать Сообщить модератору |
Akina Member Откуда: Зеленоград, Москва, Россия Сообщений: 20978 |
Гм... 7489069 |
||
5 янв 18, 11:07 [21082867] Ответить | Цитировать Сообщить модератору |
Между сообщениями интервал более 1 года. |
Akina Member Откуда: Зеленоград, Москва, Россия Сообщений: 20978 |
(по мотивам одной темы на одном форуме) Тонкость в присвоении инициализированным переменным нового значения. create table test (id int) select 1 id; select id from test; -- обе переменные получат значение 1 set @var1 := (select id from test where id=1); select id into @var2 from test where id=1; select @var1, @var2; -- будет изменено на NULL только значение переменной @var1 set @var1 := (select id from test where id=2); select id into @var2 from test where id=2; select @var1, @var2; fiddle |
26 июн 20, 08:20 [22157579] Ответить | Цитировать Сообщить модератору |
Все форумы / MySQL | ![]() |