| |
 12 Кб. |
|
| | Хорошего утра ВСЕМ
Скажите пожалуйста
Возможно ли получить запрос выводящий всех предков (если они есть) для каждой записи в древовидной структуре данных, а именно имеем таблицу «Departments» с 3-мя полями (DepID, ParentID, Name1) со след.записями
DepID ParentID Name1
1 0 A
2 1 B1
3 1 B2
4 2 C1
5 3 C2
6 3 C3
Нужно найти всех предков (если они есть) для каждой записи, т.е. получить такой запрос
DepID ancestorID
2 1
3 1
4 1
4 2
5 1
5 3
6 1
6 3
Где
DepID=2 имеет одного предка с DepID=1
DepID=4 имеет двух предков с DepID=1, DepID=2
Ну и так далее
С уважением ПАСАТ  | |
| |
| |
| |
|
|
| | А что у вас показывает ParentID? | |
| |
| |
| |
|
|
| | Если правильно понял вопрос, то
ParentID показывает ID записи из таблицы «Departments»
т.е. имеем дерево
A имеет детей В1 и В2
В1 имеет детей С1
В2 имеет детей С1 и С2 | |
| |
| |
| |
|
|
| | сорри 
A имеет детей В1 и В2
В1 имеет детей С1
В2 имеет детей С2 и С3 | |
| |
| |
| |
|
|
| | По моему это таблицу нужно разбить на 2 таблицы и от туда уже вытягивать данные. | |
| |
| |
| |
|
|
| | Я поэтому и спросил
Если есть возможность создать запрос то зачем тогда таблица
Вопрос в том как его создать??? 
и (или)
возможно ли его создать ???  | |
| |
| |
|
|
| |
|
|
| | С возвращением OSMOR
Что Вы думаете:
возможно ли создать такой запрос ??? | |
| |
| |
| |
|
|
| | спасибо.
надо внимательно вопрос почитать... разгребу работу - посмотрю | |
| |
| |
| |
|
|
| | решение в лоб:
SELECT DepID, ParentID as ancestorID
FROM Departments
WHERE (((Departments.ParentID)<>0))
Union ALL
SELECT D.DepID, D1.ParentId as ancestorID
FROM Departments as D , Departments AS D1
WHERE D.ParentId = D1.DepID and D1.ParentID<>0
Union ALL
SELECT D.DepID, D2.ParentId as ancestorID
FROM Departments as D , Departments AS D1,Departments as D2
WHERE D.ParentId = D1.DepID and D1.ParentID = D2.DepID and D2.ParentID<>0
Order by DepId, ancestorID DESC
|
Union должно быть столько сколько возможно уровней -1 , для данного набора достаточно было бы 1 Union т.е.
SELECT DepID, ParentID as ancestorID
FROM Departments
WHERE (((Departments.ParentID)<>0))
Union ALL
SELECT D.DepID, D1.ParentId as ancestorID
FROM Departments as D , Departments AS D1
WHERE D.ParentId = D1.DepID and D1.ParentID<>0
Order by DepId, ancestorID DESC
|
| |
| |
| |
| |
|
|
| | СУПЕР – большое спасибо
Если я все правильно понял - это подойдет если используется определенное кол-во уровней не больше того которого мы напишем в запросе (половина задачи решена – можно сделать 5, 10, 20 … union all по аналогии).
А что делать если у нас будет неограниченное (бесконечное) количество уровней вложенности ???
Ведь иерархическая (древовидная) структура данных это предполагает | |
| |
| |
| |
|
|
| | ПОскольку каждый следующий select в union имеет похожую структуру то можно такой запрос генерить динамически в зависимости от глубины дерева.
можно попытаться решать задачу с помощью функции... правда не уверен что будет быстрее.
В результате нужно получить именно запрос или можно рекордсет?
Можно добавить поле FullPath ,в которое через разделитель записывать все коды от корня до данной ветки (очень иногда полезно) особенно когда при выборе узла в дереве нужно показать все записи которые лежат ниже узла (т.е. все дочерние) я обычно так и делал. | |
| |
| |
| |
|
|
| | Если мы имеем основную таблицу «Departments» с 3-мя полями (DepID, ParentID, Name1) - в ней хранится иерархическая структура данных, где Поле ParentID является ссылкой на Id (первичный ключ) вышестоящего уровня в иерархии - со след.записями
DepID ParentID Name1
1 0 A
2 1 B1
3 1 B2
4 2 C1
5 3 C2
6 3 C3
и вспомогательную таблицу ANCESTORS содержащую всего два поля. В одном из них храним Id элемента, а в другом - Id всех его предков (Поле ancestorID ссылается на Id предка каждого элемента. В данном случае оно позволяет узнать все подразделения, в которые входит данный элемент)
DepID ancestorID
2 1
3 1
4 1
4 2
5 1
5 3
6 1
6 3
То подобная схема легко позволяет получить любую информацию об иерархических элементах одним запросом
1) можно получить всех предков, либо потомков определенного элемента (нет необходимости добавлятть поле FullPath ,в которое через разделитель записывать все коды от корня до данной ветки)
2) можно узнать, на каком уровне иерархии находится элемент (получив в запросе количество его предков)
3) можно узнать, отсутствуют или имеются другие элементы, входящие в конкретный элемент
Пользуясь двумя такими таблицами, можно легко строить практически любые запросы, характерные для иерархических объектов
Вот я и озадачился тем чтобы иметь только одну таблицу «Departments» а вместо вспомогательной таблицы ANCESTORS использовать запрос (вроде все данные в таблице «Departments» для этого есть).
На данный момент я вижу след. варианты
1) пытаться все-таки получить нужный запрос (если это реально) – это было бы самое лучшее
2) иметь таблицу в которой при каждом к ней обращении програмно создавать актуальный рекордсет, на основе данных таблицы «Departments».
Что Вы посоветуете в данном случае делать ???
Или рациональнее все-таки иметь постоянную вспомогательную таблицу ???
И последниий вопрос
Является ли вообще обтимальным выбранное решение, а именно использывание двух таблиц «Departments» и «ANCESTORS» для работы с иерархической структурой данных. Если есть что-либо более простое, тогда все выше описанное просто не нужно
С уважением ПАСАТ | |
| |
| |
| |
|
|
| | если бы знать что является оптимальным... все зависит от задач, во многих случаях нужно только дерево без всяких "всех родителей и всех потомков"
в свете вышесказанного таблица ANCESTORS мне кажется наиболее привлекательным решением, только пока не понял как поддерживать ее актуальность при перетаскивании веток... | |
| |
| |
| |
|
|
| |
как поддерживать ее актуальность при перетаскивании веток
|
Вроде бы пока перетаскивать ветки не требуется | |
| |
| |
| |
|
|
| | как вариант, но не очень красивый (плюс надо смотреть как будет себя вести при большом количестве веток)
создать функцию
Public Function GetParent(Id As Double) As String
Dim prom As Double
On Error GoTo ErrDebug
prom = Nz(DLookup("ParentID", "Departments", "DepID =" & Id), 0)
If prom > 0 Then
GetParent = "|" & prom & GetParent(prom)
End If
ExitHere:
Exit Function
ErrDebug:
Resume ExitHere
End Function
|
тогда запрос будет иметь вид
SELECT q.DepID, w.DepID
FROM Departments AS q, Departments AS w
WHERE (((InStr("|" & [q].[ParentID] & GetParent([q].[parentid]) & "|","|" & [w].[depid] & "|"))>0))
ORDER BY q.DepID, w.DepID DESC
|
| |
| |
| |
|
|
| |
|
|
| | некрасивый - потому что постоянно пользуется dlookup для каждого узла, причем может неоднократно
про то и говорил, что на больших объемах будет притормаживать
суть метода: рекурчивная функция, в результате выдает для каждого узла список их родителей разделенный знаком |
ну а дальше нечеткое связывание таблицы с самой собой, по вхождению узла в список родителей | |
| |
| |
| |
|
|
| | Как Вы думаете возможно ли придумать что-либо более быстрое ???
Если да, то где рыть ???
зы быстрейшего выздоровления | |
| |
| |
| |
|
|
| | может быть стоит попробывать бегать по открытому рекордсету поиском и результаты укладывать в массив, который выложить потом к Excell (либо сразу результаты в Excell выводить) | |
| |
| |
| |
70 Кб. |
|
| | +1
так и делал, т.к. получилось быстрее (чем запросами)
*
когда-то были попытки вывода участков городской теплотрассы в виде дерева
намудохался... | |
| |
| |
| |
|
|
| | Делали так ???
бегать по открытому рекордсету поиском и результаты укладывать в массив, который выложить потом к Excell (либо сразу результаты в Excell выводить)
|
Мне кажется Excell здесь не нужен ??? | |
| |
| |
| |
|
|
| | намудохался.. что за выражение........
Видимо Вы прсто Зае...сь с решением проблеммы  . | |
| |
| |
|
|
| |
|
|
| | "osmor здесь как-то выкладывал для тестирования dll-ку, а исходников не дал "
есть такое дело, я ее доделал
http://hiprog.com/index.php?option=com_content&task=view&id=251661568&Itemid=35
исходников пока не дам...
Ну там все просто, там нет запросов, бегу по дереву, читаю ноды | |
| |
| |
| |
|
|
| | ужас!! | |
| |
| |
| |
|
|
| | В свое время была похожая задачка. Таблица административных единиц KOATUU (область/район/сельсовет/населеный пункт) отображалась через TreeViev.
При выборе любой админединицы необходимо было отобразить информацию по всех входящив в нее "детях".
Решил следующим образом:
1. Таблицу дополнил полем Cheked (Boolean) для меток выделенных записей, которое в начале равно Fаlse.
2. При выделении узла поле Cheked этой записи получает значение True.
3. Дальше выполнялся через .Execute запрос на обновление, который менял Cheked на True для всех записей, являющихся дочерними (в Вашем случае - родительскими) относительно записей, в которых Cheked = True.
4. Execute повторно выполняется до того времени, пока количество обновленных записей (.RecordsAffected) > 0 .
5. В результате быстро, качественно и недорого  в таблице все необходимые Вам записи имеют Cheked = True.
strSQL1 = "UPDATE Koatuu SET Koatuu.Checked = True " _
& "WHERE ((Koatuu.Key) = '" & CurKey & "');"
strSQL2 = "UPDATE Koatuu SET Koatuu.Checked = True " _
& "WHERE (((Koatuu.Checked)=False) AND ((Koatuu.Parent) " _
& " In (SELECT Koatuu.Key FROM Koatuu WHERE (((Koatuu.Checked)=True)))));"
Set qdf = dbs.QueryDefs("Temp")
qdf.SQL = strSQL1
qdf.Execute
qdf.SQL = strSQL2
Do
DoCmd.SetWarnings False
qdf.Execute
DoCmd.SetWarnings True
Loop While qdf.RecordsAffected > 0
|
| |
| |
| |
|
|
| |
|
|
| | Навскидку - очень не советую использовать DLookup.
Для единичного выполнения еще терпимо, а в запросах к большим таблицам (или рекурсия, как в Вашем случае) может сильно тормозить..
Попробуйте функцию построить на рекордсете без рекурсии.
Внутри фугкции цыклично ищете по рекордсету, пока находите, при этом собирая необходимую строку | |
| |
| |
| |
|
|
| |
спасибо что не бросаете
Я примерно так и мыслю
Как собрать строку вроде бы понятно.
Ну например соберется такая строка
anyStr = "/2;1/3;1/4;1;2/5;1;3/6;1;3/"
|
где
"2;1" это узел с ид = 2 и его предком с ид = 1
"4;1;2" это узел с ид = 4 и его предками с ид = 1 и ид = 2
каждый узел и его предки отделены "/"
Вот только что с ней делать дальше...
Не догоняю как из нее получить запрос
или такая строка
где
"6;1;3" это узел с ид = 6 и его предки с ид = 1 и ид = 3
как эту строку вывести в запросе чтобы было так
6 1
6 3 | |
| |
| |
| |
|
|
| | "SELECT ... FROM... WHERE(f1 in(" & anyStr & ")) or (f2 in(" & anyStr & "))"
не пойдет?
зы. только в качестве разделителя не точка с запятой, а запятая нужна (вроде) | |
| |
| |
| |
|
|
| | Поставте приблизительно такую функцию в запрос
Function strAllParenstID(ThisID As Long) As String
...
strAllParenstID = ""
Set rst = dbs.OpenRecordset("Select DepID, ParentID, Name1, from . . . . ")
StartFind:
rst.FindFirst "DepID = " & ThisID
If rst.NoMatch = False Then
ThisID = rst!ParentID
strAllParenstID = strAllParenstID & "." & ThisID
GoTo StartFind
End If
rst.Close
...
End Function
|
Аналогичным образом через похожую функцию в запросе можете заполнять временную таблицу,
если необходимо всех предков выводить отдельными записями.
Либо я чого-то недопонял... | |
| |
| |
| |
24 Кб. |
|
| | Спасибо VIK
Рассказываю
Создал ф-цию
Function strAllParenstID(ThisID As Long) As String
Dim dbs As Database
Dim rst As Recordset
strAllParenstID = ""
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Departments", dbOpenDynaset)
StartFind:
rst.FindFirst "DepID = " & ThisID
If rst.NoMatch = False Then
ThisID = rst!ParentID
strAllParenstID = strAllParenstID & "." & ThisID
GoTo StartFind
End If
rst.Close
End Function
|
Создал запрос
SELECT q.DepID, w.DepID
FROM Departments AS q, Departments AS w
WHERE (((InStr("." & [q].[ParentID] & strAllParenstID([q].[parentid]) & ".","." & [w].[depid] & "."))>0))
ORDER BY q.DepID, w.DepID DESC;
|
В результате выплняется медленнее чем при использывании Dlookup
А именно нужный запрос (673 записи) на осн.таблица из 100 записей отрывается 42 сек., а при использывании Dlookup – 33 сек. (см.аттач.)
Вообще то обе ф-ии не тормозят, т.е быстро формируют строку состоящую из «ид» предков
Тормозит выше указанный запрс
Поэтому вопрос в следующем
Как построить более быстрый запрос
Зы KrukVN – пока не успел разобраться с тем что Вы написали | |
| |
| |
| |
|
|
| | напишите хотяб так:
Function strAllParenstID(ThisID As Long) As String
Dim rst As DAO.Recordset
Set rst = Workspaces(0).Databases(0).OpenRecordset("Departments", dbOpenSnapshot)
StartFind:
rst.FindFirst "DepID = " & ThisID
If rst.NoMatch = False Then
ThisID = rst!ParentID
strAllParenstID = strAllParenstID & "." & ThisID
GoTo StartFind
End If
rst.Close
Set rst = Nothing
End Function
|
и скорость Вас приятно удивит 
***
но все равно медленно как-то... на 100 записях такая непозволительная тормозня... | |
| |
| |
|
|
| |
|
|
| | Думаю, основная проблема по скорости - обработка строк в условиях запроса и
двойное открывание одной и той-же таблицы.
Еще один вариант:
Function HaveThisParenstID(ThisID As Long, ThisParent as Long) As Boolean
Dim rst As DAO.Recordset
HaveThisParenstID = False
Set rst = Workspaces(0).Databases(0).OpenRecordset("Departments", dbOpenSnapshot)
StartFind:
rst.FindFirst "DepID = " & ThisID
If rst.NoMatch = False Then
ThisID = rst!ParentID
If ThisID = ThisParent Then
HaveThisParenstID = True
GoTo EndFunction
EndIf
GoTo StartFind
End If
EndFunction:
rst.Close
Set rst = Nothing
End Function
|
Еще - сделайте Parent-поле индексированным.
Возможны ошибки, писал в броузере, но суть должна быть понятна.
После запросом выбираете все записи, для коих результат этой функции = True
Там еще раньше было предложение заполнять через функцию временную таблицу. | |
| |
| |
| |
|
|
| | еще раз СПАСИБО что не сдаетесь
Поставил ф-ию в запрос так
SELECT Departments.DepID, Departments.ParentID, HaveThisParenstID([DepID],[ParentID]) AS FunctTest FROM Departments
|
результат этой функции для всех записей = True
"После запросом выбираете все записи, для коих результат этой функции = True"
соответственно и выбирать нечего...
или
надо было не так ???
Если честно, то не совсем понял где изюм
Для какой записи должно быть True, а для какой False ???
"Там еще раньше было предложение заполнять через функцию временную таблицу."
Про это пока не думал...
Я все-таки сначала хочу попробовать получить норм.работающий запрос (если конечно получиться)
С уважением ПАСАТ | |
| |
| |
| |
|
|
| | Pasat, уточните задачу.
1. Нужно вывести отдельными записями всех "родителей" (до самого старшего/древнего) конкретного "ребенка" (одного). Поскольку имеет место одна ветка, запрос отрабатывается через функцию, подобную последней из предложенных мною.
2. Необходимо проверить, является ли этот "родитель" законным  родителем конкретного "ребенка". (см. последнюю функцию)
3. Нужно получить отдельными записями всех "родителей" для всех "детей" , при этом, если родитель имет нескольких "детей", в том числе в разных поколениях, должны быть выведены все записи по паре предок/потомок (или только для потомков последнего поколения и всех их предков?). Поскольку заранее неизвестно максимальное количество поколений и количество записей в результирующим наборе будет превышать исходный, на мою точку зрения, без временной таблицы, заполняемой функцией, здесь не обойтись.
4. Еще что-то.
Подный роддом... | |
| |
| |
| |
|
|
| | Доброе утро
Задача такая
Нужно получить отдельными записями всех "родителей" для всех "детей"
В принципе задачу мы уже решили:
т.е. ф-ей (GetParent - автор ГлазастыйМышь или strAllParenstID - автор Vik) получили строку в которой через разделитель выводятся все предки для каждого узла.
Потом запросом
SELECT q.DepID, w.DepID
FROM Departments AS q, Departments AS w
WHERE (((InStr("|" & [q].[ParentID] & GetParent([q].[parentid]) & "|","|" & [w].[depid] & "|"))>0))
ORDER BY q.DepID, w.DepID DESC
|
Получаем нужный набор записей (очень медленно  )
Нужно быстрее (нужен более быстрый запрос)
ЗЫ аттач уже есть в этой ветке
автор: Pasat (21.08.2008 в 08:13) | |
| |
| |
| |
|
|
| | Уточните, что Вы имеете ввиду под "детьми":
1. Записи, которые не имеют детей
2. Все записи, имеющие родителей.
Генеалогия, однако  | |
| |
| |
| |
|
|
| | Посмотрите самое первое сообщение по этому вопросу | |
| |
| |
| |
|
|
| | ОК, сейчас будет еще один вариант. | |
| |
| |
| |
28 Кб. |
|
| | Реализовано через временную таблицу
Перед запуском не забывайте ее очищать.
Запрос также возвращает кол-во предков.
Function InsetrParentsInTMP(ThisID As Long) As Long
Dim rst As DAO.Recordset
Dim strSQL_Insert As String
Dim CurID As Long
InsetrParentsInTMP = 0
CurID = ThisID
Set rst = CurrentDb.OpenRecordset("qw_ForTMP")
StartFind:
rst.FindFirst "DepID = " & CurID
If rst.NoMatch = False Then
CurID = rst!ParentID
strSQL_Insert = "INSERT INTO tblTMP ( DepIDTMP, ParentIDTMP ) VALUES ( " & ThisID & ", " & CurID & ");"
DoCmd.SetWarnings False
CurrentDb.Execute strSQL_Insert
DoCmd.SetWarnings True
InsetrParentsInTMP = InsetrParentsInTMP + 1
GoTo StartFind
End If
rst.Close
Set rst = Nothing
End Function
|
Смотрите присоединенній файл.
Возможно, нету необходимости в SetWarnings/
Записи с "нулевым" предком исключаються так
If rst.NoMatch = False AND rst!ParentID<>0 Then
|
На всякий случай проверьте корректность работы | |
| |
| |
| |
|
|
| | 1. Мысль вроде понял
Бежим по дереву и найденные значения укладываем во временную таблицу
2.Значит Вы все-таки считаете, что с запросом мучиться не стоит, а лучше использывать временную таблицу ???
3.
На всякий случай проверьте корректность работы
|
Немного потестировал и обнаружил, что некоторые записи дублируются
Попробую разобраться до понедельника
Результат обязательно напишу
Хороших выходных
...жаль что пиво не настоящее | |
| |
| |
| |
|
|
| | Это уже нормально работает, только без запроса
Function InsetrParentsInTMP_2() As Long
Dim rst1 As DAO.Recordset
Dim rst2 As DAO.Recordset
Dim strSQL As String
Dim CurID As Long
InsetrParentsInTMP_2 = 0
strSQL = "DELETE tblTMP.* FROM tblTMP;"
CurrentDb.Execute strSQL
Set rst1 = CurrentDb.OpenRecordset("qw_ForTMP")
Set rst2 = CurrentDb.OpenRecordset("qw_ForTMP")
rst1.MoveLast
rst1.MoveFirst
Do While Not rst1.EOF
CurID = rst1!DepID
StartFind:
rst2.FindFirst "DepID = " & CurID
If rst2.NoMatch = False Then
CurID = rst2!ParentID
strSQL = "INSERT INTO tblTMP ( DepIDTMP, ParentIDTMP ) VALUES ( " & rst1!DepID & ", " & CurID & ");"
CurrentDb.Execute strSQL
InsetrParentsInTMP_2 = InsetrParentsInTMP_2 + 1
GoTo StartFind
End If
rst1.MoveNext
Loop
rst1.Close
Set rst1 = Nothing
rst2.Close
Set rst2 = Nothing
End Function
|
Ухожу в отпуск, поэтому в ближайшие 2 недели вряд-ли буду часто на форуме. | |
| |
| |
| |
|
|
| | Ура
Спешу сообщить - работает отменно (3-4 сек.) 
Наверное большую скорость получить невозможно ???
ОГРОМНОЕ СПАСИБО
и
отличного отпуска
| |
| |
| |
|
|
| |
|
|
| | PS Думаю, что резервы для улучшения существуют, но вряд-ли для Вас критична эта секунда либо ее доли...
Хотя как там в фильме "17 мгновений весны" - не думай о секундах свысока. | |
| |
| |
