Гость
Войти | Профиль | Очистить
Действия ...
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Исключение строк из запроса. / 25 сообщений из 43, страница 1 из 2
  1  все
09.04.2013, 15:04
    #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:53
    #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, 16:01
    #38219036
pio777
pio777
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Исключение строк из запроса.
Shakill,
Простите, что не сообщил, но есть некоторые ограничения. Запрос формирует приложение, я могу только в where добавить свой and, cte и временные таблицы тоже не получится использовать.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
09.04.2013, 16:03
    #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:08
    #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:11
    #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:13
    #38219062
Shakill
Shakill
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Исключение строк из запроса.
Glory,

у автора формулировка "хотя бы одна из колонок равняется", то есть может быть и несколько, вплоть до всех, насколько я понял
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
09.04.2013, 16:14
    #38219066
Glory
Glory
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Исключение строк из запроса.
Shakillавтора формулировка "хотя бы одна из колонок равняется", то есть может быть и несколько, вплоть до всех, насколько я понял
там 2 условия
"одна из колонок а1-а7 равняется определенному значению,
а все остальные 0"
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
09.04.2013, 16:15
    #38219070
Shakill
Shakill
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Исключение строк из запроса.
Glory, там "хотя бы одна"
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
09.04.2013, 16:17
    #38219075
Glory
Glory
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Исключение строк из запроса.
ShakillGlory, там "хотя бы одна"
И что неправильного ?
шесть 0-лей плюс одно любое значения должны равняться заданному значению
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
09.04.2013, 16:19
    #38219082
Shakill
Shakill
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Исключение строк из запроса.
GloryShakillGlory, там "хотя бы одна"
И что неправильного ?
шесть 0-лей плюс одно любое значения должны равняться заданному значению
пять нулевых и два искомых тоже подходят в эту формулировку. семь искомых и отсутствие нулевых - тоже
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
09.04.2013, 16:21
    #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:22
    #38219086
Glory
Glory
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Исключение строк из запроса.
Shakillпять нулевых и два искомых тоже подходят в эту формулировку. семь искомых и отсутствие нулевых - тоже
"где хотя-бы одна из колонок а1-а7 равняется определенному значению, а все остальные 0"
Это как при 2 искомых колонках "а все остальные 0" ?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
09.04.2013, 16:26
    #38219093
Shakill
Shakill
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Исключение строк из запроса.
GloryShakillпять нулевых и два искомых тоже подходят в эту формулировку. семь искомых и отсутствие нулевых - тоже
"где хотя-бы одна из колонок а1-а7 равняется определенному значению, а все остальные 0"
Это как при 2 искомых колонках "а все остальные 0" ?
ну я и говорю. если в строке в трех полях - единицы, а остальные четыре содержат ноль, то такую строку тоже надо исключить. посмотрите на фильтр у автора изначальный
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
09.04.2013, 16:31
    #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:33
    #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:38
    #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:40
    #38219128
angel_zar
angel_zar
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Исключение строк из запроса.
angel_zar,

Хотя, хрень, все 0ли попадают
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
09.04.2013, 16:53
    #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, 17:12
    #38219190
iap
iap
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Исключение строк из запроса.
pio777,

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

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

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

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

в стартовом посте, есть запрос ТС, результат которого, его устраивает,
его не устраивает скорость
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
09.04.2013, 17:26
    #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:32
    #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:34
    #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
| Ответить | Цитировать | Написать  
  1  все
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Исключение строк из запроса. / 25 сообщений из 43, страница 1 из 2
Целевая тема:
Создать новую тему:
Автор:
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
созд. / загр.: 516ms
Закрыть
start [/forum/topic.php?fid=46&tablet=1&tid=1707480]:
 0ms
get settings:
 9ms
get forum list:
 13ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 169ms
get topic data:
 13ms
get forum data:
 3ms
get page messages:
 65ms
get tp. blocked users:
 2ms
others: 236ms
total:  516ms
x
x
Закрыть


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