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

Откуда:
Сообщений: 127
Добрый день.
Задача: исключить из запроса все строки, где хотя-бы одна из колонок а1-а7 равняется определенному значению, а все остальные 0. Условие в where генерируется динамически, в зависимости от количества значений, которые нужно исключить.
На практике колонок больше.
Мое решение слишком медленное.
create table #t (
id int identity primary key,
a1 int not null default 0,
a2 int not null default 0,
a3 int not null default 0,
a4 int not null default 0,
a5 int not null default 0,
a6 int not null default 0,
a7 int not null default 0
)
insert into #t 
values(1,0,1,0,5,6,7),
	  (2,0,1,0,5,6,7),
	  (1,0,1,0,8,6,7),
	  (1,0,1,0,0,0,0),
	  (1,0,1,0,1,1,0),
	  (0,2,2,0,0,2,0),
	  (1,1,1,1,1,1,1),
	  (0,0,0,0,0,0,0)

select * from #t where 
(not (1 in (a1,a2,a3,a4,a5,a6,a7)
	and a1 in (1,0)
	and a2 in (1,0)
	and a3 in (1,0)
	and a4 in (1,0)
	and a5 in (1,0)
	and a6 in (1,0)
	and a7 in (1,0))
)
and 
(not (2 in (a1,a2,a3,a4,a5,a6,a7)
	and a1 in (2,0)
	and a2 in (2,0)
	and a3 in (2,0)
	and a4 in (2,0)
	and a5 in (2,0)
	and a6 in (2,0)
	and a7 in (2,0))
)
9 апр 13, 15:04    [14156931]     Ответить | Цитировать Сообщить модератору
 Re: Исключение строк из запроса.  [new]
Shakill
Member

Откуда: мск
Сообщений: 1880
pio777,

если столбцы развернуть в строки, то можно будет проводить агрегацию
как-то так

create table #e (val int); -- таблица исключаемых значений
insert #e values (1), (2);

with cte as (
	select vx.id, vx.val
	from #t t
	cross apply (
		select id, val
		from (values 
			(t.id, t.a1),
			(t.id, t.a2),
			(t.id, t.a3),
			(t.id, t.a4),
			(t.id, t.a5),
			(t.id, t.a6),
			(t.id, t.a7)
		) vv(id, val)
	) vx 
),
counts as (
	select id, val, count(*) cnt
	from cte
	group by id, val
),
exrows as (
	select c.id
	from counts c
	join #e e on e.val = c.val 
	left join counts c0 on c0.id = c.id and c0.val = 0
	where c.cnt + isnull(c0.cnt, 0) = 7 -- количество проверяемых столбцов
)
select *
from #t m
where not exists(select id from exrows e where e.id = m.id)
9 апр 13, 15:53    [14157387]     Ответить | Цитировать Сообщить модератору
 Re: Исключение строк из запроса.  [new]
pio777
Member

Откуда:
Сообщений: 127
Shakill,
Простите, что не сообщил, но есть некоторые ограничения. Запрос формирует приложение, я могу только в where добавить свой and, cte и временные таблицы тоже не получится использовать.
9 апр 13, 16:01    [14157457]     Ответить | Цитировать Сообщить модератору
 Re: Исключение строк из запроса.  [new]
Glory
Member

Откуда:
Сообщений: 104760
select *
from #t
where sign(a1)+sign(a2)+sign(a3)+sign(a4)+sign(a5)+sign(a6)+sign(a7) = 1 
and a1+a2+a3+a4+a5+a6+a7=@myvar
9 апр 13, 16:03    [14157490]     Ответить | Цитировать Сообщить модератору
 Re: Исключение строк из запроса.  [new]
iap
Member

Откуда: Москва
Сообщений: 47000
Glory
select *
from #t
where sign(a1)+sign(a2)+sign(a3)+sign(a4)+sign(a5)+sign(a6)+sign(a7) = 1 
and a1+a2+a3+a4+a5+a6+a7=@myvar
Наверно, в SIGN надо ABS добавить? А то +1-1=0
9 апр 13, 16:08    [14157526]     Ответить | Цитировать Сообщить модератору
 Re: Исключение строк из запроса.  [new]
Glory
Member

Откуда:
Сообщений: 104760
iap
Наверно, в SIGN надо ABS добавить? А то +1-1=0

+1 и -1 - это значения уже в 2х полях. А по условию задачи, только одно поле должно быть заполнено
скорее уж так
ABS(sign(a1)+sign(a2)+sign(a3)+sign(a4)+sign(a5)+sign(a6)+sign(a7))
9 апр 13, 16:11    [14157554]     Ответить | Цитировать Сообщить модератору
 Re: Исключение строк из запроса.  [new]
Shakill
Member

Откуда: мск
Сообщений: 1880
Glory,

у автора формулировка "хотя бы одна из колонок равняется", то есть может быть и несколько, вплоть до всех, насколько я понял
9 апр 13, 16:13    [14157568]     Ответить | Цитировать Сообщить модератору
 Re: Исключение строк из запроса.  [new]
Glory
Member

Откуда:
Сообщений: 104760
Shakill
автора формулировка "хотя бы одна из колонок равняется", то есть может быть и несколько, вплоть до всех, насколько я понял

там 2 условия
"одна из колонок а1-а7 равняется определенному значению,
а все остальные 0"
9 апр 13, 16:14    [14157580]     Ответить | Цитировать Сообщить модератору
 Re: Исключение строк из запроса.  [new]
Shakill
Member

Откуда: мск
Сообщений: 1880
Glory, там "хотя бы одна"
9 апр 13, 16:15    [14157592]     Ответить | Цитировать Сообщить модератору
 Re: Исключение строк из запроса.  [new]
Glory
Member

Откуда:
Сообщений: 104760
Shakill
Glory, там "хотя бы одна"

И что неправильного ?
шесть 0-лей плюс одно любое значения должны равняться заданному значению
9 апр 13, 16:17    [14157606]     Ответить | Цитировать Сообщить модератору
 Re: Исключение строк из запроса.  [new]
Shakill
Member

Откуда: мск
Сообщений: 1880
Glory
Shakill
Glory, там "хотя бы одна"

И что неправильного ?
шесть 0-лей плюс одно любое значения должны равняться заданному значению

пять нулевых и два искомых тоже подходят в эту формулировку. семь искомых и отсутствие нулевых - тоже
9 апр 13, 16:19    [14157627]     Ответить | Цитировать Сообщить модератору
 Re: Исключение строк из запроса.  [new]
Glory
Member

Откуда:
Сообщений: 104760
Glory
iap
Наверно, в SIGN надо ABS добавить? А то +1-1=0

+1 и -1 - это значения уже в 2х полях. А по условию задачи, только одно поле должно быть заполнено
скорее уж так
ABS(sign(a1)+sign(a2)+sign(a3)+sign(a4)+sign(a5)+sign(a6)+sign(a7))

Хотя для -1+1+1 таки нужен ABS в каждом sign
9 апр 13, 16:21    [14157639]     Ответить | Цитировать Сообщить модератору
 Re: Исключение строк из запроса.  [new]
Glory
Member

Откуда:
Сообщений: 104760
Shakill
пять нулевых и два искомых тоже подходят в эту формулировку. семь искомых и отсутствие нулевых - тоже

"где хотя-бы одна из колонок а1-а7 равняется определенному значению, а все остальные 0"
Это как при 2 искомых колонках "а все остальные 0" ?
9 апр 13, 16:22    [14157649]     Ответить | Цитировать Сообщить модератору
 Re: Исключение строк из запроса.  [new]
Shakill
Member

Откуда: мск
Сообщений: 1880
Glory
Shakill
пять нулевых и два искомых тоже подходят в эту формулировку. семь искомых и отсутствие нулевых - тоже

"где хотя-бы одна из колонок а1-а7 равняется определенному значению, а все остальные 0"
Это как при 2 искомых колонках "а все остальные 0" ?

ну я и говорю. если в строке в трех полях - единицы, а остальные четыре содержат ноль, то такую строку тоже надо исключить. посмотрите на фильтр у автора изначальный
9 апр 13, 16:26    [14157685]     Ответить | Цитировать Сообщить модератору
 Re: Исключение строк из запроса.  [new]
так ?
Guest
если все значения a(i)>=0
select *
--,(a1+a2+a3+a4+a5+a6+a7)/nullif(sign(a1)+sign(a2)+sign(a3)+sign(a4)+sign(a5)+sign(a6)+sign(a7),0)
--,sign(a1)+sign(a2)+sign(a3)+sign(a4)+sign(a5)+sign(a6)+sign(a7)
from #t
where 
	sign(a1)+sign(a2)+sign(a3)+sign(a4)+sign(a5)+sign(a6)+sign(a7)=0
or
	(a1+a2+a3+a4+a5+a6+a7)/nullif(sign(a1)+sign(a2)+sign(a3)+sign(a4)+sign(a5)+sign(a6)+sign(a7),0) not in (1,2)
9 апр 13, 16:31    [14157732]     Ответить | Цитировать Сообщить модератору
 Re: Исключение строк из запроса.  [new]
Glory
Member

Откуда:
Сообщений: 104760
Ну дороботайте чуть чуть
declare @x int
set @x = 1
select *,sign(a1)+sign(a2)+sign(a3)+sign(a4)+sign(a5)+sign(a6)+sign(a7),a1+a2+a3+a4+a5+a6+a7
from #t
where (sign(a1)+sign(a2)+sign(a3)+sign(a4)+sign(a5)+sign(a6)+sign(a7))*@x = a1+a2+a3+a4+a5+a6+a7
9 апр 13, 16:33    [14157743]     Ответить | Цитировать Сообщить модератору
 Re: Исключение строк из запроса.  [new]
так,
Guest
так ?
если все значения a(i)>=0
select *
--,(a1+a2+a3+a4+a5+a6+a7)/nullif(sign(a1)+sign(a2)+sign(a3)+sign(a4)+sign(a5)+sign(a6)+sign(a7),0)
--,sign(a1)+sign(a2)+sign(a3)+sign(a4)+sign(a5)+sign(a6)+sign(a7)
from #t
where 
	sign(a1)+sign(a2)+sign(a3)+sign(a4)+sign(a5)+sign(a6)+sign(a7)=0
or
	(a1+a2+a3+a4+a5+a6+a7)/nullif(sign(a1)+sign(a2)+sign(a3)+sign(a4)+sign(a5)+sign(a6)+sign(a7),0) not in (1,2)

нее, хрень на таком
insert into #t values
	  (1,3,0,0,0,0,0)
9 апр 13, 16:38    [14157778]     Ответить | Цитировать Сообщить модератору
 Re: Исключение строк из запроса.  [new]
angel_zar
Member

Откуда: Барнаул
Сообщений: 902
Declare @t table (
id int identity primary key,
a1 int not null default 0,
a2 int not null default 0,
a3 int not null default 0,
a4 int not null default 0,
a5 int not null default 0,
a6 int not null default 0,
a7 int not null default 0
)
insert into @t 
SELECT 1,0,1,0,5,6,7
UNION ALL
SELECT
	  2,0,1,0,5,6,7
UNION ALL
SELECT
	  1,0,1,0,8,6,7
UNION ALL
SELECT
	  1,0,1,0,0,0,0
UNION ALL
SELECT
	  1,0,1,0,1,1,0
UNION ALL
SELECT
	  0,2,2,0,0,2,0
UNION ALL
SELECT
	  1,1,1,1,1,1,1
UNION ALL
SELECT
	  0,0,0,0,0,0,0

Select * from @t

Declare @MyVal int
set @MyVal=1

Select *
from @t
where
ABS(IsNull(NullIf(a1,@MyVal),0))+
ABS(IsNull(NullIf(a2,@MyVal),0))+
ABS(IsNull(NullIf(a3,@MyVal),0))+
ABS(IsNull(NullIf(a4,@MyVal),0))+
ABS(IsNull(NullIf(a5,@MyVal),0))+
ABS(IsNull(NullIf(a6,@MyVal),0))+
ABS(IsNull(NullIf(a7,@MyVal),0))
=0

Я такую, бяку наваял
9 апр 13, 16:38    [14157781]     Ответить | Цитировать Сообщить модератору
 Re: Исключение строк из запроса.  [new]
angel_zar
Member

Откуда: Барнаул
Сообщений: 902
angel_zar,

Хотя, хрень, все 0ли попадают
9 апр 13, 16:40    [14157802]     Ответить | Цитировать Сообщить модератору
 Re: Исключение строк из запроса.  [new]
так,
Guest
незнаю, сумеет ли ТС "запихнуть" это в условие, но так - не врёт
select * from #t
where id not in
	(select id from
		(select * from #t
			unpivot(val FOR a IN (a1,a2,a3,a4,a5,a6,a7))AS unpvt) a
	where val <> 0
	group by id
	having var(val)=0 and avg(val) in (1,2))
9 апр 13, 16:53    [14157930]     Ответить | Цитировать Сообщить модератору
 Re: Исключение строк из запроса.  [new]
iap
Member

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

а какой результат должен получиться на Ваших данных?

И как понять "хотя-бы одна из колонок а1-а7 равняется определенному значению, а все остальные 0"
Жирные слова поясните, пожалуйста.

Это что же, исключить записи, в которых некоторые колонки равны друг другу и не равны 0, а остальные - 0?
Некоторые колонки равны заданному снаружи значению, а остальные - 0?
Одна-единственная колонка - не ноль, остальные - ноль?
Зачем в примере заданы 1 и 2? Как это соответствует сформулированному (криво и невразумительно) условию?
9 апр 13, 17:12    [14158072]     Ответить | Цитировать Сообщить модератору
 Re: Исключение строк из запроса.  [new]
`
Guest
iap
pio777,

а какой результат должен получиться на Ваших данных?

в стартовом посте, есть запрос ТС, результат которого, его устраивает,
его не устраивает скорость
9 апр 13, 17:15    [14158100]     Ответить | Цитировать Сообщить модератору
 Re: Исключение строк из запроса.  [new]
iap
Member

Откуда: Москва
Сообщений: 47000
`
iap
pio777,

а какой результат должен получиться на Ваших данных?

в стартовом посте, есть запрос ТС, результат которого, его устраивает,
его не устраивает скорость
SELECT * FROM #t WHERE NOT EXISTS
(
 SELECT *
 FROM(VALUES(a1),(a2),(a3),(a4),(a5),(a6),(a7))T(a)
 HAVING COUNT(DISTINCT a)=1 AND MAX(a)IN(1,2)AND MIN(a)IN(1,2)
     OR COUNT(DISTINCT a)=2 AND MAX(a)IN(1,2)AND MIN(a)=0
);
9 апр 13, 17:26    [14158165]     Ответить | Цитировать Сообщить модератору
 Re: Исключение строк из запроса.  [new]
pio777
Member

Откуда:
Сообщений: 127
iap,

Если вы выполните мой запрос, то получите то, что должно быть.
Хотя-бы одна и колонок значит, что от 1, 2, 3,... или все 7.
Значение берется с приложения, в зависимости от того, что выберет пользователь.
Т.е. если я выбрал 1 то выпадают строки:
1,0,0,0,0,0,0
1,1,0,0,0,0,0
1,1,1,0,0,0,0
1,1,1,1,0,0,0
1,1,1,1,1,0,0
1,1,1,1,1,1,0
1,1,1,1,1,1,1
0,1,0,0,0,0,0
0,0,1,0,0,0,0
.................
В приложении можно выбрать несколько значений. Пример для исключение 1 и 2.
9 апр 13, 17:32    [14158196]     Ответить | Цитировать Сообщить модератору
 Re: Исключение строк из запроса.  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
тоже вариант подкину:)
-- исключить из запроса все строки, где хотя-бы одна из колонок а1-а7 равняется определенному значению, 
-- а все остальные 0. 
declare @n int=5
select *
from #t 
cross apply (select qty_nonzero=
  ABS(SIGN(a1))+ABS(SIGN(a2))+ABS(SIGN(a3))+ABS(SIGN(a4))
  +ABS(SIGN(a5))+ABS(SIGN(a6))+ABS(SIGN(a7))) c
where qty_nonzero!=1 or qty_nonzero=1 and a1+a2+a3+a4+a5+a6+a7!=@n
9 апр 13, 17:34    [14158206]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить