不使用 with 子句重写 sql

2022-01-23 00:00:00 sql subquery mysql with-clause

我掉进了 SQL 兔子洞，请帮帮我我需要在没有 with 子句的情况下重写这个查询

I've fallen down the SQL rabit hole, please help me I need to rewrite this query without the with clause

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;

基本上，第一张表将所有基于部门的工资加起来.第二个表得到总和的平均值.我需要找到一种方法来挑选部门总数大于平均值的部门.

basically the first table adds up all salaries based on department. the second table gets an average of the sums. I need to find a way to pick out the departments that have a department total greater than the averages.

只要不使用 with 子句，它可以以任何方式编写.我一直在想——选择 sum(salary)/count(salary) 作为 dept_total_avg, dept_name来自导师其中 dept_total_avg > all(选择总和(工资)，来自导师)按部门名称分组；

It can be written any way as long as it doesn't use the with clause. I've been thinking about - select sum(salary)/count(salary) as dept_total_avg, dept_name from instructor where dept_total_avg > all (select sum(salary), from instructor) group by dept_name;

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

but it doesn't work and now my brain doesn't either. Please help.

推荐答案

你需要计算部门平均数，不能访问中间cte dept_total

You need to calculate the department average without access to the intermediate 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)，每个都有一个名称，以便以后可以引用.

In the original query there are 2 "common table expressions" (cte) and each one is given a name so that they can be referred to later.

其中第一个被命名为 dept_total

允许任何后续的 cte 以该名称重用该 cte.然而，传统的派生表"子查询无法实现 cte 的这种重用方面.因此，在最终查询中使用名称 dept_total 的地方，您必须用一种新方法来获得部门平均值.

Any following cte is allowed to reuse that cte by that name. However this reuse aspect of cte's is not possible with traditional "derived table" subqueries. Hence where the name dept_total is used in the final query you have to substitute a new way to arrive at the departmental average.

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

In case you are not aware, MySQL 8 is now available and it supports "common table expressions" (the with clause)

相关文章