ReSQL.ru
     
powered by simpleCommunicator - 2.0.29     © 2024 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Map
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Учу подзапросы и никак не могу понять в каком порядке составить данный запрос.
25 сообщений из 33, страница 1 из 2
  1  все
Учу подзапросы и никак не могу понять в каком порядке составить данный запрос.
    #40101502
Artem20012005
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Artem20012005
Гость
Задание:
Сформируйте запрос, выводящий фамилии
руководителей, у которых все подчиненные получают
больше $2500

Таблица выглядит примерно так:
Где Mgr это руководитель

Empno Ename Mgr Sal
------- ------- ---- ---
7698 King 7839 2850
7782 Clrk 7839 1500
7566 Jones 7698 2975
...
Рейтинг: 0 / 0
02.10.2021, 11:32
| Ответить | Цитировать | Написать  
Учу подзапросы и никак не могу понять в каком порядке составить данный запрос.
    #40101529
Фотография SY
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SY
Участник
Artem20012005,

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
SELECT  EMPNO,
        ENAME,
        MGR,
        SAL
  FROM  EMP
  WHERE EMPNO IN (
                  SELECT MGR
                    FROM  EMP
                    GROUP BY MGR
                    HAVING MIN(SAL) > 2500
                 )
/

     EMPNO ENAME             MGR        SAL
---------- ---------- ---------- ----------
      7566 JONES            7839       2975

SQL>



SY.
...
Рейтинг: 0 / 0
02.10.2021, 14:25
| Ответить | Цитировать | Написать  
Учу подзапросы и никак не могу понять в каком порядке составить данный запрос.
    #40101682
Фотография Stax
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Stax
Участник
SY,

у которых все подчиненные получают, а не только непосредственно

имхо, надо строить иерархию

.....
stax
...
Рейтинг: 0 / 0
04.10.2021, 08:55
| Ответить | Цитировать | Написать  
Учу подзапросы и никак не могу понять в каком порядке составить данный запрос.
    #40102899
exciter
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
exciter
Гость
Stax,

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
SELECT MANAGER FROM
(
SELECT LEVEL AS LVL,
       CONNECT_BY_ROOT ENAME AS MANAGER, 
       SAL
   FROM EMP
   CONNECT BY PRIOR EMPNO = MGR
) 
WHERE LVL > 1
GROUP BY MANAGER
HAVING MIN(SAL) > :SALARY
...
Рейтинг: 0 / 0
07.10.2021, 21:31
| Ответить | Цитировать | Написать  
Учу подзапросы и никак не могу понять в каком порядке составить данный запрос.
    #40102950
ArchiSQL
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ArchiSQL
Гость
exciter, приветствую :)

Попробуем средствами ansi sql, предполагаю, что у самого верхнего нода (руководителя) mgr is null

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
with
rec (mgr_id, empno, ename, job, sal, lvl)
as
(select mgr as mgr_id, empno, ename, job, sal, 1 lvl from emp 
 union all
 select r.mgr_id, e.empno, e.ename, e.job, e.sal, lvl + 1  
 from emp e
 inner join rec r on r.empno = e.mgr)

select r.mgr_id, e.ename, avg(r.sal) as avg_sal
from rec r
inner join emp e on e.empno = r.mgr_id
group by r.mgr_id, e.ename
having avg(r.sal) > 2500
...
Рейтинг: 0 / 0
08.10.2021, 09:34
| Ответить | Цитировать | Написать  
Учу подзапросы и никак не могу понять в каком порядке составить данный запрос.
    #40102976
ArchiSQL
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ArchiSQL
Гость
ArchiSQL
exciter, приветствую :)

Попробуем средствами ansi sql, предполагаю, что у самого верхнего нода (руководителя) mgr is null

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
with
rec (mgr_id, empno, ename, job, sal, lvl)
as
(select mgr as mgr_id, empno, ename, job, sal, 1 lvl from emp 
 union all
 select r.mgr_id, e.empno, e.ename, e.job, e.sal, lvl + 1  
 from emp e
 inner join rec r on r.empno = e.mgr)

select r.mgr_id, e.ename, avg(r.sal) as avg_sal
from rec r
inner join emp e on e.empno = r.mgr_id
group by r.mgr_id, e.ename
having avg(r.sal) > 2500



Почему то здесь нахожу среднюю всех подчиненных, хотя в задании минимальная, исправляюсь.

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
WITH
REC (MGR_ID, EMPNO, ENAME, JOB, SAL, LVL)
AS
  (SELECT MGR AS MGR_ID, EMPNO, ENAME, JOB, SAL, 1 LVL FROM EMP 
   UNION ALL
   SELECT R.MGR_ID, E.EMPNO, E.ENAME, E.JOB, E.SAL, LVL + 1  
   FROM EMP E
        INNER JOIN REC R ON R.EMPNO = E.MGR)

SELECT R.MGR_ID, E.ENAME, MIN(R.SAL) AS MIN_SAL
FROM REC R
     INNER JOIN EMP E ON E.EMPNO = R.MGR_ID
GROUP BY R.MGR_ID, E.ENAME
HAVING MIN(R.SAL) > 2500
...
Рейтинг: 0 / 0
08.10.2021, 10:31
| Ответить | Цитировать | Написать  
Учу подзапросы и никак не могу понять в каком порядке составить данный запрос.
    #40102981
Фотография Stax
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Stax
Участник
ArchiSQL,

имхо, перемудрили

можно банально в 22380976 заменить connect нa with

ps
и LVL > 1

.....
stax
...
Рейтинг: 0 / 0
08.10.2021, 10:53
| Ответить | Цитировать | Написать  
Учу подзапросы и никак не могу понять в каком порядке составить данный запрос.
    #40102982
SQL*Plus
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SQL*Plus
Участник
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
SELECT E.empno, E.ename, E.mgr, E.sal 
FROM emp E
WHERE empno IN (SELECT NVL(mgr, empno) FROM emp) -- список руководителей
  AND NOT EXISTS
     (SELECT * FROM emp X
      WHERE X.sal < 950 -- условие по минимальной зарплате 
      CONNECT BY PRIOR X.empno = X.mgr
      START WITH X.mgr = E.empno);

     EMPNO ENAME             MGR        SAL
---------- ---------- ---------- ----------
      7698 BLAKE            7839       2850
      7782 CLARK            7839       2450
      7788 SCOTT            7566       3000


Скрипты для создания (demobld.sql) и уничтожения (demodrop.sql) таблиц прилагаются.
...
Рейтинг: 0 / 0
08.10.2021, 11:00
| Ответить | Цитировать | Написать  
Учу подзапросы и никак не могу понять в каком порядке составить данный запрос.
    #40102987
SQL*Plus
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SQL*Plus
Участник
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
SELECT LPAD(' ', (LEVEL-1)*3)||level||' - ' || E.empno || '. ' || E.ename AS tree, E.sal, E.mgr 
FROM emp E
CONNECT BY PRIOR empno = mgr
START WITH mgr IS NULL;

TREE                             SAL        MGR
------------------------- ---------- ----------
1 - 7839. KING                  5000           
   2 - 7566. JONES              2975       7839
      3 - 7788. SCOTT           3000       7566
         4 - 7876. ADAMS        1100       7788
      3 - 7902. FORD            3000       7566
         4 - 7369. SMITH         800       7902
   2 - 7698. BLAKE              2850       7839
      3 - 7499. ALLEN           1600       7698
      3 - 7521. WARD            1250       7698
      3 - 7654. MARTIN          1250       7698
      3 - 7844. TURNER          1500       7698
      3 - 7900. JAMES            950       7698
   2 - 7782. CLARK              2450       7839
      3 - 7934. MILLER          1300       7782

14 rows selected.


Иерархия сотрудников (чтобы легче было проверять решения)
...
Рейтинг: 0 / 0
08.10.2021, 11:12
| Ответить | Цитировать | Написать  
Учу подзапросы и никак не могу понять в каком порядке составить данный запрос.
    #40102996
Фотография Stax
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Stax
Участник
SQL*Plus

Иерархия сотрудников (чтобы легче было проверять решения)


поставте скотту зп 500грн

.....
stax
...
Рейтинг: 0 / 0
08.10.2021, 11:35
| Ответить | Цитировать | Написать  
Учу подзапросы и никак не могу понять в каком порядке составить данный запрос.
    #40102999
ArchiSQL
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ArchiSQL
Гость
Stax
ArchiSQL,

ps
и LVL > 1

.....
stax


Имхо, что это условие лишнее, более того ошибочно, т.к. LVL = 1 имеют записи:
1. Самый высший нод, который мы отсекаем INNER JOIN EMP E ON E.EMPNO = R.MGR_ID
2. Те, у которых MGR_ID != EMPNO, их отсекать нельзя.

Stax
ArchiSQL,

имхо, перемудрили

можно банально в 22380976 заменить connect нa with


Вы не могли бы пояснить, что значит "connect нa with". Не совсем понял что Вы имели ввиду.
Решение exciter корректное (если уж не придираться к CONNECT_BY_ROOT ENAME, а не CONNECT_BY_ROOT EMPNO, что абсолютно несущественно для логики), красивое и лаконичное, просто поскольку он его уже опубликовал, захотелось обойтись чистым sql без оракловых операторов :)
...
Рейтинг: 0 / 0
08.10.2021, 11:49
| Ответить | Цитировать | Написать  
Учу подзапросы и никак не могу понять в каком порядке составить данный запрос.
    #40103002
ArchiSQL
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ArchiSQL
Гость
lvl вообще нужно убрать из запроса, чтобы не путало

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
WITH
REC (MGR_ID, EMPNO, ENAME, JOB, SAL)
AS
  (SELECT MGR AS MGR_ID, EMPNO, ENAME, JOB, SAL FROM EMP 
   UNION ALL
   SELECT R.MGR_ID, E.EMPNO, E.ENAME, E.JOB, E.SAL  
   FROM EMP E
        INNER JOIN REC R ON R.EMPNO = E.MGR)

SELECT R.MGR_ID, E.ENAME, MIN(R.SAL) AS MIN_SAL
FROM REC R
     INNER JOIN EMP E ON E.EMPNO = R.MGR_ID
GROUP BY R.MGR_ID, E.ENAME
HAVING MIN(R.SAL) > 2500
...
Рейтинг: 0 / 0
08.10.2021, 11:52
| Ответить | Цитировать | Написать  
Учу подзапросы и никак не могу понять в каком порядке составить данный запрос.
    #40103004
SQL*Plus
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SQL*Plus
Участник
Stax
SQL*Plus

Иерархия сотрудников (чтобы легче было проверять решения)


поставте скотту зп 500

И что?

Будет у него оклад 500, но у всех его подчиненных больше 950.
Так что всё нормально.
...
Рейтинг: 0 / 0
08.10.2021, 11:54
| Ответить | Цитировать | Написать  
Учу подзапросы и никак не могу понять в каком порядке составить данный запрос.
    #40103027
Фотография Stax
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Stax
Участник
SQL*Plus
Stax
пропущено...


поставте скотту зп 500

И что?

Будет у него оклад 500, но у всех его подчиненных больше 950.
Так что всё нормально.


будет EXISTS, и в результат не попадет

....
stax
...
Рейтинг: 0 / 0
08.10.2021, 12:57
| Ответить | Цитировать | Написать  
Учу подзапросы и никак не могу понять в каком порядке составить данный запрос.
    #40103032
Фотография Stax
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Stax
Участник
ArchiSQL

Вы не могли бы пояснить, что значит "connect нa with".


иерария 22380976 построена ораклячим connect by ...,
но ее можно и переписать через with + union all (Ваш вариант)

ps
я Ваш запрос не тестировал (лень данные набивать), проверте сами

напр

1) когда начальник получает меньше оговореного (у скотт зп 500грн)
2) когда все работники получают больше оговореного
и тд
....
stax
...
Рейтинг: 0 / 0
08.10.2021, 13:08
| Ответить | Цитировать | Написать  
Учу подзапросы и никак не могу понять в каком порядке составить данный запрос.
    #40103035
SQL*Plus
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SQL*Plus
Участник
Stax
SQL*Plus
пропущено...

И что?

Будет у него оклад 500, но у всех его подчиненных больше 950.
Так что всё нормально.


будет EXISTS, и в результат не попадет


Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
SQL> UPDATE emp SET sal = 500
  2  WHERE empno = 7788;

1 row updated.

SQL> 
SQL> SELECT E.empno, E.ename, E.mgr, E.sal 
  2  FROM emp E
  3  WHERE empno IN (SELECT NVL(mgr, empno) FROM emp)
  4    AND NOT EXISTS
  5       (SELECT * FROM emp X
  6        WHERE X.sal < 950 -- условие по минимальной зарплате 
  7        CONNECT BY PRIOR X.empno = X.mgr
  8        START WITH X.mgr = E.empno)
  9  ;

     EMPNO ENAME             MGR        SAL
---------- ---------- ---------- ----------
      7698 BLAKE            7839       2850
      7782 CLARK            7839       2450
      7788 SCOTT            7566        500


Всё нормально.
SCOTT попадает в результат.
...
Рейтинг: 0 / 0
08.10.2021, 13:11
| Ответить | Цитировать | Написать  
Учу подзапросы и никак не могу понять в каком порядке составить данный запрос.
    #40103037
Фотография Stax
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Stax
Участник
SQL*Plus

Всё нормально.
SCOTT попадает в результат.

понял, был неправ
не учел, что иерархия на уровень выше

....
stax
...
Рейтинг: 0 / 0
08.10.2021, 13:17
| Ответить | Цитировать | Написать  
Учу подзапросы и никак не могу понять в каком порядке составить данный запрос.
    #40103044
SQL*Plus
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SQL*Plus
Участник
Stax
ps
я Ваш запрос не тестировал (лень данные набивать), проверте сами


Для самых ленивых приложен скрипт для создания классической демо-схемы Oracle.
22381098
:-)
...
Рейтинг: 0 / 0
08.10.2021, 13:26
| Ответить | Цитировать | Написать  
Учу подзапросы и никак не могу понять в каком порядке составить данный запрос.
    #40103053
Фотография SY
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SY
Участник
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
SELECT  EMPNO,
        ENAME,
        MGR,
        SAL 
  FROM  EMP
  WHERE EMPNO IN (
                  SELECT  MGR
                    FROM  EMP
                 )
    AND  EMPNO NOT IN (
                       SELECT  NVL(MGR,EMPNO)
                         FROM  EMP
                         START WITH SAL < 950
                         CONNECT BY EMPNO = PRIOR MGR
                      )
/

     EMPNO ENAME             MGR        SAL
---------- ---------- ---------- ----------
      7782 CLARK            7839       2450
      7788 SCOTT            7566       3000
      7698 BLAKE            7839       2850

SQL>



SY.
...
Рейтинг: 0 / 0
08.10.2021, 14:02
| Ответить | Цитировать | Написать  
Учу подзапросы и никак не могу понять в каком порядке составить данный запрос.
    #40103069
Фотография Stax
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Stax
Участник
SY,

только кинг получает < xxx

ps
+nvl

.....
stax
...
Рейтинг: 0 / 0
08.10.2021, 14:39
| Ответить | Цитировать | Написать  
Учу подзапросы и никак не могу понять в каком порядке составить данный запрос.
    #40103074
SQL*Plus
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SQL*Plus
Участник
Stax
SY,

только кинг получает < xxx

ps
+nvl

[spoiler] А теперь, Федя, скажи Васе все, что ты ему сказал раньше, нормально - на гражданском языке
YouTube Video
YouTube Video
...
Рейтинг: 0 / 0
08.10.2021, 14:48
| Ответить | Цитировать | Написать  
Учу подзапросы и никак не могу понять в каком порядке составить данный запрос.
    #40103094
Фотография Stax
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Stax
Участник
[quote=SQL*Plus]
[spoiler] А теперь, Федя, скажи Васе все, что ты ему сказал раньше, нормально - на гражданском языке
YouTube Video
YouTube Video
...
Рейтинг: 0 / 0
08.10.2021, 15:39
| Ответить | Цитировать | Написать  
Учу подзапросы и никак не могу понять в каком порядке составить данный запрос.
    #40103100
SQL*Plus
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SQL*Plus
Участник
[quote=Stax] [quote=SQL*Plus]
[spoiler] А теперь, Федя, скажи Васе все, что ты ему сказал раньше, нормально - на гражданском языке
YouTube Video
YouTube Video
...
Рейтинг: 0 / 0
08.10.2021, 15:58
| Ответить | Цитировать | Написать  
Учу подзапросы и никак не могу понять в каком порядке составить данный запрос.
    #40103103
Фотография SY
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SY
Участник
Stax

Все на предприятии получают ЗП больше 950, кроме Кинга у которого ЗП 500грн


Понял. NULL надо превращать несуществующий EMPNO:

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
SQL> update emp set sal = 1000;

14 rows updated.

SQL> update emp set sal = 500 where ename = 'KING';

1 row updated.

SELECT  EMPNO,
        ENAME,
        MGR,
        SAL 
  FROM  EMP
  WHERE EMPNO IN (
                  SELECT  MGR
                    FROM  EMP
                 )
    AND  EMPNO NOT IN (
                       SELECT  NVL(MGR,-1)
                         FROM  EMP
                         START WITH SAL < 950
                         CONNECT BY EMPNO = PRIOR MGR
                      )
/

     EMPNO ENAME             MGR        SAL
---------- ---------- ---------- ----------
      7902 FORD             7566       1000
      7698 BLAKE            7839       1000
      7839 KING                         500
      7566 JONES            7839       1000
      7788 SCOTT            7566       1000
      7782 CLARK            7839       1000

6 rows selected.

SQL> rollback;

Rollback complete.

SQL>



SY.
...
Рейтинг: 0 / 0
08.10.2021, 16:02
| Ответить | Цитировать | Написать  
Учу подзапросы и никак не могу понять в каком порядке составить данный запрос.
    #40103107
Фотография SY
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SY
Участник
SQL*Plus

Попробуйте более внятно ещё раз.


Stax намекнул на куда делся президент :

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
SQL> update emp set sal = 1000;

14 rows updated.

SQL> update emp set sal = 500 where ename = 'KING';

1 row updated.

SQL> SELECT  EMPNO,
  2          ENAME,
  3          MGR,
  4          SAL
  5    FROM  EMP
  6    WHERE EMPNO IN (
  7                    SELECT  MGR
  8                      FROM  EMP
  9                   )
 10      AND  EMPNO NOT IN (
 11                         SELECT  NVL(MGR,EMPNO)
 12                           FROM  EMP
 13                           START WITH SAL < 950
 14                           CONNECT BY EMPNO = PRIOR MGR
 15                        )
 16  /

     EMPNO ENAME             MGR        SAL
---------- ---------- ---------- ----------
      7782 CLARK            7839       1000
      7788 SCOTT            7566       1000
      7698 BLAKE            7839       1000
      7566 JONES            7839       1000
      7902 FORD             7566       1000

SQL> rollback;

Rollback complete.

SQL>



SY.
P.S. Кстати весьма реальная ситуация (у нас во всяком случае) когда президент компании назначает себе оклад в $1.
...
Рейтинг: 0 / 0
08.10.2021, 16:09
| Ответить | Цитировать | Написать  
25 сообщений из 33, страница 1 из 2
  1  все
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Учу подзапросы и никак не могу понять в каком порядке составить данный запрос.
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru, Чат Кролика       Пашэ: Рисунки, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=52&startmsg=40101502&tid=1879838]:
 0ms
get settings:
 28ms
get forum list:
 16ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 61ms
get topic data:
 11ms
get forum data:
 2ms
get page messages:
 481ms
get tp. blocked users:
 1ms
others: 2541ms
total:  3149ms
созд. / загр.: 3149ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]