Блог

Главная - Вопросы по программированию - Запрос для фильтрации по количеству

Запрос для фильтрации по количеству

#sql #oracle

#sql #Oracle

Вопрос:

Учитывая эти таблицы:

 Employee(ssn, name, sex, address, salary, bdate, dno, superssn)
fk:superssn is ssn in Employee
fk:dno is dnumber in Department

Department(dnumber, dname, mgrssn, mgrstartdate)
fk:mgrssn is ssn in Employee

Dept_locations(dnumber, dlocation)
fk:dnumber is dnumber in Department

Project(pnumber, pname, plocation, dnum)
fk:dnum is dnumber in Department

Dependent(essn, dependent_name, sex, bdate, relationship)
fk: essn is ssn in Employee

Works_on(essn,pno,hours)
fk: essn is ssn in Employee; pno is pnumber in Project

Мне нужно повторно просмотреть название каждого отдела, а также название менеджера отдела для отделов, которые имеют два местоположения…

У меня пока есть этот запрос

 SELECT D.dname, D.mgrssn
FROM department D, dept_locations DL
WHERE D.dnumber = dl.dnumber
Group by D.dname, D.mgrssn

Теперь я получаю три не дублирующихся отдела. Как мне ограничить это для отделов, которые имеют два местоположения?

Комментарии:

1. «очень быстро, пожалуйста» = домашнее задание?

2. спасибо за работу по редактированию, лол, когда я посмотрел на это, я увидел, насколько это было ужасно >_<

3. Вам следует попробовать использовать GROUP BY и HAVING .

4. Подумайте о том, что ваш запрос вернет сейчас (без AND... , удалите это). Для каждой сети подразделений с 3 местоположениями он вернет 3 строки. Для каждого отдела с 1 местоположением — 1 строка. Итак, вам нужно «сгруппировать по» отделу, чтобы собрать все строки отдела в одну строку (и посчитать их, если вам нужно). Это обычный способ обработки данных, которые разбиты более чем на одну строку.

5. ооочень спасибо: D: D Я думаю, что могу получить это сейчас: D

Ответ №1:

 SELECT D.dname, D.mgrssn
FROM Department D, Dept_locations DL
WHERE D.dnumber = DL.dnumber
GROUP BY D.dname, D.mgrssn
HAVING COUNT(*) >= 2            --- for 2 or more locations

и его лучше использовать INNER JOIN (или просто JOIN ) вместо WHERE для объединения связанных таблиц. Обратите внимание на отличие от предыдущего запроса. Оба будут возвращать одинаковые результаты :

 SELECT D.dname, D.mgrssn
FROM Department D
    JOIN Dept_locations DL
        ON D.dnumber = DL.dnumber
GROUP BY D.dname, D.mgrssn
HAVING COUNT(*) >= 2

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

 SELECT D.dname
     , D.mgrssn
     , E.name
FROM Department D
    JOIN Employee E 
        ON E.ssn = D.mgrssn
    JOIN Dept_locations DL
        ON D.dnumber = DL.dnumber
GROUP BY D.dname
       , D.mgrssn
       , E.name
HAVING COUNT(*) >= 2

Комментарии:

1. @Joel: В каждом отделе работает ровно один менеджер. Смотрите определения таблиц.

2. ОТДЕЛ имеет mgrstartdate , так что с исторической точки зрения существует более одного менеджера, связанного с ним. Кроме того, синтаксис ВНУТРЕННЕГО СОЕДИНЕНИЯ ANSI-89 и ANSI-92 выдает один и тот же план запроса; просто предпочтительнее использовать синтаксис ANSI-92 для ВНЕШНИХ соединений.

3. @OMG: Договорились об объединениях. Однако это домашнее задание, так что это не вопрос лучшего плана или наилучшей практики, а игра в терминах учителя (которые должны совпадать с терминами наилучшей практики …)

4. @OMG: Что касается mgrstartdate, я предположил, что в каждом отделе хранится только один менеджер (текущий).

Ответ №2:

Ровно для двух местоположений:

 SELECT d.name AS dept_name,
       e.name AS mgr_name
  FROM DEPARTMENT d
  JOIN EMPLOYEE e ON e.ssn = d.mgrssn
 WHERE EXISTS (SELECT NULL
                 FROM DEPT_LOCATIONS dl
                WHERE dl.dnumber = d.dnumber
             GROUP BY dl.dnumber
               HAVING COUNT(*) = 2)

Для двух или более местоположений:

 SELECT d.name AS dept_name,
       e.name AS mgr_name
  FROM DEPARTMENT d
  JOIN EMPLOYEE e ON e.ssn = d.mgrssn
 WHERE EXISTS (SELECT NULL
                 FROM DEPT_LOCATIONS dl
                WHERE dl.dnumber = d.dnumber
             GROUP BY dl.dnumber
               HAVING COUNT(*) >= 2)

Имейте в виду

В DEPARTMENT таблице есть mgrstartdate , поэтому вам нужно свериться с ним — в двух приведенных мною примерах будут показаны все исторические менеджеры в дополнение к текущим менеджерам, потому что в mgrstartdate таблице нет фильтрации.

Ответ №3:

 select dname, name
from Department, Employee, Dept_locations
where mgrssn = ssn
and Department.dnumber = Dept_locations.dnumber

Комментарии:

1. у вас будет одна строка для каждого местоположения с дублирующимся отделом. если вам нужно объединить местоположения, используйте пользовательские функции, но я сомневаюсь, что ваш учитель рассказал вам, как их использовать. итак, это сработает.