Вопрос о нумерации строк запроса неоднократно задавался на форуме. Конечным ответом всегда была ссылка: http://am.rusimport.ru/MsAccess/topic.aspx?ID=87, где статья дается в разделе "Содержание. Запросы. Порядковый номер записи" Сама статья имеет ссылку на источник: http://www.dykbits.com
В статье приводятся три метода нумерации со следующей характеристикой, указанной автором: "Первые два - работают существенно быстрее последнего, но последний запрос обладает одним очень важным достоинством - данные, полученные при помощи этого запроса, можно модифицировать." То есть, первые два метода предлагают статические запросы, третий - динамический.
По текстам запросов нетрудно дать общую оценку скорости исполнения запросов: C*n*n, где С - константа, различная для запросов; n - количество строк в исходной таблице (запросе). Требование выходной сортированности только ухудшает эту оценку.
Однако автором пропущен важный недостаток всех методов: значения поля, на основе которых производится нумерация, не должны дублироваться.
Пример. Если Вы имеете исходную таблицу с одним полем и пятью строками с одним значением, то в поле Номер Вы получите везде цифру 5. То есть, задача не решена - нумерация не выполнена.
Более мелкий недостаток: во всех методах используется сравнение значений разных строк поля. Однако, как известно, сравнение можно проводить не всех типов полей.
Новый подход для получения статических запросов.
Предлагается метод нумерации с оценкой скорости C*n, не зависящий от значений и типов исходных данных. Очевидно, что эти характеристики уже улучшить нельзя. То есть метод оптимален.
Постановка задачи 1. Запрос без полей MEMO и OLE.
Пусть имеется таблица (запрос) MyQuery с полем MyField. В запросе могут быть другие поля, допускающие предикат DISTINCT. MyQuery может быть отсортирован любым образом или вообще не отсортирован.
Требуется построить запрос, не меняющий порядка следования записей MyQuery, но добавляющий вычисляемое поле Num, представляющее номер строки MyQuery, начиная с 1.
Решение.
Текст запроса:
SELECT DISTINCT Numeration(MyField) As Num, MyField FROM MyQuery WHERE Numeration() = 0;
В запросе используется функция Numeration. Вот ее описание:
Public Function Numeration(Optional var) As Long Static n As Long If IsMissing(var) Then n = 0 Else n = n + 1 End If Numeration = n End Function
Комментарий 1. Запрос оптимизируется компилятором SQL. Поэтому значение функции в предложении WHERE, не зависящее от поля запроса , вычисляется только один раз. Этот вызов используется для установления начального значения счетчика.
Комментарий 2. Предикат DISTINCT используется для превращения динамического запроса в статический. (В динамическом запросе метод не работает.) Своей прямой роли предикат DISTINCT не играет, так как все строки результирующего запроса уникальны из-за нумерации. Однако он препятствует использованию в запросе полей MEMO и OLE. Эти поля можно преобразовать в тип String, применяя функцию Mid(Field_MEMO_OLE,1). После чего конфликт исчезнет, но поля OLE потеряют свою функциональность. Если есть только поля MEMO, то такой подход применим.
Постановка задачи 2. В запросе допускаются любые поля.
Пусть имеется таблица (запрос) MyQuery с полем MyField. В запросе могут быть любые другие поля. MyQuery может быть отсортирован любым образом или вообще не отсортирован.
Требуется построить запрос, не меняющий порядка следования записей MyQuery, но добавляющий вычисляемое поле Num, представляющее номер строки MyQuery, начиная с 1.
Решение.
Текст запроса:
SELECT Numeration(MyField)/2 As Num, First(MyField) AS MyFieldFirst FROM MyQuery WHERE Numeration() = 0 GROUP BY Numeration(MyField)/2;
Комментарий. Здесь запрос превращается в статический за счет применения группировки. Это снимает все требования с полей, но усложняет запрос и увеличивает время его исполнения. Для полей MEMO и OLE следует применять агрегатную функцию First или Last, для остальных достаточно указания группировки. Реально группировка также не проводится, так как первый уровень группировки (Numeration(MyField)/2)всегда дает одиночные записи.
О нумерованном динамическом запросе.
Улучшить метод получения нумерованного динамического запроса не удалось. Более того, разработать метод, нумерующий запрос со скоростью = C*n, по-видимому, невозможно. Поэтому для полноты привожу метод 3 из упомянутой выше статьи.
SELECT DCount("*", "MyQuery",КритерийСравнения) AS Num, MyField FROM MyQuery ORDER BY MyField;
КритерийСравнения для чисел: "MyField <=" & MyField
КритерийСравнения для текста: "MyField <='" & MyField & "'"
Примечание.
Следует отметить, что реальная необходимость применения нумерованного динамического запроса возникает в только формах, предусматривающих корректировку данных. А в этом случае можно применить следующий прием: - форму строим на нумерованном статическом запросе; - корректировку проводим в свободном поле формы; - после корректировки изменяем прямым доступом соответствующее поле в основной таблице; - обновляем запрос в форме.
Примерно таким же образом можно ввести и новую запись. Все это, конечно, осложняет программирование, но резко увеличивает скорость исполнения. Разница в скоростях исполнения отчетливо видна уже на запросах в 1000 записей.