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

Мне было интересно, возможно ли, чтобы внутренний запрос возвращал несколько результатов, которые затем запрашивались внешним запросом?

Вот воспроизводимый пример:

В этом примере я хотел бы знать имя и зарплату самого высокооплачиваемого сотрудника каждого отдела.

CREATE TABLE Worker (
    WORKER_ID INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    FIRST_NAME CHAR(25),
    LAST_NAME CHAR(25),
    SALARY INT(15),
    JOINING_DATE DATETIME,
    DEPARTMENT CHAR(25)
);

INSERT INTO Worker 
    (WORKER_ID, FIRST_NAME, LAST_NAME, SALARY, JOINING_DATE, DEPARTMENT) VALUES
        (001, 'Monika', 'Arora', 100000, '14-02-20 09.00.00', 'HR'),
        (002, 'Niharika', 'Verma', 80000, '14-06-11 09.00.00', 'Admin'),
        (003, 'Vishal', 'Singhal', 300000, '14-02-20 09.00.00', 'HR'),
        (004, 'Amitabh', 'Singh', 500000, '14-02-20 09.00.00', 'Admin'),
        (005, 'Vivek', 'Bhati', 500000, '14-06-11 09.00.00', 'Admin'),
        (006, 'Vipul', 'Diwan', 200000, '14-06-11 09.00.00', 'Account'),
        (007, 'Satish', 'Kumar', 75000, '14-01-20 09.00.00', 'Account'),
        (008, 'Geetika', 'Chauhan', 90000, '14-04-11 09.00.00', 'Admin');

Найти самую высокую зарплату каждого отдела достаточно просто:

SELECT MAX(SALARY), DEPARTMENT
FROM Worker
GROUP BY DEPARTMENT;

Но как добавить в результаты имена сотрудников, получающих эту зарплату?

Большое спасибо!


mysql sql subquery correlated-subquery
person seabass20    schedule 18.05.2020    source источник
comment
Вы должны хранить идентификатор отдела в таблице, внешний ключ к таблице отделов.   -  person jarlh    schedule 18.05.2020
comment
Почему вы специально упоминаете коррелированный подзапрос в заголовке?   -  person Gordon Linoff    schedule 18.05.2020
comment
@GordonLinoff разве это не то? С удовольствием поменяю, если нет.   -  person seabass20    schedule 18.05.2020
comment
@jarlh хорошо, значит, это будет проблема с настройкой таблицы (чего я сам не делал), а не вопрос о том, что я неправильно запрашиваю данные?   -  person seabass20    schedule 18.05.2020
comment
@морской окунь20 . . . Есть много способов приблизиться к этому. Коррелированный подзапрос — это только одно из решений.   -  person Gordon Linoff    schedule 18.05.2020

Ответы (1)


arrow_upward
0
arrow_downward

Вы можете использовать свой запрос с оператором IN:

SELECT * FROM Worker
WHERE (DEPARTMENT, SALARY) IN (
  SELECT DEPARTMENT, MAX(SALARY) 
  FROM Worker
  GROUP BY DEPARTMENT 
);

или если вы используете MySql 8.0+ с оконной функцией RANK():

SELECT w.* 
FROM (
  SELECT *, RANK() OVER (PARTITION BY DEPARTMENT ORDER BY SALARY DESC) rnk
  FROM Worker
) w
WHERE w.rnk = 1

См. демонстрацию.
Результаты:

> WORKER_ID | FIRST_NAME | LAST_NAME | SALARY | JOINING_DATE        | DEPARTMENT
> --------: | :--------- | :-------- | -----: | :------------------ | :---------
>         3 | Vishal     | Singhal   | 300000 | 2014-02-20 09:00:00 | HR        
>         4 | Amitabh    | Singh     | 500000 | 2014-02-20 09:00:00 | Admin     
>         5 | Vivek      | Bhati     | 500000 | 2014-06-11 09:00:00 | Admin     
>         6 | Vipul      | Diwan     | 200000 | 2014-06-11 09:00:00 | Account
person forpas    schedule 18.05.2020
comment
Привет @forpas. Я думаю, что версия MySQL 8.0 - это то, что я ищу, хотя другой ответ не совсем работает (я думаю). В первом ответе, возможно ли, чтобы сотрудник одного отдела (скажем, он второй по величине сотрудник в своем отделе) имел такую ​​же зарплату, как и самый высокооплачиваемый сотрудник другого отдела. В таком случае она все равно появится, верно? - person seabass20; 18.05.2020
comment
Оба ответа работают нормально. Ответ на ваш вопрос - нет, потому что сравнение проводится для как ОТДЕЛА, так и ЗАРПЛАТЫ. Таким образом, она является вторым самым высокооплачиваемым сотрудником в своем отделе, сравнение вернет FALSE, потому что ни DEPARTMENT, ни SALARY не будут совпадать. Конечно, оконные функции обеспечивают лучшую производительность, и если ваша версия MySql 8.0+, вам следует использовать второй запрос. - person forpas; 18.05.2020
comment
отличный. Первоначально у меня было это как мое решение, но я не понимал, что оно будет работать таким образом. Спасибо за помощь! - person seabass20; 18.05.2020