Гость
Войти | Профиль | Очистить
Map
Действия ...
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Учу подзапросы и никак не могу понять в каком порядке составить данный запрос. / 25 сообщений из 33, страница 1 из 2
  1  все
02.10.2021, 11:32
    #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, 14:25
    #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
| Ответить | Цитировать | Написать  
04.10.2021, 08:55
    #40101682
Stax
Stax
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Учу подзапросы и никак не могу понять в каком порядке составить данный запрос.
SY,

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

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

.....
stax
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
07.10.2021, 21:31
    #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
| Ответить | Цитировать | Написать  
08.10.2021, 09:34
    #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, 10:31
    #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:53
    #40102981
Stax
Stax
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Учу подзапросы и никак не могу понять в каком порядке составить данный запрос.
ArchiSQL,

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

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

ps
и LVL > 1

.....
stax
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.10.2021, 11:00
    #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:12
    #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:35
    #40102996
Stax
Stax
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Учу подзапросы и никак не могу понять в каком порядке составить данный запрос.
SQL*Plus

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


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

.....
stax
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.10.2021, 11:49
    #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:52
    #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:54
    #40103004
SQL*Plus
SQL*Plus
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Учу подзапросы и никак не могу понять в каком порядке составить данный запрос.
Stax
SQL*Plus

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


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

И что?

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


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

И что?

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


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

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

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


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

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

напр

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

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

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

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


Для самых ленивых приложен скрипт для создания классической демо-схемы Oracle.
22381098
:-)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.10.2021, 14:02
    #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:39
    #40103069
Stax
Stax
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Учу подзапросы и никак не могу понять в каком порядке составить данный запрос.
SY,

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

ps
+nvl

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

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

ps
+nvl

[spoiler] А теперь, Федя, скажи Васе все, что ты ему сказал раньше, нормально - на гражданском языке
YouTube Video
YouTube Video
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.10.2021, 15:39
    #40103094
Stax
Stax
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Учу подзапросы и никак не могу понять в каком порядке составить данный запрос.
[quote=SQL*Plus]
[spoiler] А теперь, Федя, скажи Васе все, что ты ему сказал раньше, нормально - на гражданском языке
YouTube Video
YouTube Video
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.10.2021, 15:58
    #40103100
SQL*Plus
SQL*Plus
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Учу подзапросы и никак не могу понять в каком порядке составить данный запрос.
[quote=Stax] [quote=SQL*Plus]
[spoiler] А теперь, Федя, скажи Васе все, что ты ему сказал раньше, нормально - на гражданском языке
YouTube Video
YouTube Video
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.10.2021, 16:02
    #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:09
    #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
| Ответить | Цитировать | Написать  
  1  все
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Учу подзапросы и никак не могу понять в каком порядке составить данный запрос. / 25 сообщений из 33, страница 1 из 2
Целевая тема:
Создать новую тему:
Автор:
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
созд. / загр.: 598ms
Закрыть
start [/forum/topic.php?fid=52&tablet=1&tid=1879838]:
 0ms
get settings:
 16ms
get forum list:
 13ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 39ms
get topic data:
 9ms
get forum data:
 3ms
get page messages:
 495ms
get tp. blocked users:
 2ms
others: 13ms
total:  598ms
x
x
Закрыть


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