Переписать sql без предложения

Я упал в SQL-дыре, помогите, пожалуйста, мне нужно переписать этот запрос без предложения with

with dept_total(dept_name, value) as
     (select dept_name, sum(salary)
     from instructor
     group by dept_name),
dept_total_avg(value) as 
     (select avg(value)
     from dept_total)
select dept_name
from dept_total, dept_total_avg
where dept_total.value >= dept_total_avg.value;

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

Его можно написать любым способом, если он не использует предложение with. Я думал о - выберите сумму (зарплата)/ количество (зарплата) в качестве dept_total_avg, dept_name от инструктора, где dept_total_avg > все (выберите сумму (зарплата), из инструктора) группы по dept_name;

но это не работает, и теперь мой мозг тоже не работает. Пожалуйста помоги.

Источник

1 ответ

Вам необходимо рассчитать среднее значение по отделу без доступа к промежуточному dept_total

SELECT
    dept_total.dept_name, dept_total.value, cj.dept_av
FROM (
    SELECT
        dept_name, SUM(salary) value
    FROM instructor
    GROUP BY
        dept_name
    ) AS dept_total
CROSS JOIN (
        SELECT
            SUM(salary) / (COUNT(DISTINCT dept_name) * 1.0) dept_av
        FROM instructor
    ) cj
WHERE dept_total.value >= cj.dept_av
;

В исходном запросе есть 2 "общих табличных выражения" (cte), каждому из которых присваивается имя, чтобы к ним можно было обратиться позже.

Первому из них дается имя dept_total

Любой следующий cte может повторно использовать этот cte с этим именем. Однако этот аспект повторного использования cte's невозможен для традиционных подзапросов "производной таблицы". Отсюда и название dept_total используется в последнем запросе, вы должны заменить новый способ получить среднее значение по департаменту.

Если вы не знаете, MySQL 8 теперь доступен, и он поддерживает "общие табличные выражения" (with clause)

Источник
Другие вопросы по тегам