Автор: Юрий Шерман
www.tour-soft.com
| Слияние полей из разных строк запроса. Новая версия. |
| Для полей из разных строк запроса в SQL имеется целый ряд групповых функций: Sum, Min, Max и т.д. Однако если Вам надо объединить поля из разных строк, разделяя их, например, запятой, то такой групповой операции Вы не найдете. Придется писать собственное решение. Постановка задачи. Пусть у Вас имеется Tab1 (таблица или запрос) с полями: ID и Fam. ID - групповой номер, он может повторяться, но не пуст (для упрощения). Fam - поле с фамилией. Требуется создать запрос TabUnion с двумя полями: - ID, значения которого взяты из Tab1, но не повторяются; - FamUnion, которое состоит из списка фамилий через запятую. Список должен состоять из фамилий с одним ID. Рассмотрим решение задачи от самого простого случая до самого общего. Случай 1. Имеются упрощающие предположения. Предположение 1: Tab1 должна быть таблицей, отсортированной по ID. Предположение 2: в Tab1 должно быть не менее двух различных значений ID. Предположение 3: пустых фамилий нет. Текст запроса TabUnion1: Select ID, Last(UnionStr1(ID,Fam)) AS FamUnion FROM Tab1 GROUP BY ID; Описание функции UnionStr1: Public Function UnionStr1(ID, Fam) Static IDOld, FamUnion If IDOld <> ID Then IDOld = ID FamUnion = Null End If FamUnion = (FamUnion + ", ") & Fam UnionStr1 = FamUnion End Function Оценка скорости: время обработки пропорционально C*n, где C – константа, n – количество записей в Tab1. Очевидно, что оценку улучшить нельзя. То есть метод оптимален. Случай 2. Отличается от случая 1 снятием предположения 2. То есть, допускается случай, когда все записи Tab1 имеют один ID. Тогда приходится заниматься установкой начального значения IDOld. Текст запроса TabUnion2: Select ID, Last(UnionStr2(ID,Fam)) AS FamUnion FROM Tab1 WHERE IsEmpty(UnionStr2()) GROUP BY ID; Описание функции UnionStr2: Public Function UnionStr2(Optional ID, Optional Fam) Static IDOld, FamUnion If IsMissing(ID) Then IDOld = Empty Exit Function End If If IDOld <> ID Then IDOld = ID FamUnion = Null End If FamUnion = (FamUnion + ", ") & Fam UnionStr2 = FamUnion End Function Оценка скорости: такая же, как и в случае 1. Замечание: чтобы снять предположение о не пустых фамилиях можно (чтобы избежать лишних запятых) использовать в более тяжеловесную конструкцию. В функциях место строки: FamUnion = (FamUnion + ", ") & Fam следует писать: FamUnion = IIf (IsNull(FamUnion), Fam, FamUnion & (", " + Fam)) Случай 3. Tab1 таблица или запрос. Предположений о сортированности по ID и о пустых фамилиях нет. Предположение: нет повторяющихся пар: ID, Fam. В этом случае необходима промежуточная сортировка Tab1 по ID. Текст промежуточного запроса Tab1Sort для сортировки Tab1: Select DISTINCT ID, Fam FROM Tab1 ORDER BY ID, Fam; Текст запроса TabUnion3: Select ID, Last(UnionStr2(ID,Fam)) AS FamUnion FROM Tab1Sort WHERE IsEmpty(UnionStr2()) GROUP BY ID; Здесь применяется та же функция, что и в случае 2. Оценка скорости: оценка ухудшается из-за применения сортировки. Известно, что сортировку быстрее, чем C*n*log(n) сделать нельзя. Обычно используют сортировку методом Шелла, которая имеет оценку C*n*log(n)^2. Эта же оценка является оценкой быстродействия в данном случае. Быстрее, очевидно, сделать нельзя. Замечание: в промежуточной сортировке используется предикат DISTINCT. Он превращает запрос Tab1Sort в статический. Без этого метод неработоспособен. Но этот же предикат удаляет повторяющиеся пары ID, Fam. Поэтому необходимо указанное выше предположение. Случай 4. Tab1 таблица или запрос. Никаких дополнительных предположений нет. Для применения метода слияния из случая 3 необходимо все строки Tab1 сделать уникальными. Для этого используем нумерацию строк запроса, изложенную в статье «Нумерация строк запроса. Новый подход». Текст промежуточного запроса Tab1Num для нумерации Tab1: Select DISTINCT Numeration(ID) As Num, ID, Fam FROM Tab1 WHERE Numeration()=0; Описание функции Numeration дано в статье, указанной выше. Текст промежуточного запроса Tab1SortNum для сортировки Tab1Num: Select DISTINCT ID, Fam, Num FROM Tab1Num ORDER BY ID, Fam; И, наконец, текст запроса TabUnion4: Select ID, Last(UnionStr2(ID,Fam)) AS FamUnion FROM Tab1SortNum WHERE IsEmpty(UnionStr2()) GROUP BY ID; Здесь применяется та же функция, что и в случае 2. Оценка скорости: оценка скорости та же, что и в случае 3, так как нумерация имеет оценку C*n. Решение поставленной задачи завершено. Другой вариант постановки задачи. Предположения о Tab1 остаются прежними. Теперь требуется заполнить пустую таблицу Tab2, содержащую поля ID и FamUnion значениями, описанными в основной задаче. Метод 1. Очевидный. Формируем запрос TabUnion одним из способов, указанных выше. Затем записываем его результат в Tab2. Метод 2. Заполнение таблицы Tab2 с помощью Recordset. Это можно сделать с помощью следующей функции: Public Function UnionStr3() Dim R As Recordset, W As Recordset Dim IDOld, FamUnion Set R = CurrentDb.OpenRecordset("SELECT * FROM Tab1 ORDER BY ID,Fam") If R.EOF Then Exit Function Set W = CurrentDb.OpenRecordset("Tab2") Do NewID: IDOld = R!ID FamUnion = Null Union: FamUnion = (FamUnion + ", ") & R!Fam R.MoveNext If R.EOF Then GoTo WriteUnion If IDOld <> R!ID Then WriteUnion: W.AddNew W!ID = IDOld W!FamUnion = FamUnion W.Update Else GoTo Union End If If Not R.EOF Then GoTo NewID Exit Do Loop End Function Область применения: в том случае, когда кроме составления списка нужно сделать еще какие-нибудь нестандартные операции. Недостаток: по факту работает значительно медленнее метода 1. Метод 3. Заполнение таблицы при помощи запросов (по Митину). Ссылка: http://c85.cemi.rssi.ru/Access/AnsPointDetail.idc?QID=14147 Оригинальный и неочевидный метод. Выполняется с помощью двух запросов. 1. Запись в Tab2 уникальных ID без фамилий. Текст запроса WriteID: INSERT INTO Tab2 (ID) Select DISTINCT ID FROM Tab1; 2. Запись списков фамилий. Текст запроса TabUnion5: UPDATE Tab2 INNER JOIN Tab1 On Tab2.ID = Tab1.ID Set Tab2.FamUnion = ([Tab2].[FamUnion]+", ") & [Tab1].[Fam]; Оценка скорости: по-видимому, работает быстрее метода 1 за счет отсутствия вызова специальных функций. Недостаток: "Однако в T-SQL это непрокатывает" (Митин). Общий недостаток всех методов: при большом объеме данных все методы заполнения Tab2 работают достаточно долго. Если результат (список фамилий) используется часто, то лучше Tab2 сделать постоянной таблицей в базе общего доступа. Далее корректировать ее при изменении Tab1. Так как одиночная корректировка Tab1 меняет только один список в Tab2, то при этом объем работы будет мал и выполнится быстро. Реализация для базы Access: в форме, в которой можно изменить Tab1 следует на события создания/измения/удаления записи молча проводить синхронные изменения в Tab2. Собственно такую же рекомендацию для MS SQL дает и Митин: "Я в общем то для обеспечения максимальной производительности остановился на решении, когда на триггерах поддерживается перечень упаковок в основной табл. при изменении записей в табл. с упаковками." |
Просмотров: 70965
 Коментарии (10) |
 |
| Добавить коментарий |