Надо составить запрос

Работа VB и СУБД (Access, MSSQL, MySQL, Oracle и пр.)
Правила форума
При создании новой темы не забывайте указывать используемую СУБД.
tyomitch
Пользователь #1352
Пользователь #1352
Аватара пользователя
 
Сообщения: 12822
Зарегистрирован: 20.10.2002 (Вс) 17:02
Откуда: חיפה

Надо составить запрос

Сообщение tyomitch » 24.10.2004 (Вс) 14:44

Задали мне домашку - составить запрос, который выводит:
Код: Выделить всё
все записи таблицы EMP m такие, что
  для каждой записи из EMP e, для которой (связка по DEPT.DEPTNO = e.DEPTNO) DEPT.LOC = 'AFRICA' - хотя бы по одной из таких записей для кадого e.DEPTNO было бы e.MGR = m.EMPNO;
  для любой записи из EMP e, для которой DEPT.LOC != 'AFRICA' - для этих записей e.MGR != m.EMPNO.

(Естественно, задание давалось в текстовой форме, это я его уже формализовал)
Помогите! Все мозги иссушил, а не выходит...


Идея у меня была такая, что
Код: Выделить всё
select * from EMP m where
  (select count(*) from EMP e join DEPT d on (d.DEPTNO = e.DEPTNO) where d.LOC = 'AFRICA' and e.MGR = m.EMPNO group by DEPTNO) = (select count(*) from DEPT where LOC = 'AFRICA')
and
  (select count(*) from EMP e join DEPT d on (d.DEPTNO = e.DEPTNO) where d.LOC != 'AFRICA' and e.MGR = m.EMPNO ) = 0

Чего-то всё жаловалось на синтаксис, я уже не помню на что именно, потому что много раз всё переставлял местами и всячески крутил-вертел, а дома той базы нету...


Кто-нибудь знает правильный ответ?
Изображение

GSerg
Шаман
Шаман
 
Сообщения: 14286
Зарегистрирован: 14.12.2002 (Сб) 5:25
Откуда: Магадан

Сообщение GSerg » 24.10.2004 (Вс) 14:48

Rainbow, наверное, и так бы справилась, но лично я не понял описания :oops:

Мож исходный текст дашь?
Как только вы переберёте все варианты решения и не найдёте нужного, тут же обнаружится решение, простое и очевидное для всех, кроме вас

tyomitch
Пользователь #1352
Пользователь #1352
Аватара пользователя
 
Сообщения: 12822
Зарегистрирован: 20.10.2002 (Вс) 17:02
Откуда: חיפה

Сообщение tyomitch » 24.10.2004 (Вс) 15:08

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

GSerg
Шаман
Шаман
 
Сообщения: 14286
Зарегистрирован: 14.12.2002 (Сб) 5:25
Откуда: Магадан

Сообщение GSerg » 24.10.2004 (Вс) 16:00

Ну а теперь названия таблиц и полей :)
Как только вы переберёте все варианты решения и не найдёте нужного, тут же обнаружится решение, простое и очевидное для всех, кроме вас

GSerg
Шаман
Шаман
 
Сообщения: 14286
Зарегистрирован: 14.12.2002 (Сб) 5:25
Откуда: Магадан

Сообщение GSerg » 24.10.2004 (Вс) 16:16

Так...
Вот это вот должно возвращать начальников, у которых есть подчинённые только в Африке...

SELECT Начальники.ФИО FROM Начальники INNER JOIN Подчинённые ON Начальники.ФИО = Подчинённые.ФИОНачальника WHERE Подчинённые.Страна = 'Африка'

Продолжим...
Как только вы переберёте все варианты решения и не найдёте нужного, тут же обнаружится решение, простое и очевидное для всех, кроме вас

GSerg
Шаман
Шаман
 
Сообщения: 14286
Зарегистрирован: 14.12.2002 (Сб) 5:25
Откуда: Магадан

Сообщение GSerg » 24.10.2004 (Вс) 16:56

Чувствую, что нуно выбрать начальников этим запросом, выбрать другим distinct-запросом те отделы, которые в Африке, и потом пересечь их :)
Команду IN я знаю, а вот "ALL IN"...

Учиться, учиться и учиться (c)

Rainbow, ты там где? :)
Как только вы переберёте все варианты решения и не найдёте нужного, тут же обнаружится решение, простое и очевидное для всех, кроме вас

tyomitch
Пользователь #1352
Пользователь #1352
Аватара пользователя
 
Сообщения: 12822
Зарегистрирован: 20.10.2002 (Вс) 17:02
Откуда: חיפה

Сообщение tyomitch » 24.10.2004 (Вс) 17:04

GSerg писал(а):Так...
Вот это вот должно возвращать начальников, у которых есть подчинённые только в Африке...

С этим-то как раз проблем нету.... Да в общем, по частям ни с чем проблем нету. Просто всё вместе настолько громоздко, что никак не стыкуется.

Я вот что думаю: это ведь задание для обычных студентов, а не хитро***ых извращенцев. Наверняка есть какая-то фишка, которую я не знаю и которая помогла бы избавиться от громоздкости трёх вложенных запросов?
Изображение

GSerg
Шаман
Шаман
 
Сообщения: 14286
Зарегистрирован: 14.12.2002 (Сб) 5:25
Откуда: Магадан

Сообщение GSerg » 24.10.2004 (Вс) 17:06

Есть операторы пересечения и объединения... Есть IN... Можно делать SELECT * FROM (SELECT *FROM ...)... Чего там ещё...
Как только вы переберёте все варианты решения и не найдёте нужного, тут же обнаружится решение, простое и очевидное для всех, кроме вас

tyomitch
Пользователь #1352
Пользователь #1352
Аватара пользователя
 
Сообщения: 12822
Зарегистрирован: 20.10.2002 (Вс) 17:02
Откуда: חיפה

Сообщение tyomitch » 24.10.2004 (Вс) 17:35

Вот, идея возникла - выбрать отделы в Африке, в которых у него нет подчинённых, и проверить на пустоту. Т.е.
Код: Выделить всё
select * from EMP m where not exists
  (select * from DEPT d where d.LOC = 'AFRICA' and not exists (select * from EMP e where d.DEPTNO = e.DEPTNO and e.MGR = m.EMPNO))
and not exists
  (select * from EMP e join DEPT d on (d.DEPTNO = e.DEPTNO) where d.LOC != 'AFRICA' and e.MGR = m.EMPNO)


Это правильно?
Изображение

GSerg
Шаман
Шаман
 
Сообщения: 14286
Зарегистрирован: 14.12.2002 (Сб) 5:25
Откуда: Магадан

Сообщение GSerg » 24.10.2004 (Вс) 17:41

Это правильно по логике :)

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

GSerg
Шаман
Шаман
 
Сообщения: 14286
Зарегистрирован: 14.12.2002 (Сб) 5:25
Откуда: Магадан

Сообщение GSerg » 24.10.2004 (Вс) 18:03

Нет, всё-таки дай список таблиц и полей :)
А то INNER JOIN непонятно где нужен :)
Как только вы переберёте все варианты решения и не найдёте нужного, тут же обнаружится решение, простое и очевидное для всех, кроме вас

tyomitch
Пользователь #1352
Пользователь #1352
Аватара пользователя
 
Сообщения: 12822
Зарегистрирован: 20.10.2002 (Вс) 17:02
Откуда: חיפה

Сообщение tyomitch » 24.10.2004 (Вс) 18:05

Скопировал прямо из задания:
Код: Выделить всё
DEPT (Список отделов)
Название колонки        Тип данных      Описание
DEPTNO                  NUMBER(2)       Номер отдела (PK)
DNAME                   VARCHAR2(14)    Наименование отдела
LOC                     VARCHAR2(13)    Местонахождение отдела
EMP (Список штатных сотрудников)
Название колонки        Тип данных      Описание
EMPNO                   NUMBER(4)       Номер сотрудника (PK)
ENAME                   VARCHAR2(10)    Фамилия сотрудника
JOB                     VARCHAR2(9)     Должность сотрудника
MGR                     NUMBER(4)       Номер начальника данного сотрудника (FK на колонку EMPNO в этой же таблице)
HIREDATE                DATE            Дата приема на работу
SAL                     NUMBER(7,2)     Размер заработной платы
COMM                    NUMBER(7,2)     Размер комиссионных (NULL, если сотрудник не получает комиссионные)
DEPTNO                  NUMBER(2)       Номер отдела, в котором работает данный сотрудник (FK на колонку DEPTNO в таблице DEPT)
Изображение

GSerg
Шаман
Шаман
 
Сообщения: 14286
Зарегистрирован: 14.12.2002 (Сб) 5:25
Откуда: Магадан

Сообщение GSerg » 24.10.2004 (Вс) 18:22

Чё-то всякий бред получается...
Давай Rainbow подождём :)
Как только вы переберёте все варианты решения и не найдёте нужного, тут же обнаружится решение, простое и очевидное для всех, кроме вас

Ennor
Конструктивный критик
Конструктивный критик
 
Сообщения: 2504
Зарегистрирован: 18.12.2001 (Вт) 3:58
Откуда: Калуга -> Москва

Сообщение Ennor » 24.10.2004 (Вс) 21:25

tyomitch писал(а):"Вывести всех начальников, у которых есть хотя бы один подчинённый в каждом отделе в Африке, и нет подчинённых в других отделах."

Это точно весь текст? Как-то слишком сложно получается. Далее, "и нет подчиненных в других отделах" - это как понимать, что у него в подчинении только африканские отделы, или что у него нет отделов, где нет африканцев? Уточни, Темыч...

Насчет диалекта, то ли я пропустил, то ли что еще, но версия SQL не указана. Я так понимаю, это Аксесс? Да, видимо, придется отложить до прихода Rainbow, а то я бы сейчас наваял на транзакте, что база в ступор бы вошла :) . Тем не менее, подумаю, конечно...

Кстати, а в Аксессе есть такая фишка, как HAVING? Здесь бы пригодилась...

GSerg
Шаман
Шаман
 
Сообщения: 14286
Зарегистрирован: 14.12.2002 (Сб) 5:25
Откуда: Магадан

Сообщение GSerg » 24.10.2004 (Вс) 21:35

Having - стандартная в SQL...
Как только вы переберёте все варианты решения и не найдёте нужного, тут же обнаружится решение, простое и очевидное для всех, кроме вас

tyomitch
Пользователь #1352
Пользователь #1352
Аватара пользователя
 
Сообщения: 12822
Зарегистрирован: 20.10.2002 (Вс) 17:02
Откуда: חיפה

Сообщение tyomitch » 24.10.2004 (Вс) 22:06

Ennor писал(а):Это точно весь текст?

Вообще, я по памяти набивал... Но вроде я склероза за собой не замечал :-?

Ennor писал(а):Как-то слишком сложно получается. Далее, "и нет подчиненных в других отделах" - это как понимать, что у него в подчинении только африканские отделы, или что у него нет отделов, где нет африканцев? Уточни, Темыч...

Местоположение есть только у отделов, не у работников.
Понимать надо "у него нет подчинённых в отделах вне Африки".

Ennor писал(а):Насчет диалекта, то ли я пропустил, то ли что еще, но версия SQL не указана. Я так понимаю, это Аксесс?

Это Оракл, но афаик там какой-то стандартный SQL, 92-ой, что ли... Есть в природе 92-ой? Если есть, значит он :-)

Ennor писал(а):Кстати, а в Аксессе есть такая фишка, как HAVING? Здесь бы пригодилась...

Есть. Как она может пригодиться?
Изображение

Sedge
Alternative Choice
Alternative Choice
Аватара пользователя
 
Сообщения: 1049
Зарегистрирован: 16.05.2002 (Чт) 18:23
Откуда: Somewhere-In-The-Net

Сообщение Sedge » 24.10.2004 (Вс) 23:38

С ораклом не работал. Но под MSSQL может сработать что-то похожее на:
Код: Выделить всё
SELECT ENAME FROM EMP AS EMP_MAIN WHERE
   (SELECT COUNT(*) FROM EMP INNER JOIN DEPT ON DEPT.DEPTNO=EMP.DEPTNO
      WHERE DEPT.LOC = 'Африка' AND EMP.MGR=EMP_MAIN.EMPNO) > 0
   AND
   (SELECT COUNT(*) FROM EMP INNER JOIN DEPT ON DEPT.DEPTNO=EMP.DEPTNO
      WHERE DEPT.LOC <> 'Африка' AND EMP.MGR=EMP_MAIN.EMPNO) = 0

tyomitch
Пользователь #1352
Пользователь #1352
Аватара пользователя
 
Сообщения: 12822
Зарегистрирован: 20.10.2002 (Вс) 17:02
Откуда: חיפה

Сообщение tyomitch » 25.10.2004 (Пн) 0:12

Не канает. У тебя выбираются начальники, у которых есть хотя бы один подчинённый в Африке. Мне надо, чтобы выбирались те, у которых есть по подчинённому в каждом отделе в Африке.
Изображение

Ennor
Конструктивный критик
Конструктивный критик
 
Сообщения: 2504
Зарегистрирован: 18.12.2001 (Вт) 3:58
Откуда: Калуга -> Москва

Сообщение Ennor » 25.10.2004 (Пн) 10:05

Уже понял - varchar2 есть только в Огакуле. И почему я сразу не заметил...

ЕМНИП, в Оракле нет джойнов. То есть они там есть, но не в стандартном MS-овском виде. Ладно, будем смотреть...

Rainbow
Человек-радуга
Человек-радуга
 
Сообщения: 543
Зарегистрирован: 13.05.2003 (Вт) 14:16

Сообщение Rainbow » 25.10.2004 (Пн) 10:22

Я решала проблему последовательно. Посмотрите, тот ли результат получился...

Предоставляю вам квери, а вы уж смотрите - то ли хранимую процедуру дейлайте, то ли склеивайте их - как-нибудь...

1) Выбираем африканские отделы (AFR_DEPT):
Код: Выделить всё
SELECT *
FROM DEPT
WHERE DName='AFR'

2) Выбираем всех начальников, у кого есть какие-нибудь сотрудники (Cheef_Dept)
Код: Выделить всё
SELECT DISTINCT [MGR], [DeptNo]
FROM EMP
WHERE (MGR<>0)

3) выбираем всех начальников и их отношение к африканским отделам (CHEEF_IN_AFR)
Код: Выделить всё
SELECT [AFR_DEPT].[DeptNo], [Cheef_Dept].[MGR]
FROM AFR_DEPT RIGHT JOIN Cheef_Dept ON [AFR_DEPT].[DeptNo]=[Cheef_Dept].[DeptNo]

4) теперь считаем, у кого сколько сотрудников в африканских отделах (AFR_COUNT)
Код: Выделить всё
SELECT [MGR], Count([DeptNo]) AS cnt
FROM CHEEF_IN_AFR
GROUP BY [MGR]

5) последний штрих - смотрим, у кого количество совпадает с количеством африканских отделов
Код: Выделить всё
SELECT MGR
FROM AFR_COUNT
WHERE cnt = (select count(DeptNo) from AFR_DEPT)


P.S. если что-то не заработает в самих кверях - значит, я перепутала названия (я свою структуру зачем-то сделала, а потом пыталась адаптировать к данной)
P.P.S. посмотрев на написанное есть подозрение, что можно оптимизировать (может, лишних запросов понаписала), но как - времени думать нет.
Учиться - значит открывать для себя то, что уже знаешь. <...> Учить - значит напоминать другим о том, что они знают это также хорошо, как и ты. <...> Лучше всего ты учишь тому, чему тебе самому больше всего надо научиться. (Р. Бах)

alibek
Большой Человек
Большой Человек
 
Сообщения: 14205
Зарегистрирован: 19.04.2002 (Пт) 11:40
Откуда: Russia

Сообщение alibek » 25.10.2004 (Пн) 11:09

Ну вот, пришла Радуга и на все ответила :)
Попробую придумать альтернативное решение и не смотреть на ее вариант :) .

Итак.
Вывести всех начальников (1)
у которых есть хотя бы один подчиненный (2)
в каждом отделе Африки (3)
и нет подчиненных в других отделах (4)

Т.е. если я правильно понял, у выбранного начальника должны быть подчиненные в _каждом_ отделе Африки? Или в любом из отделов Африки?

Будем считать, что первое.

Для проверок наличия/отсутствия я бы использовал EXISTS.

Получаем (предварительно):
Код: Выделить всё
select M.EMPNO
from EMP M
where 0=0
  and not exists
    (
      select E.EMPNO
      from EMP E, DEPT D
      where E.MGR = M.EMPNO
        and E.DEPTNO = D.DEPTNO
        and D.LOC <> 'AFRICA'
    )
  and exists
    (
      select
      from EMP E, DEPT D
      where E.MGR = M.EMPNO
        and E.DEPTNO = D.DEPTNO
        and D.LOC = 'AFRICA'
    )

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

Код: Выделить всё
select D.DEPTNO as Dpt                 -- номер отдела
     , count(E.EMPNO) as Cnt           -- сколько сотрудников в отделе (NULL не включается)
from DEPT D, EMP E
where E.MGR = M.EMPNO                  -- M.EMPNO - из вышестящего запроса
  and D.DEPTNO(+) = E.DEPTNO           -- выбрать ВСЕ отделы
  and D.LOC = 'AFRICA'                 -- все отделы в Африке
group by D.DEPTNO


(+) это равносильно LEFT JOIN

Код: Выделить всё
select M.EMPNO
from EMP M
where 0=0
  and not exists
    (
      select E.EMPNO
      from EMP E, DEPT D
      where E.MGR = M.EMPNO
        and E.DEPTNO = D.DEPTNO
        and D.LOC <> 'AFRICA'
    )
  and exists
    (
      select
      from EMP E, DEPT D
      where E.MGR = M.EMPNO
        and E.DEPTNO = D.DEPTNO
        and D.LOC = 'AFRICA'
    )
  and not exists
    (
      select DPT_CNT.DPT
      from
        (
          select D.DEPTNO as DPT
               , count(E.EMPNO) as CNT
          from DEPT D, EMP E
          where E.MGR = M.EMPNO
            and D.DEPTNO(+) = E.DEPTNO
            and D.LOC = 'AFRICA'
          group by D.DEPTNO
        ) DPT_CNT
      where DPT_CNT.CNT = 0
    )


Помоему так...
Что-то конструкция слишком сложная, как бы не накосячить :) А проверить не на чем.
tyomitch, как проверишь, напишешь?
Последний раз редактировалось alibek 25.10.2004 (Пн) 11:25, всего редактировалось 1 раз.
Lasciate ogni speranza, voi ch'entrate.

Konst_One
Член-корреспондент академии VBStreets
Член-корреспондент академии VBStreets
Аватара пользователя
 
Сообщения: 3041
Зарегистрирован: 09.04.2004 (Пт) 13:47
Откуда: Химки

Сообщение Konst_One » 25.10.2004 (Пн) 11:11

Если это Оракл, то можно посмотреть оператор INTERSECT, может все в одном запросе тогда получится

Ennor
Конструктивный критик
Конструктивный критик
 
Сообщения: 2504
Зарегистрирован: 18.12.2001 (Вт) 3:58
Откуда: Калуга -> Москва

Сообщение Ennor » 25.10.2004 (Пн) 11:21

Не, ну а че гадать-то? Проще тогда уж сразу сюда:

http://www.sql.ru/forum/actualtopics.aspx?bid=3

tyomitch
Пользователь #1352
Пользователь #1352
Аватара пользователя
 
Сообщения: 12822
Зарегистрирован: 20.10.2002 (Вс) 17:02
Откуда: חיפה

Сообщение tyomitch » 03.11.2004 (Ср) 9:05

alibek писал(а):tyomitch, как проверишь, напишешь?

tyomitch писал(а):Вот, идея возникла - выбрать отделы в Африке, в которых у него нет подчинённых, и проверить на пустоту. Т.е.
Код: Выделить всё
select * from EMP m where not exists
  (select * from DEPT d where d.LOC = 'AFRICA' and not exists (select * from EMP e where d.DEPTNO = e.DEPTNO and e.MGR = m.EMPNO))
and not exists
  (select * from EMP e join DEPT d on (d.DEPTNO = e.DEPTNO) where d.LOC != 'AFRICA' and e.MGR = m.EMPNO)

Я проверил этот запрос, и он работал. Без count(*) и без группировки :-)
Ваши с Rainbow не проверял - сорри :oops: Но чего-то они уж больно наворочанные...
Изображение


Вернуться в Базы данных

Кто сейчас на конференции

Сейчас этот форум просматривают: Google-бот и гости: 1

    TopList