Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Помогите составить запрос с исключениями  [new]
Евгений.
Member

Откуда:
Сообщений: 46
Добрый день.

Помогите составить запрос, если это возможно.

Есть таблица (справочник с шаблонами номеров):

provider | template
----------------------
p1 | 8
----------------------
p1 | 823
----------------------
p2 | 82
----------------------
p2 | 8234


Есть рабочая таблица (номера телефонов):

numb
--------
821111
822222
823333
823444

Необходимо составить запрос, который вернет из таблицы numb все телефоны, соответствующие оператору p1, но не подпадающие под номера оператора p2.
Заранее спасибо.
30 окт 18, 11:10    [21718700]     Ответить | Цитировать Сообщить модератору
 Re: Помогите составить запрос с исключениями  [new]
Евгений.
Member

Откуда:
Сообщений: 46
уточнение: понятно что это упрощенный вариант, а на самом деле шаблонов и провайдеров неограниченно много.
30 окт 18, 11:13    [21718706]     Ответить | Цитировать Сообщить модератору
 Re: Помогите составить запрос с исключениями  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20206
Попробуйте начать с простейшего EXISTS/NOT EXISTS - для каждого телефона искать подходящие или не подходящие шаблоны...
30 окт 18, 11:15    [21718708]     Ответить | Цитировать Сообщить модератору
 Re: Помогите составить запрос с исключениями  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
что-то типа
provider  = 'p1' AND numb LIKE template  + '%' AND NOT EXISTS (SELECT 1 FROM t WHERE provider <> 'p1' AND LIKE template + '%' )
30 окт 18, 11:18    [21718711]     Ответить | Цитировать Сообщить модератору
 Re: Помогите составить запрос с исключениями  [new]
Евгений.
Member

Откуда:
Сообщений: 46
TaPaK
что-то типа
provider  = 'p1' AND numb LIKE template  + '%' AND NOT EXISTS (SELECT 1 FROM t WHERE provider <> 'p1' AND LIKE template + '%' )

Таблица с провайдерами может меняться, поэтому не подходит использование конкретных провайдеров в запросе, иначе каждый раз надо будет переделывать запрос под текущие условия.
30 окт 18, 11:21    [21718717]     Ответить | Цитировать Сообщить модератору
 Re: Помогите составить запрос с исключениями  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
Евгений.
TaPaK
что-то типа
provider  = 'p1' AND numb LIKE template  + '%' AND NOT EXISTS (SELECT 1 FROM t WHERE provider <> 'p1' AND LIKE template + '%' )

Таблица с провайдерами может меняться, поэтому не подходит использование конкретных провайдеров в запросе, иначе каждый раз надо будет переделывать запрос под текущие условия.

ну так и поменяйте под то что вам надо, моя сова натянутая на шар предсказаний очень страдает
30 окт 18, 11:24    [21718723]     Ответить | Цитировать Сообщить модератору
 Re: Помогите составить запрос с исключениями  [new]
court
Member

Откуда:
Сообщений: 1956
TaPaK
что-то типа
provider  = 'p1' AND numb LIKE template  + '%' AND NOT EXISTS (SELECT 1 FROM t WHERE provider <> 'p1' AND LIKE template + '%' )
записи "размножаться" в "Есть рабочая таблица (номера телефонов)" со всеми этими ТСовскими
p1 | 8
----------------------
p1 | 823



select ...
from [Есть рабочая таблица (номера телефонов)] a
left join [Есть таблица (справочник с шаблонами номеров)] b on a.numb like b.template  + '%' and provider <> 'p1'
where b.provider is null
and exists(SELECT 1 FROM [Есть таблица (справочник с шаблонами номеров)] b1 WHERE b1.provider = 'p1' AND a.numb LIKE b1.template + '%' )
30 окт 18, 11:27    [21718729]     Ответить | Цитировать Сообщить модератору
 Re: Помогите составить запрос с исключениями  [new]
Евгений.
Member

Откуда:
Сообщений: 46
TaPaK
ну так и поменяйте под то что вам надо, моя сова натянутая на шар предсказаний очень страдает

Не знаю что там с совой, но менять запрос каждый раз как изменяются данные в таблице мне видится не разумным.
30 окт 18, 11:27    [21718732]     Ответить | Цитировать Сообщить модератору
 Re: Помогите составить запрос с исключениями  [new]
court
Member

Откуда:
Сообщений: 1956
Евгений.
Таблица с провайдерами может меняться, поэтому не подходит использование конкретных провайдеров в запросе, иначе каждый раз надо будет переделывать запрос под текущие условия.
инлайн функция с параметром @provider
30 окт 18, 11:32    [21718739]     Ответить | Цитировать Сообщить модератору
 Re: Помогите составить запрос с исключениями  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
Евгений.
TaPaK
ну так и поменяйте под то что вам надо, моя сова натянутая на шар предсказаний очень страдает

Не знаю что там с совой, но менять запрос каждый раз как изменяются данные в таблице мне видится не разумным.

как же печально нынче в сообществе sql...

если надо определить оператора для всех, уберите "provider = 'p1' " и добавьте t.provider <> a.provider
30 окт 18, 11:32    [21718740]     Ответить | Цитировать Сообщить модератору
 Re: Помогите составить запрос с исключениями  [new]
Евгений.
Member

Откуда:
Сообщений: 46
TaPaK
Евгений.
пропущено...

Таблица с провайдерами может меняться, поэтому не подходит использование конкретных провайдеров в запросе, иначе каждый раз надо будет переделывать запрос под текущие условия.

ну так и поменяйте под то что вам надо, моя сова натянутая на шар предсказаний очень страдает

Согласен. Был не прав.
Посмотрю ваш вариант.
30 окт 18, 11:32    [21718741]     Ответить | Цитировать Сообщить модератору
 Re: Помогите составить запрос с исключениями  [new]
iap
Member

Откуда: Москва
Сообщений: 46953
Евгений.
TaPaK
ну так и поменяйте под то что вам надо, моя сова натянутая на шар предсказаний очень страдает

Не знаю что там с совой, но менять запрос каждый раз как изменяются данные в таблице мне видится не разумным.
Вот это кто написал?
Евгений
Необходимо составить запрос, который вернет из таблицы numb все телефоны, соответствующие оператору p1, но не подпадающие под номера оператора p2.
Вам так и сделали.
30 окт 18, 11:33    [21718742]     Ответить | Цитировать Сообщить модератору
 Re: Помогите составить запрос с исключениями  [new]
Евгений.
Member

Откуда:
Сообщений: 46
TaPaK
Евгений.
пропущено...

Не знаю что там с совой, но менять запрос каждый раз как изменяются данные в таблице мне видится не разумным.

как же печально нынче в сообществе sql...

если надо определить оператора для всех, уберите "provider = 'p1' " и добавьте t.provider <> a.provider

Не стоит делать выводы о сообществе.
Я всего лишь зашел на форум попросить помощи.
Да и уже признал что был не прав.
30 окт 18, 11:34    [21718745]     Ответить | Цитировать Сообщить модератору
 Re: Помогите составить запрос с исключениями  [new]
Евгений.
Member

Откуда:
Сообщений: 46
iap
Евгений.
пропущено...

Не знаю что там с совой, но менять запрос каждый раз как изменяются данные в таблице мне видится не разумным.
Вот это кто написал?
Евгений
Необходимо составить запрос, который вернет из таблицы numb все телефоны, соответствующие оператору p1, но не подпадающие под номера оператора p2.
Вам так и сделали.

Да да. Все правильно.
30 окт 18, 11:37    [21718750]     Ответить | Цитировать Сообщить модератору
 Re: Помогите составить запрос с исключениями  [new]
Евгений.
Member

Откуда:
Сообщений: 46
court
TaPaK
что-то типа
provider  = 'p1' AND numb LIKE template  + '%' AND NOT EXISTS (SELECT 1 FROM t WHERE provider <> 'p1' AND LIKE template + '%' )
записи "размножаться" в "Есть рабочая таблица (номера телефонов)" со всеми этими ТСовскими
p1 | 8
----------------------
p1 | 823



select ...
from [Есть рабочая таблица (номера телефонов)] a
left join [Есть таблица (справочник с шаблонами номеров)] b on a.numb like b.template  + '%' and provider <> 'p1'
where b.provider is null
and exists(SELECT 1 FROM [Есть таблица (справочник с шаблонами номеров)] b1 WHERE b1.provider = 'p1' AND a.numb LIKE b1.template + '%' )

Спасибо. Похоже на правду.
30 окт 18, 11:38    [21718752]     Ответить | Цитировать Сообщить модератору
 Re: Помогите составить запрос с исключениями  [new]
Евгений.
Member

Откуда:
Сообщений: 46
court
TaPaK
что-то типа
provider  = 'p1' AND numb LIKE template  + '%' AND NOT EXISTS (SELECT 1 FROM t WHERE provider <> 'p1' AND LIKE template + '%' )
записи "размножаться" в "Есть рабочая таблица (номера телефонов)" со всеми этими ТСовскими
p1 | 8
----------------------
p1 | 823



select ...
from [Есть рабочая таблица (номера телефонов)] a
left join [Есть таблица (справочник с шаблонами номеров)] b on a.numb like b.template  + '%' and provider <> 'p1'
where b.provider is null
and exists(SELECT 1 FROM [Есть таблица (справочник с шаблонами номеров)] b1 WHERE b1.provider = 'p1' AND a.numb LIKE b1.template + '%' )


Обнаружилась проблема в этом запросе :(
Если есть более широкая маска, то она не дает отобрать строки, соответствующие и более узкой маске.
Т.е. если в запрос подставить провайдера p2, то вывод будет пустой, т.к. все отсекается соответствием маске 8 провайдера p1
30 окт 18, 13:11    [21718894]     Ответить | Цитировать Сообщить модератору
 Re: Помогите составить запрос с исключениями  [new]
court
Member

Откуда:
Сообщений: 1956
Евгений.
Т.е. если в запрос подставить провайдера p2, то вывод будет пустой, т.к. все отсекается соответствием маске 8 провайдера p1
ну, дык, что не так ?
"всё по ТЗ" (с) :)
30 окт 18, 13:58    [21718955]     Ответить | Цитировать Сообщить модератору
 Re: Помогите составить запрос с исключениями  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
Евгений.
court
пропущено...
записи "размножаться" в "Есть рабочая таблица (номера телефонов)" со всеми этими ТСовскими
p1 | 8
----------------------
p1 | 823



select ...
from [Есть рабочая таблица (номера телефонов)] a
left join [Есть таблица (справочник с шаблонами номеров)] b on a.numb like b.template  + '%' and provider <> 'p1'
where b.provider is null
and exists(SELECT 1 FROM [Есть таблица (справочник с шаблонами номеров)] b1 WHERE b1.provider = 'p1' AND a.numb LIKE b1.template + '%' )


Обнаружилась проблема в этом запросе :(
Если есть более широкая маска, то она не дает отобрать строки, соответствующие и более узкой маске.
Т.е. если в запрос подставить провайдера p2, то вывод будет пустой, т.к. все отсекается соответствием маске 8 провайдера p1

звучит так, что необходим приоритет масок
30 окт 18, 14:00    [21718957]     Ответить | Цитировать Сообщить модератору
 Re: Помогите составить запрос с исключениями  [new]
Евгений.
Member

Откуда:
Сообщений: 46
court
Евгений.
Т.е. если в запрос подставить провайдера p2, то вывод будет пустой, т.к. все отсекается соответствием маске 8 провайдера p1
ну, дык, что не так ?
"всё по ТЗ" (с) :)

Допускаю что мой косяк, не все описал - думал что это логично, что более узкая маска имеет больший приоритет, т.е. если номер соответствует маске 8 и 82, то считается по маске 82.
30 окт 18, 14:29    [21719006]     Ответить | Цитировать Сообщить модератору
 Re: Помогите составить запрос с исключениями  [new]
Евгений.
Member

Откуда:
Сообщений: 46
TaPaK
звучит так, что необходим приоритет масок

Да.

Или может быть как то проверять что маска сама не подпадает под другую маску и тогда не учитывать ее в JOIN.
30 окт 18, 14:33    [21719013]     Ответить | Цитировать Сообщить модератору
 Re: Помогите составить запрос с исключениями  [new]
court
Member

Откуда:
Сообщений: 1956
типа такого что-то ...
select distinct a.* 
from [Есть рабочая таблица (номера телефонов)] a
left join [Есть таблица (справочник с шаблонами номеров)] b on a.numb like b.template  + '%' and provider <> 'p1'
cross apply
    (SELECT top 1 len(b1.template) as len_template FROM [Есть таблица (справочник с шаблонами номеров)] b1
    WHERE b1.provider = 'p1' AND a.numb LIKE b1.template + '%' order by len(b1.template) desc) x
where b.provider is null or len(b.template)<x.len_template
30 окт 18, 14:54    [21719040]     Ответить | Цитировать Сообщить модератору
 Re: Помогите составить запрос с исключениями  [new]
court
Member

Откуда:
Сообщений: 1956
так будет на порядок лучше
;with cte as (
	select 
		b.template 
		,b.provider
	from [Есть таблица (справочник с шаблонами номеров)] b
	where	not exists(select 1 from [Есть таблица (справочник с шаблонами номеров)] b1 where b.template like b1.template+'%')
)
select a.* 
from [Есть рабочая таблица (номера телефонов)] a 
inner join cte on a.numb like cte.template+'%'
where cte.provider = 'p1'
30 окт 18, 15:02    [21719055]     Ответить | Цитировать Сообщить модератору
 Re: Помогите составить запрос с исключениями  [new]
court
Member

Откуда:
Сообщений: 1956
fix
court
так будет на порядок лучше
;with cte as (
	select 
		b.template 
		,b.provider
	from [Есть таблица (справочник с шаблонами номеров)] b
	where	not exists(select 1 from [Есть таблица (справочник с шаблонами номеров)] b1 where b1.template like b.template+'%')
)
select a.* 
from [Есть рабочая таблица (номера телефонов)] a 
inner join cte on a.numb like cte.template+'%'
where cte.provider = 'p1'
30 окт 18, 15:03    [21719060]     Ответить | Цитировать Сообщить модератору
 Re: Помогите составить запрос с исключениями  [new]
Евгений.
Member

Откуда:
Сообщений: 46
court
типа такого что-то ...
select distinct a.* 
from [Есть рабочая таблица (номера телефонов)] a
left join [Есть таблица (справочник с шаблонами номеров)] b on a.numb like b.template  + '%' and provider <> 'p1'
cross apply
    (SELECT top 1 len(b1.template) as len_template FROM [Есть таблица (справочник с шаблонами номеров)] b1
    WHERE b1.provider = 'p1' AND a.numb LIKE b1.template + '%' order by len(b1.template) desc) x
where b.provider is null or len(b.template)<x.len_template


Круто. Вроде бы работает корректно, только поменял порядок сортировки DESC на ASC
30 окт 18, 15:07    [21719075]     Ответить | Цитировать Сообщить модератору
 Re: Помогите составить запрос с исключениями  [new]
Евгений.
Member

Откуда:
Сообщений: 46
court
fix
court
так будет на порядок лучше
;with cte as (
	select 
		b.template 
		,b.provider
	from [Есть таблица (справочник с шаблонами номеров)] b
	where	not exists(select 1 from [Есть таблица (справочник с шаблонами номеров)] b1 where b1.template like b.template+'%')
)
select a.* 
from [Есть рабочая таблица (номера телефонов)] a 
inner join cte on a.numb like cte.template+'%'
where cte.provider = 'p1'


Первый пример (с сортировкой ASC) рабочий судя по тестам, сейчас попробую этот.
30 окт 18, 15:09    [21719082]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить