Выберите идентификаторы, содержащие строки с NBR 1 и 2 в отдельных строках

#sql #sql-server #sql-server-2014

#sql #sql-сервер #sql-server-2014

Вопрос:

Я хочу написать SQL, который будет возвращать строки, в которых VENDOR_ID есть как строка с NBR_ID = 1 , так и другая строка с NBR_ID = 2 . В приведенном ниже примере SQL вернул бы данные для VENDOR_ID 91309A, строку с NBR_ID 1, а другую строку с NBR_ID 2.

 GROUP_ID     VENDOR_ID    NBR_ID
AUX          27           1
AUX          87188A       1
AUX          92481A       1
AUX          92482A       1
AUX          92527A       1
AUX          93309A       1
AUX          93309A       2
AUX          93328A       1

Я написал следующий SQL, но он специально не возвращает VENDOR_ID идентификаторы как для строки с NBR_ID, равным 1, так и для другой строки с NBR_ID, равным 2.

 SELECT GROUP_ID, VENDOR_ID, NBR_ID
FROM TEST_TABLE
WHERE NBR_ID IN (1,2)

Как я могу обновить, чтобы возвращать только те VENDOR_ID идентификаторы, которые содержат эти NBR_ID идентификаторы в разных строках?

Ответ №1:

используйте коррелированный подзапрос

 SELECT GROUP_ID,VENDOR_ID,NBR_ID
FROM TEST_TABLE a
WHERE exists(select 1 from TEST_TABLE b where a.GROUP_ID=b.group_id and 
a.VENDOR_ID=b.vendor_id and NBR_ID IN (1,2) 
having count(distinct nbr_id)=2) and NBR_ID IN (1,2)

1. Работает отлично! Спасибо!

2. Похоже, что это возвращает VENDOR_ID идентификаторы, у которых есть строки с NBR_ID значениями выше 2, т.Е. Он вернет строки 1-4, если a VENDOR_ID имеет 4 NBR_ID идентификатора, каждый в разных строках. Как я могу просто вернуть VENDOR_ID идентификаторы, у которых есть только NBR_ID строки 1 и 2?

3. Спасибо — Есть ли способ также по-прежнему выбирать столбцы GROUP_ID и NBR_ID , как у вас было раньше?

4. Потрясающе, работает, как ожидалось! Еще раз спасибо за доработку.

Ответ №2:

Вы можете использовать агрегирование:

 SELECT GROUP_ID, VENDOR_ID
FROM TEST_TABLE
WHERE NBR_ID IN (1, 2)
GROUP BY GROUP_ID, VENDOR_ID
HAVING COUNT(DISTINCT NBR_ID) = 2;

Это не возвращается NBR_ID по двум причинам. Во-первых, в вашем вопросе не указано, что вы этого хотите. Во-вторых, это довольно избыточно, потому что вы знаете, что список состоит как из 1, так и из 2.

1. Я добавил NBR_ID в Select и Group By , но затем он не возвращает никаких данных. Я все еще хочу отобразить этот столбец и вернуть обе строки (с NBR_ID 1 и NBR_ID 2, с уважением).

2. Также, похоже, это возвращает строки с более чем NBR_ID 1 и NBR_ID 2, это возвращает идентификаторы VENDOR_ID с NBR_ID, 3 или более. В идеале я хочу возвращать только те, у которых есть только 1 и 2.

Ответ №3:

использование существует

 select t1.* from TEST_TABLE t1
where exists ( select 1 from TEST_TABLE t2 where t1.VENDOR_ID=t2.VENDOR_ID
                                         and t1.GROUP_ID=t2.GROUP_ID
                                      and NBR_ID in (1,2)
                                      having count(distinct NBR_ID)=2)
and NBR_ID in (1,2)

1. Это неверно, это возвращает строки для каждого VENDOR_ID, у которых NBR_ID больше 2 (например, 3,4,5)

2. Это по-прежнему возвращает идентификаторы поставщиков, у которых есть строки с NBR_ID выше 2 (например, 3,4,5).

3. @Nick проверьте сейчас