ReSQL.ru
     
powered by simpleCommunicator - 2.0.49     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Исключение строк из запроса.
25 сообщений из 43, страница 1 из 2
  1  все
Исключение строк из запроса.
    #38218910
pio777
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
pio777
Гость
Добрый день.
Задача: исключить из запроса все строки, где хотя-бы одна из колонок а1-а7 равняется определенному значению, а все остальные 0. Условие в where генерируется динамически, в зависимости от количества значений, которые нужно исключить.
На практике колонок больше.
Мое решение слишком медленное.
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
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))
)
...
Рейтинг: 0 / 0
09.04.2013, 15:04
| Ответить | Цитировать | Написать  
Исключение строк из запроса.
    #38219022
Фотография Shakill
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Shakill
Участник
pio777,

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

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
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)
...
Рейтинг: 0 / 0
09.04.2013, 15:53
| Ответить | Цитировать | Написать  
Исключение строк из запроса.
    #38219036
pio777
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
pio777
Гость
Shakill,
Простите, что не сообщил, но есть некоторые ограничения. Запрос формирует приложение, я могу только в where добавить свой and, cte и временные таблицы тоже не получится использовать.
...
Рейтинг: 0 / 0
09.04.2013, 16:01
| Ответить | Цитировать | Написать  
Исключение строк из запроса.
    #38219041
Glory
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Glory
Участник
Код: sql
1.
2.
3.
4.
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
...
Рейтинг: 0 / 0
09.04.2013, 16:03
| Ответить | Цитировать | Написать  
Исключение строк из запроса.
    #38219049
iap
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
iap
Участник
Glory
Код: sql
1.
2.
3.
4.
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
...
Рейтинг: 0 / 0
09.04.2013, 16:08
| Ответить | Цитировать | Написать  
Исключение строк из запроса.
    #38219058
Glory
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
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))
...
Рейтинг: 0 / 0
09.04.2013, 16:11
| Ответить | Цитировать | Написать  
Исключение строк из запроса.
    #38219062
Фотография Shakill
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Shakill
Участник
Glory,

у автора формулировка "хотя бы одна из колонок равняется", то есть может быть и несколько, вплоть до всех, насколько я понял
...
Рейтинг: 0 / 0
09.04.2013, 16:13
| Ответить | Цитировать | Написать  
Исключение строк из запроса.
    #38219066
Glory
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Glory
Участник
Shakillавтора формулировка "хотя бы одна из колонок равняется", то есть может быть и несколько, вплоть до всех, насколько я понял
там 2 условия
"одна из колонок а1-а7 равняется определенному значению,
а все остальные 0"
...
Рейтинг: 0 / 0
09.04.2013, 16:14
| Ответить | Цитировать | Написать  
Исключение строк из запроса.
    #38219070
Фотография Shakill
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Shakill
Участник
Glory, там "хотя бы одна"
...
Рейтинг: 0 / 0
09.04.2013, 16:15
| Ответить | Цитировать | Написать  
Исключение строк из запроса.
    #38219075
Glory
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Glory
Участник
ShakillGlory, там "хотя бы одна"
И что неправильного ?
шесть 0-лей плюс одно любое значения должны равняться заданному значению
...
Рейтинг: 0 / 0
09.04.2013, 16:17
| Ответить | Цитировать | Написать  
Исключение строк из запроса.
    #38219082
Фотография Shakill
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Shakill
Участник
GloryShakillGlory, там "хотя бы одна"
И что неправильного ?
шесть 0-лей плюс одно любое значения должны равняться заданному значению
пять нулевых и два искомых тоже подходят в эту формулировку. семь искомых и отсутствие нулевых - тоже
...
Рейтинг: 0 / 0
09.04.2013, 16:19
| Ответить | Цитировать | Написать  
Исключение строк из запроса.
    #38219085
Glory
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Glory
Участник
GloryiapНаверно, в 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
...
Рейтинг: 0 / 0
09.04.2013, 16:21
| Ответить | Цитировать | Написать  
Исключение строк из запроса.
    #38219086
Glory
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Glory
Участник
Shakillпять нулевых и два искомых тоже подходят в эту формулировку. семь искомых и отсутствие нулевых - тоже
"где хотя-бы одна из колонок а1-а7 равняется определенному значению, а все остальные 0"
Это как при 2 искомых колонках "а все остальные 0" ?
...
Рейтинг: 0 / 0
09.04.2013, 16:22
| Ответить | Цитировать | Написать  
Исключение строк из запроса.
    #38219093
Фотография Shakill
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Shakill
Участник
GloryShakillпять нулевых и два искомых тоже подходят в эту формулировку. семь искомых и отсутствие нулевых - тоже
"где хотя-бы одна из колонок а1-а7 равняется определенному значению, а все остальные 0"
Это как при 2 искомых колонках "а все остальные 0" ?
ну я и говорю. если в строке в трех полях - единицы, а остальные четыре содержат ноль, то такую строку тоже надо исключить. посмотрите на фильтр у автора изначальный
...
Рейтинг: 0 / 0
09.04.2013, 16:26
| Ответить | Цитировать | Написать  
Исключение строк из запроса.
    #38219106
так ?
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
так ?
Гость
если все значения a(i)>=0
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
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)
...
Рейтинг: 0 / 0
09.04.2013, 16:31
| Ответить | Цитировать | Написать  
Исключение строк из запроса.
    #38219107
Glory
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Glory
Участник
Ну дороботайте чуть чуть
Код: sql
1.
2.
3.
4.
5.
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
...
Рейтинг: 0 / 0
09.04.2013, 16:33
| Ответить | Цитировать | Написать  
Исключение строк из запроса.
    #38219118
так,
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
так,
Гость
так ?если все значения a(i)>=0
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
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)


нее, хрень на таком
Код: sql
1.
2.
insert into #t values
	  (1,3,0,0,0,0,0)
...
Рейтинг: 0 / 0
09.04.2013, 16:38
| Ответить | Цитировать | Написать  
Исключение строк из запроса.
    #38219120
angel_zar
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
angel_zar
Участник
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
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


Я такую, бяку наваял
...
Рейтинг: 0 / 0
09.04.2013, 16:38
| Ответить | Цитировать | Написать  
Исключение строк из запроса.
    #38219128
angel_zar
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
angel_zar
Участник
angel_zar,

Хотя, хрень, все 0ли попадают
...
Рейтинг: 0 / 0
09.04.2013, 16:40
| Ответить | Цитировать | Написать  
Исключение строк из запроса.
    #38219158
так,
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
так,
Гость
незнаю, сумеет ли ТС "запихнуть" это в условие, но так - не врёт
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
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))
...
Рейтинг: 0 / 0
09.04.2013, 16:53
| Ответить | Цитировать | Написать  
Исключение строк из запроса.
    #38219190
iap
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
iap
Участник
pio777,

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

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

Это что же, исключить записи, в которых некоторые колонки равны друг другу и не равны 0, а остальные - 0?
Некоторые колонки равны заданному снаружи значению, а остальные - 0?
Одна-единственная колонка - не ноль, остальные - ноль?
Зачем в примере заданы 1 и 2? Как это соответствует сформулированному (криво и невразумительно) условию?
...
Рейтинг: 0 / 0
09.04.2013, 17:12
| Ответить | Цитировать | Написать  
Исключение строк из запроса.
    #38219197
`
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
`
Гость
iappio777,

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

в стартовом посте, есть запрос ТС, результат которого, его устраивает,
его не устраивает скорость
...
Рейтинг: 0 / 0
09.04.2013, 17:15
| Ответить | Цитировать | Написать  
Исключение строк из запроса.
    #38219217
iap
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
iap
Участник
`iappio777,

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

в стартовом посте, есть запрос ТС, результат которого, его устраивает,
его не устраивает скорость
Код: sql
1.
2.
3.
4.
5.
6.
7.
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
);
...
Рейтинг: 0 / 0
09.04.2013, 17:26
| Ответить | Цитировать | Написать  
Исключение строк из запроса.
    #38219228
pio777
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
pio777
Гость
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.
...
Рейтинг: 0 / 0
09.04.2013, 17:32
| Ответить | Цитировать | Написать  
Исключение строк из запроса.
    #38219232
Cygapb-007
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Cygapb-007
Участник
тоже вариант подкину:)
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
-- исключить из запроса все строки, где хотя-бы одна из колонок а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
...
Рейтинг: 0 / 0
09.04.2013, 17:34
| Ответить | Цитировать | Написать  
25 сообщений из 43, страница 1 из 2
  1  все
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Исключение строк из запроса.
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=46&msg=38219093&tid=1707480]:
 0ms
get settings:
 11ms
get forum list:
 16ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 141ms
get topic data:
 13ms
get forum data:
 2ms
get page messages:
 68ms
get tp. blocked users:
 1ms
others: 15ms
total:  275ms
созд. / загр.: 275ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]