将SQL查询从with子句转移到with子句

Question

考虑以下查询：

查找薪资总额大于所有部门薪资总额平均值的所有部门

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构造。

SELECT AVG(salary) GROUP BY dept_name;

Answer 1

在不支持CTEs的mySQL版本中，有几种方法可以做到这一点（我想这就是为什么他们让您重写查询以省略with）。另外，您正在按部门计算平均工资，但问题是要找出每个部门的平均总工资，然后返回高于该平均总工资的部分。

https://dbfiddle.uk/？rdbms=mysql_5.5&fiddle=fefea829cff3e20aea93634f9a4114d6

使用子查询：

SELECT dept_name
FROM (
  SELECT dept_name, sum(salary) as salaries
  FROM instructor GROUP BY dept_name
) d

WHERE salaries > (
  SELECT avg(salaries) as avgSalaries FROM (
    SELECT dept_name, sum(salary) as salaries
    FROM instructor GROUP BY dept_name
  ) z
)

SELECT dept_name
FROM (
  SELECT dept_name, sum(salary) as salaries
  FROM instructor GROUP BY dept_name
) d
CROSS JOIN (
  SELECT avg(salaries) as avgSalaries FROM (
    SELECT dept_name, sum(salary) as salaries
    FROM instructor GROUP BY dept_name
  ) z
) avgs
WHERE salaries > avgs.avgSalaries

SELECT avg(salaries) INTO @avg FROM (
  SELECT dept_name, sum(salary) as salaries
  FROM instructor GROUP BY dept_name
) z;

SELECT @avg;

SELECT dept_name
FROM instructor GROUP BY dept_name
HAVING sum(salary) > @avg;