同一个表上的SQL嵌套查询

I need to select every space a company has where its area is bigger than average per company. Here's my query:

我需要选择公司所拥有的每个空间，其面积大于每个公司的平均值。这是我的查询：

SELECT *
FROM Space outer
WHERE area >
( SELECT AVG (area)
FROM Space inner
WHERE outer.address = inner.address );

MySQL returns a syntax error starting at area>. I know I shouldn't have used address as primary key, and I can't use JOIN. It's just an assignment. Please, help

MySQL从area>开始返回语法错误。我知道我不应该使用地址作为主键，我不能使用JOIN。这只是一项任务。请帮忙

1 个解决方案

#1

First derive the average space across all records...

首先得出所有记录的平均空间......

(Select avg(area) mavg from space)

This returns a single value that we can cross join to all space records and then just check for area > average.

这将返回一个我们可以交叉连接到所有空间记录的值，然后只检查area> average。

Select * 
from Space
cross join (Select avg(area) mavg from space) Co
where area > Co.mavg

Since we know the result from the derived table/inline view will only 1 value every time, the cross join doesn't increase the number of rows being evaluated; and the rdbms only has to evaluate the average once.

由于我们知道派生表/内联视图的结果每次只有1个值，因此交叉连接不会增加被计算的行数;并且rdbms只需要评估一次平均值。

However this assumes you want average across all records and not just across a company. If it is by company... then something like...

但是，这假设您希望所有记录的平均值，而不仅仅是整个公司的平均值。如果它是由公司...然后像......

Select S.* 
from Space S
LEFT join (Select address, avg(area) mavg from space Group by address) Co
  on S.address= Co.address
where S.area > Co.mavg

This determines the average by company joins back to space on that company and then compares each space record for the company to the company's average.

这决定了公司加入该公司的空间平均值，然后将公司的每个空间记录与公司的平均值进行比较。

Since we don't know how you define company in terms of data, I just assumed a "address" field.

由于我们不知道您如何根据数据定义公司，因此我只假设了一个“地址”字段。

a different approach...

一种不同的方法......

Select S.* 
from space S
where S.area > (Select avg(area) from space)

However this assumes average across all companies

然而，这假设所有公司的平均值

要么

Select S.*
from space
where s.avg > (Select avg(area) from space S2 where S.Company = S2.company)

If this doesn't do it I would need to see the DDL of the table space (The structure columns, data types PK, FKs etc) and some sample data.

如果没有这样做，我需要查看表空间的DDL（结构列，数据类型PK，FK等）和一些示例数据。

unless address is the same for every company area.... there's must be some other criteria to relate a company to all the other company records (a name perhaps or a consistent key?)

除非每个公司区域的地址相同....必须有一些其他标准将公司与所有其他公司记录（一个名称或一致的密钥？）联系起来。

Personally I find the use of a correlated sub query in this case slow as it has to calculate the average for every record in Space. The cross join IMO would be more efficient.

我个人认为在这种情况下使用相关子查询很慢，因为它必须计算Space中每条记录的平均值。交叉加入IMO会更有效率。

#1