Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Выборка по групповым фильтрам (простой вопрос Знатокам SQL по обычному Select)  [new]
ВладимирЛ
Member

Откуда:
Сообщений: 69
Добрый день, Знатоки SQL!

Сейчас в интернете часто встречается использование групповых (или сгруппированных) фильтров.
Как по ним организовать выборку? Вот максимально упрощённый вариант T-SQL кода:

--Главная таблица
Declare @tbMain table
(idMain int,   
 p1 int,
 p2 int )

			 Insert into @tbMain (idMain, p1,p2)
				  values (1,    100, 2017),
						 (2,    100, 2017),
						 (3,    200, 2017),
						 (4,    200, 2018)


--Таблица Фильтр
Declare @tbFilter table
		(idFilter int,  
		 nameFilter nvarchar(10),
		 value int)

			 Insert into @tbFilter (idFilter, nameFilter, value)
				  values (10,   'Код',100),
						 (20,   'Код',200),
						 (30,   'Год',2017),
						 (40,   'Год',2018)

--Таблица для связи (многие ко многим) Главной Таблицы и Таблицы Фильтра
Declare @tbManyToMany table
		(id int  identity (1,1),
		 idMain int,
		 idFilter int)

			 Insert into @tbManyToMany (idMain, idFilter)
				  values (1, 10),  --Фильтр по коду
                         (2, 10),  --Фильтр по коду
                         (3, 20),  --Фильтр по коду
                         (4, 20),  --Фильтр по коду

                         (1, 30),  --Фильтр по Году
                         (2, 30),  --Фильтр по Году
                         (3, 30),  --Фильтр по Году
                         (4, 40)   --Фильтр по Году


--Задаем Фильтр:
--Код   -> Указываем 100 и 200
--Год   -> Указываем 2017
--Результат должен быть   <Select * From @tbMain...+ where ...???>:
			--1	100	2017
			--2	100	2017
			--3	200	2017



--Вопрос - Как составить запрос в один запрос? Решается ли задача в один запрос?
--Пробовал варианты, но увы-ссс:



Select M.* From @tbMain As M
           inner join @tbManyToMany as MM on M.idMain = MM.idMain
		      inner join @tbFilter F on MM.idFilter = F.idFilter
where 
            (F.nameFilter = 'Код' and  F.value in (100, 200) )
			or --??? and ???
			(F.nameFilter = 'Год' and  F.value in (2017) )


Сообщение было отредактировано: 8 фев 18, 15:17
8 фев 18, 14:10    [21176340]     Ответить | Цитировать Сообщить модератору
 Re: Выборка по групповым фильтрам (простой вопрос Знатокам SQL по обычному Select)  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20538
ВладимирЛ
Как составить запрос в один запрос? Решается ли задача в один запрос?

Схематично:
SELECT m.*
FROM main m
JOIN properties p
WHERE (p.type, p.value) IN ( ('type1', 'value1'), .. , ('typeN', 'valueN') )
GROUP BY m.*
HAVING COUNT(*) = N
8 фев 18, 14:44    [21176490]     Ответить | Цитировать Сообщить модератору
 Re: Выборка по групповым фильтрам (простой вопрос Знатокам SQL по обычному Select)  [new]
ВладимирЛ
Member

Откуда:
Сообщений: 69
Akina, Уточнение-вопрос:
Конструкция

WHERE (p.type, p.value) IN ( ('type1', 'value1'), .. , ('typeN', 'valueN') )

вроде как не из T-SQL?
В "транзакте" с левой стороны IN допускается только один параметр(?).
8 фев 18, 15:06    [21176618]     Ответить | Цитировать Сообщить модератору
 Re: Выборка по групповым фильтрам (простой вопрос Знатокам SQL по обычному Select)  [new]
iap
Member

Откуда: Москва
Сообщений: 46983
Akina
ВладимирЛ
Как составить запрос в один запрос? Решается ли задача в один запрос?

Схематично:
SELECT m.*
FROM main m
JOIN properties p
WHERE (p.type, p.value) IN ( ('type1', 'value1'), .. , ('typeN', 'valueN') )
GROUP BY m.*
HAVING COUNT(*) = N
В T-SQL так нельзя.
Однако, есть же EXISTS() и INTERSECT
8 фев 18, 15:06    [21176621]     Ответить | Цитировать Сообщить модератору
 Re: Выборка по групповым фильтрам (простой вопрос Знатокам SQL по обычному Select)  [new]
iap
Member

Откуда: Москва
Сообщений: 46983
iap
Однако, есть же EXISTS() и INTERSECT
Может, даже NOT EXISTS(SELECT ... EXCEPT SELECT ... EXCEPT SELECT .........)
8 фев 18, 15:08    [21176632]     Ответить | Цитировать Сообщить модератору
 Re: Выборка по групповым фильтрам (простой вопрос Знатокам SQL по обычному Select)  [new]
ВладимирЛ
Member

Откуда:
Сообщений: 69
iap,
Вот так сработало:
Select M.* From @tbMain As M
inner join @tbManyToMany as MM on M.idMain = MM.idMain
inner join @tbFilter F on MM.idFilter = F.idFilter
where
(F.nameFilter = 'Код' and F.value in (100, 200) )
INTERSECT
Select M.* From @tbMain As M
inner join @tbManyToMany as MM on M.idMain = MM.idMain
inner join @tbFilter F on MM.idFilter = F.idFilter
where

(F.nameFilter = 'Год' and F.value in (2017) )

Но здесь все-таки два SELECT, а как в одном?
8 фев 18, 15:16    [21176668]     Ответить | Цитировать Сообщить модератору
 Re: Выборка по групповым фильтрам (простой вопрос Знатокам SQL по обычному Select)  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20538
ВладимирЛ
Конструкция
WHERE (p.type, p.value) IN ( ('type1', 'value1'), .. , ('typeN', 'valueN') )

вроде как не из T-SQL?

Вам слово "схематично" ни о чём не говорит? и да, в T-SQL это развернётся в показанное Вами же
ВладимирЛ
where 
(F.nameFilter = 'Код' and  F.value in (100, 200) )
or --??? and ???
(F.nameFilter = 'Год' and  F.value in (2017) )
8 фев 18, 15:17    [21176677]     Ответить | Цитировать Сообщить модератору
 Re: Выборка по групповым фильтрам (простой вопрос Знатокам SQL по обычному Select)  [new]
iap
Member

Откуда: Москва
Сообщений: 46983
ВладимирЛ,

можно же переписать запрос Akina с помощью явного сравнения пар type и value и операторов AND и OR.
Длинное будет условие, зато SELECT - один!
8 фев 18, 15:19    [21176695]     Ответить | Цитировать Сообщить модератору
 Re: Выборка по групповым фильтрам (простой вопрос Знатокам SQL по обычному Select)  [new]
ВладимирЛ
Member

Откуда:
Сообщений: 69
Akina,
Да, спасибо за ответ. На "схематично" я, конечно, обратил внимание. Но если бы все было так просто, я бы не задавал вопрос.
Упрощаем запрос до одной таблицы:

Select M.* From @tbMain As M
where p1 in (100,200)
and p2 in (2017)

И вот так работает, поскольку условие наложено на два поля таблицы.
А в том, что я написал - условие накладывается на (схематично) одно поле, поэтому по or оно возвращает больше, чем надо, а по and вообще ничего не возвращает. То есть, опять же схематично - сначала должен отработать первый фильтр, а потом на его результат належится следующий фильтр. Как это сделать одним SELECTом я не знаю. Возможно, задача не решаема.






Там нюанс в том, что используется
8 фев 18, 15:30    [21176758]     Ответить | Цитировать Сообщить модератору
 Re: Выборка по групповым фильтрам (простой вопрос Знатокам SQL по обычному Select)  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20538
ВладимирЛ
по or оно возвращает больше, чем надо
То есть группировку и пост-отбор Вы в моей схеме не увидели? а именно они обеспечивают решение задачи.
8 фев 18, 15:41    [21176832]     Ответить | Цитировать Сообщить модератору
 Re: Выборка по групповым фильтрам (простой вопрос Знатокам SQL по обычному Select)  [new]
ВладимирЛ
Member

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

Ну вот запрос с группировкой:
Select M.idMain, M.p1, M.p2 From @tbMain As M
           inner join @tbManyToMany as MM on M.idMain = MM.idMain
		      inner join @tbFilter F on MM.idFilter = F.idFilter
where 
            (F.nameFilter = 'Код' and  F.value in (100, 200) )
			or --??? and ???
			(F.nameFilter = 'Год' and  F.value in (2017) )
group by  M.idMain, M.p1, M.p2



Возвращает:

1 100 2017
2 100 2017
3 200 2017
4 200 2018


А годик то указан 2017 ???? Вот в этом и заковыка.
8 фев 18, 15:51    [21176898]     Ответить | Цитировать Сообщить модератору
 Re: Выборка по групповым фильтрам (простой вопрос Знатокам SQL по обычному Select)  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20538
Нет, вот что Вы хотите, если у Вас в списке вывода - уникальный индекс???
8 фев 18, 15:52    [21176912]     Ответить | Цитировать Сообщить модератору
 Re: Выборка по групповым фильтрам (простой вопрос Знатокам SQL по обычному Select)  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20538
Да и HAVING я как-то не наблюдаю...
8 фев 18, 15:53    [21176916]     Ответить | Цитировать Сообщить модератору
 Re: Выборка по групповым фильтрам (простой вопрос Знатокам SQL по обычному Select)  [new]
ВладимирЛ
Member

Откуда:
Сообщений: 69
Akina,
Без "ключа" вот так:
100 2017
200 2017
200 2018

то есть 2018 все равно есть, поэтому "оно" не принципиально.
А что даёт это условие:

HAVING COUNT(*) = N

в частности я не совсем понимаю параметр N. (в T-SQL такого нет)
8 фев 18, 16:04    [21177003]     Ответить | Цитировать Сообщить модератору
 Re: Выборка по групповым фильтрам (простой вопрос Знатокам SQL по обычному Select)  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20538
ВладимирЛ
в частности я не совсем понимаю параметр N
Обрати внимание на условие отбора - там указаны наборы условий с номерами от 1 до N. Вот это N и должно быть в запросе.
Select M.p1, M.p2 
From @tbMain As M
inner join @tbManyToMany as MM on M.idMain = MM.idMain
inner join @tbFilter F on MM.idFilter = F.idFilter
where (F.nameFilter = 'Код' and  F.value in (100, 200) ) -- условие по первому параметру
   or
      (F.nameFilter = 'Год' and  F.value in (2017) ) -- условие по второму параметру
group by  M.p1, M.p2
HAVING COUNT(*) = 2 -- всего параметров - два
8 фев 18, 16:32    [21177223]     Ответить | Цитировать Сообщить модератору
 Re: Выборка по групповым фильтрам (простой вопрос Знатокам SQL по обычному Select)  [new]
ВладимирЛ
Member

Откуда:
Сообщений: 69
Akina, Спасибо за ответ, осмысливаю. Вообще то -
COUNT(*) - это количество записей по возвращаемым строкам группировки и как оно связано с номером условий? В общем пока непонятно, осмысливаю.
8 фев 18, 16:50    [21177307]     Ответить | Цитировать Сообщить модератору
 Re: Выборка по групповым фильтрам (простой вопрос Знатокам SQL по обычному Select)  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20538
ВладимирЛ
как оно связано с номером условий?
не с номером, а с их количеством. Тебе же надо, чтобы запись соответствовала ВСЕМ фильтрам? COUNT(*) тебе как раз и показывает, сколько фильтров из всех соответствуют.
8 фев 18, 16:56    [21177328]     Ответить | Цитировать Сообщить модератору
 Re: Выборка по групповым фильтрам (простой вопрос Знатокам SQL по обычному Select)  [new]
ВладимирЛ
Member

Откуда:
Сообщений: 69
Akina,
Вот запрос:

Select 
 M.p1, M.p2
 , COUNT(*) as count
  From @tbMain As M
           inner join @tbManyToMany as MM on M.idMain = MM.idMain
		      inner join @tbFilter F on MM.idFilter = F.idFilter
where 
            (F.value in (100, 200) )
		     or --??? and ???
			(F.value in (2017) )
group by  
 M.p1, M.p2
  -- HAVING COUNT(*) = 2



Вот его результат:

100 2017 4
200 2017 2
200 2018 1

Правый столбец - это и есть количество возвращаемых записей по группировке "M.p1, M.p2". С количеством условий вроде как никак не связано.
8 фев 18, 17:06    [21177372]     Ответить | Цитировать Сообщить модератору
 Re: Выборка по групповым фильтрам (простой вопрос Знатокам SQL по обычному Select)  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
select t1.*
from @tbMain t1
 inner join @tbFilter f1 on t1.p1 =f1.value
 inner join @tbFilter f2 on f2.value =t1.p2
where f1.nameFilter =N'Код'
  and f2.nameFilter = N'Год'
  and t1.p2 =2017
  and t1.p1 in (100,200)
  and exists (
      select 1
	  from @tbManyToMany
	  where t1.idMain =idMain
	    and ISNULL(f1.idFilter,f2.idFilter) = idFilter
  )
12 фев 18, 16:10    [21185864]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить