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

Откуда:
Сообщений: 3
Необходимо отобрать клиентов, у которых есть всего два продукта, отвечающие требованиям:
1. У клиента должен быть продукт №1 (product_id = 1), у которого есть дата архивации (archive is not null);
2. У клиента должен быть продукт №2 (product_id = 2), у которого нет даты архивации (archive is null).
У клиентов может сколько угодно продуктов, но необходимо найти только тех, у которых есть всего два определенных продукта.

Платформа: Oracle
Пример с тестовыми данными привел здесь: https://www.jdoodle.com/a/1wmE
Судя по ним, результатом запроса должны быть клиенты с ИД: 12, 16

Помогите, пожалуйста, составить запрос
17 сен 19, 10:16    [21972200]     Ответить | Цитировать Сообщить модератору
 Re: Помогите составить SQL запрос  [new]
1C Developer
Member

Откуда:
Сообщений: 41
Вы создали тему с вопросом по Oracle в разделе Ms Sql Server:)

А еще местные старожилы любят спрашивать как Вы пытались решить задачу и что у Вас не получилось (халявщики и лентяи не вызывают желание помочь).
17 сен 19, 10:37    [21972221]     Ответить | Цитировать Сообщить модератору
 Re: Помогите составить SQL запрос  [new]
iceKreem
Member

Откуда:
Сообщений: 3
1C Developer, да, я отправил сообщение модератору об этом :)

Вот моя попытка решить задачу, но здесь не хватает условия, которое бы фильтровала клиентов, у которых всего два продукта, вот как это сделать я не пойму...

SELECT c1.client_id FROM contracts c1
JOIN contracts c2 on c1.client_id = c2.client_id and c2.product_id =2 and c2.archive is null
WHERE c1.product_id=1 and c1.archive is not null
17 сен 19, 10:40    [21972227]     Ответить | Цитировать Сообщить модератору
 Re: Помогите составить SQL запрос  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 19442
SELECT client_id 
FROM contracts
FROUP BY client_id
HAVING SUM(CASE WHEN product_id=1 AND archive IS NOT NULL THEN 1 ELSE 0 END) > 0
   AND SUM(CASE WHEN product_id=2 AND archive IS NULL THEN 1 ELSE 0 END) > 0
   AND SUM(CASE WHEN product_id IN (1, 2) THEN 0 ELSE 1 END) = 0
17 сен 19, 10:54    [21972254]     Ответить | Цитировать Сообщить модератору
 Re: Помогите составить SQL запрос  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6612
iceKreem
1C Developer, да, я отправил сообщение модератору об этом :)

Вот моя попытка решить задачу, но здесь не хватает условия, которое бы фильтровала клиентов, у которых всего два продукта, вот как это сделать я не пойму...

SELECT c1.client_id FROM contracts c1
JOIN contracts c2 on c1.client_id = c2.client_id and c2.product_id =2 and c2.archive is null
WHERE c1.product_id=1 and c1.archive is not null


NOT EXISTS (SELECT 1 FROM contracts x WHERE x.client_id = c1.client_id AND x.product_id <> c1.product_id AND x.product_id <>c2.product_id)
17 сен 19, 11:00    [21972264]     Ответить | Цитировать Сообщить модератору
 Re: Помогите составить SQL запрос  [new]
1C Developer
Member

Откуда:
Сообщений: 41
Вот Вариант:

Сначала отбираем всех клиентов у которых продукт 1 не в архиве:

SELECT c1.client_id
FROM contracts c1
WHERE c1.product_id = 1 and c1.archive is not null


Затем фильтруем тех у кого обязательно есть второй продукт в архиве:

SELECT c1.client_id
FROM contracts c1
INNER JOIN contracts c2 on c1.client_id = c2.client_id and c2.product_id = 2 and c2.archive is null
WHERE c1.product_id = 1 and c1.archive is not null


Добавляем все остальные продукты:

SELECT c1.client_id
FROM contracts c1
INNER JOIN contracts c2 on c1.client_id = c2.client_id and c2.product_id = 2 and c2.archive is null
LEFT JOIN contracts c3 on c1.client_id = c3.client_id and c3.product_id != 2 and c3.product_id != 1
WHERE c1.product_id = 1 and c1.archive is not null


Затем группируем по клиенту и выставляем условия по одному продукту 1 и 2 категории + 0 продуктов других категорий:

SELECT c1.client_id
FROM contracts c1
INNER JOIN contracts c2 on c1.client_id = c2.client_id and c2.product_id = 2 and c2.archive is null
LEFT JOIN contracts c3 on c1.client_id = c3.client_id and c3.product_id != 2 and c3.product_id != 1
WHERE c1.product_id = 1 and c1.archive is not null
GROUP BY c1.client_id HAVING COUNT(c1.product_id) = 1 AND COUNT(c2.product_id) = 1 AND COUNT(c3.product_id) = 0


Запрос не оптимальный, но для демонстрации поиска решения подойдет:)
17 сен 19, 11:05    [21972272]     Ответить | Цитировать Сообщить модератору
 Re: Помогите составить SQL запрос  [new]
vikkiv
Member

Откуда: London
Сообщений: 2468
iceKreem
Платформа: Oracle


т.к. пока в теме по SQL Server - то ответ на T-SQL:
declare @t table (client_id int, product_id int, archive date)
insert into @t values(11, 1,'2018-08-08'),(11, 2, null),(11, 3, '2018-08-08'),(11, 4, null),(12, 1, '2018-08-08')
,(12, 2, null),(13, 1, null),(13, 2, '2018-08-08'),(13, 3, '2018-08-08'),(13, 4, '2018-08-08'),(15, 1, '2018-08-08')
,(15, 2, null),(15, 3, null),(15, 4, null),(16, 1, '2018-08-08'),(16, 2, null),(17, 1, null),(17, 2, '2018-08-08')
,(17, 3, null),(17, 4, '2018-08-08')
select client_id from @t group by client_id having count(distinct product_id)=2
intersect --можно и inner join
select client_id from @t where (product_id=1 and archive is not null) or (product_id=2 and archive is null) group by client_id
17 сен 19, 11:07    [21972274]     Ответить | Цитировать Сообщить модератору
 Re: Помогите составить SQL запрос  [new]
Tactical Nuclear Penguin
Member

Откуда: холодно тут
Сообщений: 2702
select c.client_id from contracts c
join contracts c1 on c.client_id=c1.client_id and c1.product_id=1 and c1.archive is not null
join contracts c2 on c.client_id=c2.client_id and c2.product_id=2 and c2.archive is null
group by c.client_id
having count(*)=2
17 сен 19, 11:26    [21972302]     Ответить | Цитировать Сообщить модератору
 Re: Помогите составить SQL запрос  [new]
iceKreem
Member

Откуда:
Сообщений: 3
Большое спасибо всем за Ваши варианты решения, очень помогли!
17 сен 19, 12:02    [21972374]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить