Добро пожаловать в форум, Guest >> Войти | Регистрация | Поиск | Правила | | В избранное | Подписаться | ||
Все форумы / Microsoft SQL Server |
![]() ![]() |
Davulcu Member Откуда: Сообщений: 21 |
Доброго времени суток, уважаемые знатоки! Занимаюсь запросом, который обрабатывает и возвращает значительное количество записей, что требует его оптимизации. Вот тут то мне и нужна Ваша помощь, Ваши размышления. Суть задачи такова, необходимо вернуть только записи документов 3-го типа, которые через документ-родитель имеют связь с документом 2-го типа. Количество ссылок у документа-родителя на каждый из типов не должно быть больше одной, в противном случае ссылки документа-родителя не учитывать. Т.е. документ-родитель всегда обязан иметь не более одной ссылки на документ типа 2, также не более одной, но не в обязательном порядке ссылку на документ типа 3. Ниже приведен пример табличных данных. Столбец DocRef получен присоединением к таблице документов таблицы ссылок по столбцу DocID. Документы типов, отличных от 2-3 не учитывать. Один документ может участвовать в ссылочной связи как правильным документом-родителем, так и с неправильным, при этом его все равно выводить. На текущий момент я решаю эту проблему следующим образом (запрос обезличен): select distinct sum (case when DC.DocType = 3 then DC.DocID else 0 end) 'ID' from ( select D.DocID 'Parent' , DC.DocID 'Child' , DC.DocType , count (DC.DocID) over (partition by D.DocID, DC.DocType) Cnt -- Считаем ссылки from Docs D left join Refs R on D.DocID = R.DocID join Docs DC on DC.DocID = R.DocRef and DC.DocType in (2,3) where D.DocType = 1 ) X where X.Cnt = 1 -- Убираем документ-родители с количеством ссылок более 1 group by D.DocID having (sum (case when DC.DocType = 2 then DC.DocID else 0 end)) * (sum (case when DC.DocType = 3 then DC.DocID else 0 end)) != 0 -- Отсеиваем документы без ссылки на один из типов Если в моем запросе есть ошибка, то просьба указать на нее. Также буду рад комментариям =) Надеюсь не сильно перегрузил Вас своей задачей, всем заранее очень благодарен за советы или примеры! К сообщению приложен файл. Размер - 147Kb |
20 дек 13, 22:56 [15326249] Ответить | Цитировать Сообщить модератору |
Davulcu Member Откуда: Сообщений: 21 |
Прошу прощения, в первом сообщении допустил ошибки, прикладываю исправленный вариант, со скриптом создания таблиц. Еще раз спасибо!CREATE TABLE [dbo].[Docs] ( [DocType] [int] NOT NULL , [DocID] [int] NOT NULL , [DocName] [char] (50) NOT NULL ) GO CREATE TABLE [dbo].[Refs] ( [DocID] [int] NOT NULL , [DocRef] [int] NOT NULL , ) GO insert into Docs values(1, 1, 'СТП 2013.001') insert into Docs values(1, 2, 'СТП 2013.002') insert into Docs values(1, 3, 'СТП 2013.003') insert into Docs values(1, 4, 'СТП 2013.004') insert into Docs values(1, 5, 'СТП 2013.005') insert into Docs values(1, 7, 'СТП 2013.007') insert into Docs values(2, 16, 'ГОСТ 2.105-95') insert into Docs values(2, 17, 'ГОСТ 2.304-81') insert into Docs values(2, 18, 'ГОСТ 2.307-68') insert into Docs values(2, 19, 'ГОСТ 3.1109-82') insert into Docs values(2, 20, 'ГОСТ 3.1118-82') insert into Docs values(3, 21, 'ГОСТ 7795-70') insert into Docs values(3, 22, 'ГОСТ 7798-70') insert into Docs values(3, 23, 'ГОСТ 7817-80') insert into Docs values(3, 24, 'ГОСТ 15589-70') insert into Docs values(3, 25, 'ГОСТ 7805-70') insert into Docs values(4, 26, 'ГОСТ 5915-70') insert into Refs values(1, 16) insert into Refs values(1, 21) insert into Refs values(2, 16) insert into Refs values(2, 17) insert into Refs values(2, 22) insert into Refs values(3, 18) insert into Refs values(3, 21) insert into Refs values(3, 23) insert into Refs values(4, 19) insert into Refs values(5, 24) insert into Refs values(6, 22) insert into Refs values(6, 25) insert into Refs values(7, 20) insert into Refs values(7, 25) insert into Refs values(7, 26) select distinct sum (case when X.DocType = 3 then X.Child else 0 end) 'ID' from ( select D.DocID 'Parent' , DC.DocID 'Child' , DC.DocType , count (DC.DocID) over (partition by D.DocID, DC.DocType) Cnt -- Считаем ссылки from Docs D left join Refs R on D.DocID = R.DocID join Docs DC on DC.DocID = R.DocRef and DC.DocType in (2,3) where D.DocType = 1 ) X where X.Cnt = 1 -- Убираем документ-родители с количеством ссылок более 1 group by X.Parent having (sum (case when X.DocType = 2 then X.Child else 0 end)) * (sum (case when X.DocType = 3 then X.Child else 0 end)) != 0 -- Отсеиваем документы без ссылки на один из типов |
20 дек 13, 23:46 [15326483] Ответить | Цитировать Сообщить модератору |
Davulcu Member Откуда: Сообщений: 21 |
Исправил изображение: К сообщению приложен файл. Размер - 141Kb |
21 дек 13, 01:25 [15326743] Ответить | Цитировать Сообщить модератору |
Cygapb-007 Member Откуда: Сообщений: 1677 |
Попробуйте такой вариант:create index IX_Refs_DocID_DocRef on Refs(DocID,DocRef); create index IX_Docs_DocType_DocID on Docs(DocType,DocID);Вообще-то тут можно и select d.*, но требовалось только DocID select d.DocID from Docs d join Refs r on r.DocRef=d.DocID where d.DocType=3 and exists( select NULL from Refs f join Docs s on s.DocID=f.DocRef and s.DocType in (2,3) where f.DocID=r.DocID group by f.DocID having sum(case when s.DocType=2 then 1 end)=1 and sum(case when s.DocType=3 then 1 end)=1 )Не по теме: specially для тех, кому CROSS APPLY горло режет, завернул в EXISTS ![]() |
21 дек 13, 01:47 [15326768] Ответить | Цитировать Сообщить модератору |
o-o
Guest |
|
|
21 дек 13, 10:30 [15327011] Ответить | Цитировать Сообщить модератору |
Cygapb-007 Member Откуда: Сообщений: 1677 |
o-o, да я разве против? нравятся вам длинные трудночитаемые формулы в запросах - ну и на здоровье, вам их и поддерживать, в конце концов ![]() |
21 дек 13, 10:56 [15327023] Ответить | Цитировать Сообщить модератору |
Davulcu Member Откуда: Сообщений: 21 |
Cygapb-007, Прирост в скорости есть! Вы кстати уже не одну задачку мне помогли закрыть, спасибо! У кого еще какие варианты будут? |
21 дек 13, 12:00 [15327104] Ответить | Цитировать Сообщить модератору |
o-o
Guest |
|
|
21 дек 13, 12:29 [15327165] Ответить | Цитировать Сообщить модератору |
Cygapb-007 Member Откуда: Сообщений: 1677 |
По поводу GROUP BY - вы считаете, что если писать в select AVG(..), а в конце не писать GROUP BY, то группировки не будет? ![]() |
||
21 дек 13, 12:45 [15327189] Ответить | Цитировать Сообщить модератору |
o-o
Guest |
Cygapb-007,
|
|
21 дек 13, 13:03 [15327223] Ответить | Цитировать Сообщить модератору |
Cygapb-007 Member Откуда: Сообщений: 1677 |
По сути не ответили, но полностью поддерживаю
Тем более что мериться никто не собирался, а просто был предложен вариант, в котором кому-то по горлу задело применения APPLY |
||
21 дек 13, 14:01 [15327350] Ответить | Цитировать Сообщить модератору |
o-o
Guest |
по сути вот тут: 15327642 |
21 дек 13, 15:50 [15327650] Ответить | Цитировать Сообщить модератору |
Все форумы / Microsoft SQL Server | ![]() |