В статье приводятся три метода нумерации со следующей характеристикой, указанной автором: «Первые два — работают существенно быстрее последнего, но последний запрос обладает одним очень важным достоинством — данные, полученные при помощи этого запроса, можно модифицировать.» То есть, первые два метода предлагают статические запросы, третий — динамический.
По текстам запросов нетрудно дать общую оценку скорости исполнения запросов: 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
Комментарий 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 Примечание.
Следует отметить, что реальная необходимость применения нумерованного динамического запроса возникает в только формах, предусматривающих корректировку данных. А в этом случае можно применить следующий прием: — форму строим на нумерованном статическом запросе; — корректировку проводим в свободном поле формы; — после корректировки изменяем прямым доступом соответствующее поле в основной таблице; — обновляем запрос в форме.
Примерно таким же образом можно ввести и новую запись. Все это, конечно, осложняет программирование, но резко увеличивает скорость исполнения. Разница в скоростях исполнения отчетливо видна уже на запросах в 1000 записей.
10 Fauzi88 [2013-02-04 11:52:00]
У меня есть таблица в Microsoft Access, и я хочу показать номер строки в столбце, используя запрос выбора в Access, подобно использованию функции ROW_NUMBER() в SQL Server.
В SQL Server я могу использовать этот запрос:
Я использую тот же запрос в доступе, но получаю ошибку.
Обычно необходимость нумерации строк возникает при формировании отчетов. В этом случае нумерацию строк, возвращаемых запросом, обычно реализуют на клиенте. Например, не составляет особого труда перенумеровать строки отчета, подготовленного в MS Access. Однако иногда это необходимо сделать в самом запросе. Этот случай мы сейчас и рассмотрим.
