mysql - 重写不带with子句的sql

Question

我掉进了 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 子句即可。我一直在想——选择 sum(salary)/count(salary) 作为 dept_total_avg, dept_name来自教练其中 dept_total_avg > all(选择总和(工资)， 来自导师)按部门名称分组；

但它不起作用，现在我的大脑也不起作用。请帮忙。

Answer 1

您需要计算部门平均值，而无需访问中间 cte 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 的这种重用方面对于传统的“派生表”子查询来说是不可能的。因此，在最终查询中使用名称 dept_total 时，您必须替换一种新方法来得出部门平均值。

<小时/>

如果您不知道，MySQL 8 现已推出，它支持“通用表表达式”(with 子句)