помогите создать SQL запрос

← →
toropoff (2011-10-10 18:16) [0]

есть таблица с полем ID.
нумерую сам при добавлении.
необходимо произвести перенумерацию упорядочив по определенному полю, например naimen.
база Access.
в таблице около полусотни тысяч записей.
путем перебора очень долго получается.
хотелось бы одним запросом перенумеровать поле ID значением номера строки.
что-то типа
UPDATE myTable SET id = номер записи
но как-то ещё учесть упорядочивание по полю naimen

простите что прошу готовое решение, никак не получается

← →
toropoff (2011-10-10 18:22) [1]

крутится что то такое

UPDATE mytable a SET ID =
(SELECT номер записи? FROM mytable b WHERE a.ID=b.ID ORDER by Naimen)

не, бред какой-то..

← →
Ega23 © (2011-10-10 19:35) [2]

id нельзя менять.
Заведи рядом поле, его нумеруй как угодно.

← →
armstrong (2011-10-10 20:13) [3]

простите что вероятно неверно описал проблему.
ID поле не autoincrement а обычное.

> Заведи рядом поле, его нумеруй как угодно

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

тоесть есть: kod, naimen
1 петя
2 вася
3 антон

должно
1 антон
2 вася
3 петя

← →
stas © (2011-10-10 20:23) [4]

Если это нужно сделать единоразово, то сделайте так:
select kod, naimen into New_table from mytable order by Naimen
Потом удалите mytable, создайте в таблице New_table поле типа счетчик назовите ID и переименуйте ее в mytable.

← →
armstrong (2011-10-10 20:37) [5]

да в том то и дело, что это не единоразово..
да и счетчик мне никак не подходит.

а если без учета сортировки, то как будет выглядеть
UPDATE mytable SET ID = физический номер записи ?

← →
Сергей М. © (2011-10-10 21:07) [6]

> физический номер записи

Не кури эту траву.

← →
Омлет © (2011-10-10 21:17) [7]

http://www.trevor.easynet.co.uk/AccFAQ/queries.htm#counter

← →
armstrong (2011-10-10 21:39) [8]

Сергей М. ©,
при select * from mytable порядок записей будет как физически они сохранены. или не так?
ну не в этом дело..

Омлет ©, мне не нужен счетчик.

мне нужно одним махом через UPDATE занести в поле RecNo (Integer) порядковый номер записи.

← →
Омлет © (2011-10-10 21:53) [9]

> armstrong (10.10.11 21:39) [8]
> мне не нужен счетчик.
> мне нужно одним махом через UPDATE занести в поле RecNo (Integer) порядковый номер записи.

Чтобы получить твой порядковый номер, надо сначала построить выборку. Т.е. без временной таблицы не обойтись.

← →
armstrong (2011-10-10 22:08) [10]

да я вот и думаю как-то так:

UPDATE mytable a SET ID =
(SELECT номер записи? FROM mytable b WHERE a.ID=b.ID)

← →
Inovet © (2011-10-10 22:22) [11]

В Инете было несколько вариантов решения, может и на ДМ было.

← →
armstrong (2011-10-10 22:26) [12]

эх.. да гуглил уже..
а самому слабо что-то, тямы не хватает..

← →
stas © (2011-10-10 22:47) [13]

> armstrong (10.10.11 22:08) [10]
> да я вот и думаю как-то так:
>

нет такой функции.
И зачем это вообще нужно?

← →
armstrong (2011-10-10 23:08) [14]

нужно автоматически пронумеровать записи.
автонумерация не подходит.
путем перебора очень долгий процесс..
пытался вынести в отдельный поток, но все равно нужно синхронизировать функцию смены ID значения, один фиг минут 20 проход выходит в тысяч 50 записей.
вот думаю как бы в одну команду через update это сделать.
ну на фокспро в запросе RecNo и есть номер записи..
а тут х.з. даже ..

← →
armstrong (2011-10-10 23:08) [15]

на фоксе было бы replace ID with recno()

← →
stas © (2011-10-10 23:35) [16]

armstrong (10.10.11 23:08) [14]
Это я понял.
А цель перенумерации какая, тем более делать это постоянно ?

← →
Inovet © (2011-10-10 23:53) [17]

Когда наблюдаются странные желания, закрадывается сомнение - а надо ли это на самом деле или надо сделать совсем не так.

Гуглить около темы порядковый номер в SQL запросе.

← →
Ega23 © (2011-10-11 00:00) [18]

> при select * from mytable порядок записей будет как физически
> они сохранены. или не так?

Не так. Гарантированный порядок сортировки будет только в случае Order by, всё остальное не гарантируется.

> мне нужно одним махом через UPDATE занести в поле RecNo
> (Integer) порядковый номер записи.

Что такое "порядковый номер записи"?

Я всегда в order by могу этот порядок поменять. А также потом на клиенте этот порядок поменять. А потом ещё и отфильтровать, как на клиенте, так и на сервере.

Ответь на один простой вопрос: какую проблему ты решаешь?

← →
Ega23 © (2011-10-11 00:01) [19]

> ну на фокспро в запросе RecNo и есть номер записи..

Он меняется, понимаешь? Меняется. Сейчас он один, через 5 минут - другой. А запись - та же самая.

← →
знайка (2011-10-11 00:02) [20]

что-то типа такого
select *, (select count(z.id) from mytable z where z.id <= mytable.id) as rownumber from mytable

← →
Inovet © (2011-10-11 00:07) [21]

> [18] Ega23 © (11.10.11 00:00)
> Я всегда в order by могу этот порядок поменять.

Автор так и хочет сделать

> [0] toropoff (10.10.11 18:16)
> необходимо произвести перенумерацию упорядочив по определенному полю, например naimen.

Только зачем нужна нумерация в базе, ещё и постоянно требуется её обновлять - вот это странное желание.

← →
Ega23 © (2011-10-11 00:08) [22]

> Только зачем нужна нумерация в базе, ещё и постоянно требуется
> её обновлять - вот это странное желание.

Сильно подозреваю, что это опять сага о XYZ

← →
Inovet © (2011-10-11 00:09) [23]

> [20] знайка (11.10.11 00:02)
> select *,
> (select count(z.id) from mytable z where z.id <= mytable.id) as rownumber
> from mytable

Только naimen надо.

← →
armstrong (2011-10-11 00:32) [24]

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

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

не спрашивайте зачем такая хрень нужна.

знайка, спасибо! интересный запрос, пока ничего не понятно..
а как это будет выглядеть с update ?

← →
armstrong (2011-10-11 00:49) [25]

> select *,
> (select count(z.id) from mytable z where z.id <= mytable.
> id) as rownumber
> from mytable

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

← →
Ega23 © (2011-10-11 00:53) [26]

Бред какой-то.
Впрочем, твои проблемы. СУБД какая?

← →
sniknik © (2011-10-11 00:58) [27]

> интересный запрос, пока ничего не понятно..
это спец запрос для внесения "солидности" в программу... там где "путем перебора" ([0]) меньше минуты, тут будет меньше часа... и чем дальше тем больше, в прогрессии.
+ "закладка" на существующие, и возрастающие, значения ID. для произвольной сортировки бесполезен.

← →
armstrong (2011-10-11 01:05) [28]

Ega23 ©, Access, если это можно назвать СУБД.

sniknik ©, а у Вас мысли есть как можно это сделать?

была бы нормальная СУБД через хранимые процедуры накрайняк что-то слепил, может быстрее чуток было бы..
но тут тупым перебором другой раз пол часа уходит.
не думал с утра что так всё сложно окажется :(

← →
sniknik © (2011-10-11 01:20) [29]

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

← →
Германн © (2011-10-11 01:20) [30]

> была бы нормальная СУБД через хранимые процедуры накрайняк
> что-то слепил, может быстрее чуток было бы..

А чем Аксесс "ненормальная" БД?
Если речь только о том, чтобы раз в месяц формировать отчёт?

Все ваши посты упоминают RecNo. Это ваш бзик такой?
У "нормальной" БД нет никакого RecNo!"
Сравните, например, с файловой системой. Какой файл первый, какой последний?

← →
знайка (2011-10-11 01:23) [31]

а печатает тоже Access, или как?

← →
armstrong (2011-10-11 01:27) [32]

sniknik ©, да в том то и дело, что изменение поля при простом переборе занимает до получаса. в таблице около 50т. записей.

tempQuery.first;
while not eof do begin

пробывал через edit post, через update - один фиг до получаса..

next;
end;

Германн ©, RecNo наверное "бзик", я просто хотел показать что нужен номер записи..

← →
armstrong (2011-10-11 01:29) [33]

знайка,
да не.. все на delphi.
вывод в файл формата excel"я

← →
знайка (2011-10-11 01:33) [34]

ну так пусть excel и нумерует, у него это получится куда быстрее

← →
Германн © (2011-10-11 01:34) [35]

> Германн ©, RecNo наверное "бзик", я просто хотел показать
> что нужен номер записи..

А "номер записи" это фикция! Такая же как и RecNo. Более того - это одно и то же!

← →
armstrong (2011-10-11 01:39) [36]

знайка, так оператор в проге ищет запись по номеру..

Германн ©, так в sql RecNo не вставить.. поэтому я просто хотел показать что нужно..

← →
sniknik © (2011-10-11 01:40) [37]

> sniknik ©, да в том то и дело, что изменение поля при простом переборе занимает до получаса. в таблице около 50т. записей.
ключница водку делала... © Иван Васильевич меняет профессию...

проверил у себя на 172 тыс... = 40 сек. вся "перенумеровка", подозреваю на 50 тыс. будет быстрее...

p.s. что вообще за привычки у людей не учится делать, а искать варианты.

← →
sniknik © (2011-10-11 01:44) [38]

> А "номер записи" это фикция! Такая же как и RecNo. Более того - это одно и то же!
ну, порядок записей все одно есть, он просто не гарантированный, без сортировки...

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

← →
Германн © (2011-10-11 02:11) [39]

> ну, порядок записей все одно есть, он просто не гарантированный,
> без сортировки...
>

А автор хочет именно по "порядку записей". Думая что такой порядок есть.

← →
armstrong (2011-10-11 02:29) [40]

да ничего я не думаю..
порядок конечно же есть.
если я в запросе указал oprder by naimen то и порядок будет по этому полю.
мне нужно теперь заполнить поле kod текущим порядковым номером записи.
да бог с ним, я понял что одним запросом не получится.
буду выносить в отдельный поток нумерацию в тупом переборе.
если у sniknik © "172 тыс... = 40 сек." то это вполне устраивает.
сейчас упакую базу и проверю.. хотя на моём i7 будут отличаться результаты от операторского 4-го пня :)
спасибо всем!

← →
Inovet © (2011-10-11 02:35) [41]

> [39] Германн © (11.10.11 02:11)
> А автор хочет именно по "порядку записей". Думая что такой
> порядок есть.

Да по имени он хочет, см выше.

← →
Inovet © (2011-10-11 02:38) [42]

> [40] armstrong (11.10.11 02:29)
> если у sniknik © "172 тыс... = 40 сек." то это вполне устраивает.
> сейчас упакую базу и проверю..

DisableControls
EnableControls
не забудь

← →
armstrong (2011-10-11 02:41) [43]

Inovet ©, да конечно )

на sql.ru :

Q7: Как сымитировать счетчик в обычном запросе либо ленточной форме?

A1:
SELECT (Select Sum(1) From t AS p Where p.f<=p1.f), p1.f
FROM t AS p1
ORDER BY p1.f;

A2:
SELECT DCount("f", "t","f<=" & CStr(f)), f
FROM t
ORDER BY f;

тогда пока не сдаюсь, про DCount не знал... (:

← →
Inovet © (2011-10-11 03:04) [44]

> [43] armstrong (11.10.11 02:41)

Уже сказали, что такие запросы будут медленные.

Делай циклом по датасету. Параметры используй и Prepare перед циклом - будет несколько быстрее.

← →
Servy © (2011-10-11 03:23) [45]

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

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

← →
Германн © (2011-10-11 04:02) [46]

> Inovet © (11.10.11 02:35) [41]
>
> > [39] Германн © (11.10.11 02:11)
> > А автор хочет именно по "порядку записей". Думая что такой
> > порядок есть.
>
> Да по имени он хочет, см выше.
>

Значит опять та самая сага о x,y,z.

← →
Кщд (2011-10-11 06:56) [47]

>armstrong (11.10.11 02:41) [43]
>Q7: Как сымитировать счетчик в обычном запросе либо ленточной форме?
получим глупости при дубликатах в t.f

← →
sniknik © (2011-10-11 07:59) [48]

> DisableControls
> EnableControls
> не забудь
не только. многое можно "забыть" (не читая справки/не пробуя/не понимая принципов) и после "валить" на тормознутость ADO/Access/системы в общем оправдывая себя любимого...

> Не знаю насколько относится к именно акцессу ...
слабо относится... у меня вот получилось 5-ти секундное ускорение (35 сек) при открытии базы монопольно. такая вот особенность. но делать ограничения на "всем выйти из программы, счаз буду цикл делать" ради 5ти сек думаю не стоит.

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

> Значит опять та самая сага о x,y,z.
да пофиг. пусть уже делает своего "монстра".

← →
sniknik © (2011-10-11 08:07) [49]

> он с из точки зрения идеален, а виновата в тормозах система
вот эта вот фраза (и не только)
> сейчас упакую базу и проверю..
вместо "сейчас сделаю пример с кодом и проблемными данными и выложу."
заставляет так думать

← →
Anatoly Podgoretsky © (2011-10-11 08:54) [50]

50 000 записей, это менее 10 секунд, при по рекодном обновление с клиента.

И решение два - уйдите от намерации, замените кодами товара - это вообще будет 0 секунд

← →
Inovet © (2011-10-11 13:37) [51]

> [50] Anatoly Podgoretsky © (11.10.11 08:54)
> И решение два - уйдите от намерации, замените кодами товара
> - это вообще будет 0 секунд

Так надо же проктально сделать - кадый месяц грохать старый прайс и создавать новый с новыми кодами.

← →
armstrong (2011-10-11 14:37) [52]

Inovet ©, через жопу это точно.. но так уже налажена работа...

пока сделал так:

procedure TAutoNumerate.Execute; begin TempQuery:= TADOQuery.create(dm); TempCommand:= TADOCommand.Create(dm); try TempQuery.Connection:= DM.ADOConnection; TempCommand.Connection:= DM.ADOConnection; TempCommand.Parameters.CreateParameter("newkod", ftInteger, pdInput, 10, 0); TempCommand.Parameters.CreateParameter("oldkod", ftInteger, pdInput, 10, 0); TempCommand.Parameters.CreateParameter("mynaimen", ftString, pdInput, 50, ""); TempCommand.CommandText:= "update "+IntToStr(dmu.arrTab[myСurTabSheet].idTable)+" set n = :newkod where (n=:oldkod) and (naimen= :mynaimen)"; TempCommand.ParamCheck:= true; TempCommand.Prepared:= true; TempQuery.sql.Add("select n, naimen from mytable order by naimen"); TempQuery.open; TempQuery.first; while not TempQuery.Eof do begin TempCommand.Parameters.FindParam("newkod").Value:= firstNumb; TempCommand.Parameters.FindParam("mynaimen").Value:= TempQuery.Fields[1].AsString; TempCommand.Parameters.FindParam("oldkod").Value:= TempQuery.Fields[0].AsInteger; TempCommand.Execute; inc(firstNumb); TempQuery.next; end; finally TempQuery.Close; TempQuery.Free; TempCommand.free; PostMessage(form1.Handle, wm_myEvent, Integer(unit1.myEventRefreshGrid), 0); AutoNumerate:= nil; MessageBeep(0); end; end;

проверил 4000 записей - 15 секунд.

я вот не пойму советуют
"делается запрос на создание таблицы с нужной сортировкой, потом в полученную таблицу вставляется автосчетчик запросом alter table xxx add column yyy counter."

"select ... into НоваяТаблица .... order by naimen
И второй запрос, alter table НоваяТаблица add column id counter"

но мои знания SQL слабоваты, никак не могу понять как это предлагается реализовать..

> проверил 4000 записей - 15 секунд.

use SQL, Luke!

← →
armstrong (2011-10-11 14:53) [54]

> [53] Ega23 © (11.10.11 14:44)
> > проверил 4000 записей - 15 секунд.
>
> use SQL, Luke!

Так было 30 минут, теперь быстрее в 1800 / 15 > 100 раз

> [52] armstrong (11.10.11 14:37)
> and (naimen= :mynaimen)

Наименование зачем менять сомо на себя

Ещё это можно заменить
TempCommand.Parameters.FindParam("newkod")
TempCommand.Parameters.FindParam("oldkod")

на индекс - один раз его определить, потом
TempCommand.Parameters[ioldkod].Values
TempCommand.Parameters[inewkod].Values

> [52] armstrong (11.10.11 14:37)

И эта. Где DisablerControls хоть их и нет?

> не могли бы Вы для тупых чуть пояснить?

Мог бы. Я уже выше писал: ID суть неприкосновенная вещь. С каким ID ты товар завёл, ровно с таким же ID он и должен умереть.
Для решения твоей проблемы (ИМХО, крайне надуманной) нужно завести в таблице ещё одно поле, что-нибудь типа PositionNr int not null
И вот его уже и перестраивать.
Как это сделать с помощью SQL для Access я не в курсе, ибо с ним не работал. Для MSSQL - нет проблем. Для Access, ИМХО, тоже, надо только чуток мануал покурить.

> [58] Ega23 © (11.10.11 15:25)
> Для решения твоей проблемы (ИМХО, крайне надуманной) нужно
> завести в таблице ещё одно поле, что-нибудь типа PositionNr
> int not null

Тогда уж и историю этих PositionNr надо не в таблице с товарами держать, а сделать отдельную таблицу навроде год, месяц, ID, PositionNr. Иначе нафиг нужен ID если есть и так уникальный n. И вообще автор судя по
IntToStr(dmu.arrTab[myСurTabSheet].idTable)
каждый месяц создаёт новую таблицу.

Кстати, интересный вопрос :-)
Делаю выборку, допустим, товаров из базы, упорядоченных по алфавиту, и хочу вывести с порядковым номером
Этакий Автоинкремент.
Спасет ли OnCalcFields орд (если такая фигня нужна только для печати)?

> Спасет ли OnCalcFields

В частном случае - да.
В общем - нет.

> [60] Cobalt © (11.10.11 16:04)
> если такая фигня нужна только для печати

Если только для печати - в репортерах обычно есть функция печати порядкового номера.

> [60] Cobalt © (11.10.11 16:04)
> Спасет ли OnCalcFields орд (если такая фигня нужна только для печати)?

Событие срабатывает много от чего, как-то инкремент в нём некошерно делать. Если только открыть и двигаться вниз, то вроде получиться. Но зачем оно так нужно.

Т.е в общем случае придется заняться интимными отношениями с Select"ом и хранимкой?

> [64] Cobalt © (11.10.11 16:56)
> Т.е в общем случае придется заняться интимными отношениями с Select"ом и хранимкой?

Для ручной печати нумеровать во время вывода, а так генераторы отчётов есть для этого.

> Т.е в общем случае придется заняться интимными отношениями
> с Select"ом и хранимкой?

В общем - непонятно. Суть задачи лично мне так до сих пор и неясна.

вот что правильно:
Для ручной печати нумеровать во время вывода, а так генераторы отчётов есть
для этого.

но,
вот такой трюк находил как-то. Чисто из любопытства

select
(select count(s.name)
from mytable s
where s.name < = t.name) as mycount,
t.name
from mytable t
order by t.name

← →
знайка (2011-10-11 17:49) [68]

> вот такой трюк находил как-то.
Это не в [20] ли посту? :)

> Так надо же проктально сделать - кадый месяц грохать старый
> прайс и создавать новый с новыми кодами.

Менять коды, это же сумашедствие.

> Менять коды, это же сумашедствие.
а я сталкивался... причем код как часть баркода, в магазине. учет всегда был в минус...
после переделки кодов по нормальному, и нормальный штучный учет, как же они были счастливы, на учете меня, постороннего человека (я просто систему делал), "ловили" и хвалились "представляете у нас за полгода недостача только ремень и носки!!!!" (что кстати вполне может быть "естественной" убылью... ну реально сперли ;).

> знайка (11.10.11 17:49) [68]

м-да? :) нет :))
на не свои вопросы, чтобы не зашорится, не читаю чужих советов
кроме Мастеров (ну и еще пару человек )

← →
Кщд (2011-10-12 11:06) [72]

>OW © (11.10.11 17:42) [67]
>вот такой трюк находил как-то. Чисто из любопытства
это же путь в ад
опуская проблему с дубликатами имен, делать - теоретически - full table scan для каждой строки результирующего набора только для того, чтобы пронумеровать?

> это же путь в ад

+1

Попробуй рассказать по русски что откуда берётся и куда кладётся.
"Оператор формирует новый прайс" - КАК???
1) Тупо стирает базу товаров и набирает руками заново? Тогда о какой выборке и скорости речь?
2) Оператор создаёт новый прайс на основе некоего справочника товаров? Как происходит процесс и куда помещается результат? Он проставляет некие метки в справочнике, что товар выбран или переносит сведения о товаре в некую новую таблицу? Опять не ясно о какой выборке и скорости речь, если оператор руками всё делает. Главный вопрос - если в любом случае создаётся НОВАЯ (в смысле пустая) таблица "каталог товаров" и заполняется раз в месяц, то КТО МЕШАЕТ один раз заполнить поле ID, сразу после создания таблицы оператором? Создал -> отсортировал -> пронумеровал (способ ИМХО не имеет значения ибо любой будет быстрым по сравнению со временем заполнения таблицы оператором).
Поясни что не так?

помогите создать SQL запрос Найти похожие ветки