Каким будет SQL-запрос для следующего?

Таблицы:
сотрудник(имя сотрудника, улица, город)
работает(имя сотрудника, название компании, зарплата)
компания(название компании, город)
управляет(имя сотрудника, имя-менеджера)

Запрос: Предположим, что компании могут быть расположены в нескольких городах. Найдите все компании, расположенные в каждом городе, в котором находится «Маленькая банковская корпорация».

Я пробовал:
select cname from company c where (select city from company c2 where c2.cname='Small Bank Corporation' MINUS select city from company where cname=c.cname) is null;

и несколько подобных вариантов, но это вообще не работает. Моя идея состоит в том, чтобы сгенерировать набор A и набор B. Набор B — это все города, в которых находится компания Small bank. Скажем, Set B = {Мумбаи, Пуна}. Теперь я пытался создать набор A для каждой компании в таблице компаний. Скажем, для первого цикла cname = 'FBC', тогда Set A будет содержать все города, в которых расположен FBC.
Теперь, Set B - Set A, если эта часть станет нулевой, это означает, что A по крайней мере расположен во всех городах, в которых находится FBC. SBC и я пытались напечатать название компании A этого набора. Но по какой-то причине это не работает, и я не могу понять это.

Ошибка Oracle: однострочный подзапрос возвращает более одной строки.

Есть мысли по этому поводу? Спасибо


sql oracle oracle18c
person SpawN    schedule 07.02.2021    source источник

Ответы (3)


arrow_upward
2
arrow_downward

Прежде всего: модель данных немного неуместна, потому что таблица company не содержит ни одной строки для каждой компании. Лучшее имя было бы, например, company_branch_office. Мы должны помнить об этом при написании запроса, потому что запрос может читаться иначе, чем на самом деле. (Если можете, измените имя таблицы.)

В любом случае, получить компании, у которых есть филиалы в каждом городе, где находится Small Bank Corporation, тоже непростая задача. Самый простой способ, который я могу придумать, это:

select company_name
from company
where city in
(
  select city
  from company
  where company_name = 'Small Bank Corporation'
)
group by company_name
order by count(distinct city) desc
fetch first row with ties;

Как это работает?

  1. Я привожу все филиалы, расположенные в городах Small Bank Corporation. Таким образом, я получаю сами филиалы Small Bank Corporation плюс филиалы всех других компаний в тех же городах.
  2. Затем я агрегирую по компаниям, чтобы получить количество филиалов компаний в этих городах. Мне нужно COUNT(DISTINCT city) здесь, потому что у компании может быть два и более офиса в одном городе.
  3. Я упорядочиваю компании по количеству совпадающих городов и оставляю только те компании, у которых максимальное количество (то есть все города). Это относится, конечно же, к Small Bank Corporation и, возможно, к другим компаниям - компаниям, которые мы ищем.
person Thorsten Kettner    schedule 07.02.2021
comment
Мне потребовалось очень много времени, чтобы понять это, так как это сложно для меня; но это блестящее решение использовать предложение «IN», а затем фильтровать полученные результаты, используя orderby, count и fetch первую строку со связями. Благодарю вас ! Хотя мы не можем добавить where cname!='SBC' вместе с 'city in', так как это дает неверные результаты, поскольку результаты зависят от количества SBCcities. В этом случае мы можем просто создать представление как (результат — SBC)! - person SpawN; 07.02.2021
comment
@SpawnN . . . Это хороший и умный ответ. Однако это не обязательно сработает, если во внешнем запросе есть предложение where. Этот запрос всегда будет возвращать строки, в которых есть хотя бы один перекрывающийся город. Эта версия работает, потому что Small Bank Corporation гарантированно находится в наборе результатов, поэтому она будет вверху списка, поэтому есть по крайней мере одна строка со всеми городами. - person Gordon Linoff; 07.02.2021
comment
Я думаю, это то, что сказал SpawN. Вам нужно select * from (query) where company_name <> 'Small Bank Corporation', чтобы исключить SBC из результатов. - person Thorsten Kettner; 07.02.2021

arrow_upward
1
arrow_downward

Вы можете добиться этого, используя EXISTS:

SELECT * FROM
COMPANY
WHERE 
name != 'Small Bank Corporation'
AND EXISTS
(SELECT 1 FROM 
COMPANY as SBC WHERE 
SBC.name= 'Small Bank Corporation'
AND SBC.city = COMPANY.city)
person Neo    schedule 07.02.2021
comment
Боюсь, это дает мне неправильные результаты :(, я даже получаю название компании, которая не является надмножеством городов SBC. - person SpawN; 07.02.2021
comment
@SpawN: Да, Нео неправильно понял задание. Запрос дает вам все компании, у которых есть хотя бы один филиал в каком-либо городе SBC. - person Thorsten Kettner; 07.02.2021

arrow_upward
0
arrow_downward

Ответ Торстена подходит для заданного вами вопроса, но я думаю, что вопрос ненадежен. Если у вас есть фильтр во внешнем запросе, а Small Bank Corporation не включена, то запрос вернет компании с наибольшим количеством перекрывающихся городов, даже если ни один из них не перекроет все города.

Я бы сделал явную проверку, используя предложение HAVING:

select c.company_name
from company c
where exists (select 1
              from company c2
              where c2.city = c.city and
                    c2.name = 'Small Bank Corporation'
             )
group by c.company_name
having count(*) = (select count(*)
                   from company c2
                   where c2.name = 'Small Bank Corporation'
                  );

Обратите внимание, что здесь используется COUNT(*), при условии, что компании не имеют нескольких строк в городе. Если это возможно, используйте count(distinct city) в обоих местах.

person Gordon Linoff    schedule 07.02.2021