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

Откуда: от верблюда
Сообщений: 428
запрос возвращающий правильный результат
DECLARE @Table1 table (ID int, Val int)
INSERT @Table1 values(1,5)
INSERT @Table1 values(2,8)
INSERT @Table1 values(3,10)
INSERT @Table1 values(4,7)
INSERT @Table1 values(5,0)
INSERT @Table1 values(6,4)

DECLARE @Table2 table (ID2 int, Val2 int)
INSERT @Table2 values(1,1)
INSERT @Table2 values(2,5)
INSERT @Table2 values(3,8)
INSERT @Table2 values(5,5)

select * from @table1
where id not in (select id2 from @table2 where id2 not in (select id2 from @table2 where val2 = 5))


можно/нужно ли избавиться от NOT IN в данном случае?
я чет не соображу
29 ноя 12, 13:43    [13549155]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли представить NOT IN (NOT IN ...) через JOIN/NOT EXISTS?  [new]
Добрый Э - Эх
Guest
избавиться можно. а вот нужно ли - много от чего зависит.
29 ноя 12, 13:47    [13549229]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли представить NOT IN (NOT IN ...) через JOIN/NOT EXISTS?  [new]
iap
Member

Откуда: Москва
Сообщений: 47145
TJ001,

желательно. И обязательно, если id2 может быть NULLом.
29 ноя 12, 13:49    [13549253]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли представить NOT IN (NOT IN ...) через JOIN/NOT EXISTS?  [new]
TJ001
Member

Откуда: от верблюда
Сообщений: 428
Добрый Э - Эх
избавиться можно. а вот нужно ли - много от чего зависит.

Table1 большая over9000, увеличивается быстро
Table2 имеет пару сотен записей, в год прирост составляет 500-1000
29 ноя 12, 13:50    [13549270]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли представить NOT IN (NOT IN ...) через JOIN/NOT EXISTS?  [new]
TJ001
Member

Откуда: от верблюда
Сообщений: 428
iap
TJ001,

желательно. И обязательно, если id2 может быть NULLом.


NULL там быть не может

но как избавиться? подскажите, други
29 ноя 12, 13:50    [13549291]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли представить NOT IN (NOT IN ...) через JOIN/NOT EXISTS?  [new]
petre
Member

Откуда: Кривой Рог
Сообщений: 42
TJ001,
-1
 where id not in ((select id2 from @table2 where val2 <> 5)
29 ноя 12, 13:51    [13549303]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли представить NOT IN (NOT IN ...) через JOIN/NOT EXISTS?  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
TJ001,

Какая-то странная логика, не могу понять. В самом вложенном запросе, вы выбираете id-шки, с val = 5, потом выбираете все ид-шки, которые не вошли в ранее выбранные для 5, почему сразу не написать where val2<>5 (ну и плюс проверку на null, если колонка nullable). А дальше зависит от схемы, можно переписать not in на not exists, а возможно за вас это сделает оптимизатор.
29 ноя 12, 13:53    [13549333]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли представить NOT IN (NOT IN ...) через JOIN/NOT EXISTS?  [new]
TJ001
Member

Откуда: от верблюда
Сообщений: 428
petre
TJ001,
-1
 where id not in ((select id2 from @table2 where val2 <> 5)


к сожалению, так нельзя ибо проверка там не такая простая как val=5, там идет проверка на кучу полей во внимание принимаются значения не только те которые равны, но и те которые NULL тоже принимаются как подходящие под любое условие

вощем проверки там загонные и просто поставить неравенство не получится
29 ноя 12, 13:54    [13549342]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли представить NOT IN (NOT IN ...) через JOIN/NOT EXISTS?  [new]
HandKot
Member

Откуда: Sergiev Posad
Сообщений: 3059
как вариант
select * from @table1 t1
left join (select id2 from @table2 except select id2 from @table2 where val2 = 5) t2 on t2.ID2 = t1.id
where 
	t2.ID2 Is Null
29 ноя 12, 14:01    [13549435]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли представить NOT IN (NOT IN ...) через JOIN/NOT EXISTS?  [new]
TJ001
Member

Откуда: от верблюда
Сообщений: 428
HandKot,

как всегда я мамонт

Microsoft SQL Server 2000 - 8.00.2187 (Intel X86) Mar 9 2006 11:38:51 Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

except не для меня :(
29 ноя 12, 14:03    [13549457]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли представить NOT IN (NOT IN ...) через JOIN/NOT EXISTS?  [new]
TJ001
Member

Откуда: от верблюда
Сообщений: 428
TJ001
HandKot,

как всегда я мамонт

Microsoft SQL Server 2000 - 8.00.2187 (Intel X86) Mar 9 2006 11:38:51 Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

except не для меня :(


уж простите, сразу забыл предупредить...
29 ноя 12, 14:04    [13549467]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли представить NOT IN (NOT IN ...) через JOIN/NOT EXISTS?  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
TJ001
к сожалению, так нельзя ибо проверка там не такая простая как val=5, там идет проверка на кучу полей во внимание принимаются значения не только те которые равны, но и те которые NULL тоже принимаются как подходящие под любое условие
Так можно, просто видимо лень ковыряться в сложном условии, переписывая его таким образом, чтобы оно учитывало нуллы и после переписывания ничего не нарушилось.
Запрос работает правильно, переписывать и причесывать его вам не хочется, про производительность вы ничего не сказали... В таком случае, чего мы пытаемся добиться?
Можете все ИДшки полученные в результате ваших сложных вычислений поместить во временную таблицу или табличную переменную, и в основном запрос проверять их наличие в ней. Цель непонятна.
29 ноя 12, 14:17    [13549616]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли представить NOT IN (NOT IN ...) через JOIN/NOT EXISTS?  [new]
invm
Member

Откуда: Москва
Сообщений: 9845
select * from @table1 t
where not exists(select * from @table2 where id2 = t.id and not (проверка на кучу полей во внимание принимаются значения не только те которые равны, но и те которые NULL тоже принимаются как подходящие под любое условие))
29 ноя 12, 14:19    [13549630]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли представить NOT IN (NOT IN ...) через JOIN/NOT EXISTS?  [new]
TJ001
Member

Откуда: от верблюда
Сообщений: 428
invm,

дадада, работает!))) спасибо!!!
29 ноя 12, 14:35    [13549781]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли представить NOT IN (NOT IN ...) через JOIN/NOT EXISTS?  [new]
TJ001
Member

Откуда: от верблюда
Сообщений: 428
но надо потестить)
29 ноя 12, 14:39    [13549816]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли представить NOT IN (NOT IN ...) через JOIN/NOT EXISTS?  [new]
TJ001
Member

Откуда: от верблюда
Сообщений: 428
TJ001
но надо потестить)

не работает(
эххх....
29 ноя 12, 16:41    [13550966]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли представить NOT IN (NOT IN ...) через JOIN/NOT EXISTS?  [new]
TJ001
Member

Откуда: от верблюда
Сообщений: 428
SomewhereSomehow
TJ001
к сожалению, так нельзя ибо проверка там не такая простая как val=5, там идет проверка на кучу полей во внимание принимаются значения не только те которые равны, но и те которые NULL тоже принимаются как подходящие под любое условие
Так можно, просто видимо лень ковыряться в сложном условии, переписывая его таким образом, чтобы оно учитывало нуллы и после переписывания ничего не нарушилось.
Запрос работает правильно, переписывать и причесывать его вам не хочется, про производительность вы ничего не сказали... В таком случае, чего мы пытаемся добиться?
Можете все ИДшки полученные в результате ваших сложных вычислений поместить во временную таблицу или табличную переменную, и в основном запрос проверять их наличие в ней. Цель непонятна.


может и правда единственный вариант - менять условия(
29 ноя 12, 16:43    [13550987]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли представить NOT IN (NOT IN ...) через JOIN/NOT EXISTS?  [new]
TJ001
Member

Откуда: от верблюда
Сообщений: 428
если быть совсем уж точным, то упрощенный вариант запроса выглядит вот так (часть условий убраны)

в таблице 2 идентификаторы ID2 задвоены, это нормально, т.к. это не первичный ключ
но смысл и фишка заключается в том, что полем val3 равным нулю можно считать только в том случае, если в разрезе всего идентификатора val3=0
DECLARE @Table1 table (ID int, Val int)
INSERT @Table1 values(1,5)
INSERT @Table1 values(2,8)
INSERT @Table1 values(3,10)
INSERT @Table1 values(4,7)
INSERT @Table1 values(5,0)
INSERT @Table1 values(6,4)

DECLARE @Table2 table (ID2 int, Val2 int, val3 int)
INSERT @Table2 values(1,1,0)
INSERT @Table2 values(1,1,0)
INSERT @Table2 values(2,5,1)
INSERT @Table2 values(2,5,0)
INSERT @Table2 values(3,8,1)
INSERT @Table2 values(5,5,5)

select
 id2
from 
 @table2
where
 id2 not in 
   (select
     id2
    from
     @table2
    join
     (select
       count(NULLIF(val3,0)) as cVal2,
	   id2 as cId2
      from
       @table2 t2
      group by ID2) t1
	on id2=t1.cId2
    where
     val3 = 0 and cVal2=0)
group by id2
29 ноя 12, 17:19    [13551418]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли представить NOT IN (NOT IN ...) через JOIN/NOT EXISTS?  [new]
HandKot
Member

Откуда: Sergiev Posad
Сообщений: 3059
не понял, зачем так все навернуто, если можно было использовать having

и вот еще вариант на основе примера

select
	t2.ID2
from
	@Table2 t2
where
	0 <> some (select val3 from @Table2 t2_ where t2_.ID2 = t2.ID2)
29 ноя 12, 18:22    [13551945]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли представить NOT IN (NOT IN ...) через JOIN/NOT EXISTS?  [new]
HandKot
Member

Откуда: Sergiev Posad
Сообщений: 3059
TJ001, и все таки, как задача реально поставлена?
29 ноя 12, 18:25    [13551962]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли представить NOT IN (NOT IN ...) через JOIN/NOT EXISTS?  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
TJ001,

not хорошо раскрывается в булевой логике, но тут троичная логика, сравните
declare @t table (a int)
insert @t values (1),(2),(null);
select * from @t where a<>1; --результат: 2
select * from @t where not(a=1); --результат:2, то же самое
select * from @t where a<>1 or a is null; --результат: 2, null
select * from @t where not(a=1 and a is not null); --результат: 2, null


Тоже не совсем понял задачу. Так что, на правах догадки.
Если у вас задача исключить все группы, сгруппированные по id2, в которых только нулевые или нулл значения val3, и выбрать из таблицы 1 все ид-шки которые ответствуют группам, то вот например развитие мысли предложенной HandKot.
Если нужно исключить все группы где только 0 или нулл, то значит максимальное значение в этой группе тоже 0, значит можно воспользоваться having.
select * 
from 
	@table1 t1 
where
	--выбираем только те id которые есть в выбранных группах
	exists (
		--выбираем все группы по id2, в которых максимальное значение val3 > 0
		select * from @table2 t2 where t2.id2 = t1.id group by id2 having max(val3)>0
	)

Может быть я не правильно понял задачу. Может, в вашем случае в реальном примере, будет лучше воспользоваться временной таблицей или табличной переменной, как я ранее предлагал, не знаю...Короче, правила форума, гласящие то, что нужно описывать задачу целиком, не зря придуманы. Советую обратить внимание =)
29 ноя 12, 21:48    [13552749]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли представить NOT IN (NOT IN ...) через JOIN/NOT EXISTS?  [new]
TJ001
Member

Откуда: от верблюда
Сообщений: 428
SomewhereSomehow,

в таблице 2 множество полей (ну это понятно), записи с ИД2 могут задваиваться, затраиваться и т.д.
NULL там не бывает. поля в основном тоже ключи. если ключ не назначен, значит поле имеет значение 0.

задача такова, чтобы выбрать из таблицы записи, значения которых либо равны конкретным, например 5,
либо те, которые в разрезе ИД2 имеют нули во всех записях. нули во всех записях, это как-бы не заполненное значение, следовательно оно удовлетворяет любым условиям

в данном случае подходят под условие ид2 (1 (потому что нули),5(соответствует конкретному значению))
DECLARE @Table2 table (ID2 int, Val2 int, val3 int)
INSERT @Table2 values(1,1,0)
INSERT @Table2 values(1,1,0)
INSERT @Table2 values(2,5,1)
INSERT @Table2 values(2,5,0)
INSERT @Table2 values(3,8,1)
INSERT @Table2 values(5,5,5)


сокращенный вариант, без всяких NOT
select
     id2
    from
     @table2
    join
     (select
       count(NULLIF(val3,0)) as cVal2,
	   id2 as cId2
      from
       @table2 t2
      group by ID2) t1
	on id2=t1.cId2
    where
     val3 = 5 or cVal2=0
group by id2

можно сказать что список ИД2, удовлетворяющих условиям, получен
но т.к. существуют ситуации, когда в таблице 2 нет ни одной записи с конкретным идентификатором, это означает что у объекта не задано вообще никаких доп параметров, значит он попадает под любые условия (которые проверяются в таблице 2)
сами объекты лежат в таблице 1, таким образом я должен выбрать из таблицы 1 все объекты, у которых настроены конкретные параметры либо не задано их вообще (нет записи в таб2)

поэтому я пошел таким путем - выбрать из таблицы 2 все элементы удовлетворяющие условиям (по которым заданы фильтры)
далее сделать инверсию, т.е. выбрать все остальные элементы (которые не удовлетворяют условиям)
и выполнить выборку из таб1 всех элементов, которых нет в исключающем списке


может это и кривой вариант, но как реализовать иначе не знаю
можно было бы оставить и так, ведь работает же... но душа не на месте, хочется делать правильно, по сему и прошу вашей помощи
30 ноя 12, 10:16    [13554087]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли представить NOT IN (NOT IN ...) через JOIN/NOT EXISTS?  [new]
HandKot
Member

Откуда: Sergiev Posad
Сообщений: 3059
а для таких данных ?
DECLARE @Table2 table (ID2 int, Val2 int, val3 int)
INSERT @Table2 values(1,1,0)
INSERT @Table2 values(1,1,0)
INSERT @Table2 values(2,5,1)
INSERT @Table2 values(2,5,0)
INSERT @Table2 values(3,8,1)
INSERT @Table2 values(5,5,5)
INSERT @Table2 values(5,5,3)
30 ноя 12, 11:01    [13554314]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли представить NOT IN (NOT IN ...) через JOIN/NOT EXISTS?  [new]
TJ001
Member

Откуда: от верблюда
Сообщений: 428
HandKot,
для этих данных
результат должен быть тот же ид2 = 1 (потому что 0 - "не заполнено"), 5 (потому что одна из записей внутри данного ИД2 имеет значение 5, это удовлетворяет условиям)

если добавить еще
INSERT @Table2 values(2,1,5)

тогда в выборку попадет запись ИД2=2, что вновь удовлетворяет условиям задачи
30 ноя 12, 11:16    [13554410]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли представить NOT IN (NOT IN ...) через JOIN/NOT EXISTS?  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
TJ001,

Если правильно понял
DECLARE @Table1 table (ID int, Val int)
INSERT @Table1 values(1,5)
INSERT @Table1 values(2,8)
INSERT @Table1 values(3,10)
INSERT @Table1 values(4,7)
INSERT @Table1 values(5,0)
INSERT @Table1 values(6,4)
INSERT @Table1 values(7,4)

DECLARE @Table2 table (ID2 int, Val2 int, val3 int)
INSERT @Table2 values(1,1,0)
INSERT @Table2 values(1,1,0)
INSERT @Table2 values(2,5,1)
INSERT @Table2 values(2,5,0)
--INSERT @Table2 values(2,1,5)
INSERT @Table2 values(3,8,1)
INSERT @Table2 values(5,5,6)
INSERT @Table2 values(5,5,5)
INSERT @Table2 values(6,5,3)

declare @val3 int;
set @val3 = 5;

--м.б. так
select 
	t1.*
from 
	@Table1 t1
	join (
		select ID2 from @Table2 where val3 <= @val3 group by ID2
		having max(val3) = @val3 or max(val3) = 0
	) t2 on t1.ID = t2.ID2
union all
select
	t1.*
from 
	@Table1 t1
where
	not exists (select * from @Table2 t2 where t1.ID = t2.ID2)
;

--или так
select
	t1.ID,
	t1.val
from 
	@Table1 t1
	left join @Table2 t2 on t1.ID = t2.ID2
where
	t2.ID2 is null or
	t2.val3 <= @val3
group by all
	t1.ID,
	t1.val,
	t2.ID2
having 
	max(t2.val3) = @val3 or 
	max(t2.val3) = 0 or
	t2.ID2 is null
30 ноя 12, 13:30    [13555558]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2 3   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить