SQL 聚合函数嵌套

Question

我正在使用 PostgreSQL 学习 SQL，遇到了有关嵌套聚合函数的问题。

我试图找到拥有最多属性(property)的私有(private)所有者的详细信息，其中我有两个关系，privateowner 和 propertyforrent 以及具有外键 ownwerno 的 propertyforrent。

我怀疑我的问题出在我试图嵌套聚合函数的地方，但我看不出解决它的办法。

注意:- 我正在使用的数据库在 propertyforrent 的属性 ownwerno 中有一个拼写错误，它应该是 ownerno。

我尝试使用的代码如下所示~:-

SELECT o.fname, o.lname, telno
FROM privateowner o
WHERE o.ownerno = (SELECT p.ownwerno
           FROM propertyforrent p
           HAVING COUNT(p.ownwerno) = MAX(COUNT(o.ownerno)));

它伴随的错误如下:-

ERROR:  column "p.ownwerno" must appear in the GROUP BY clause or be used in a
aggregate function
LINE 3: WHERE o.ownerno = (SELECT p.ownwerno
                                  ^


********** Error **********

ERROR: column "p.ownwerno" must appear in the GROUP BY clause or be used in an
aggregate function
SQL state: 42803
Character: 78

任何见解都会很棒。

Answer 1

PostgreSQL 9.1 架构设置:

create table privateowner(ownerno integer, fname text);
insert into privateowner(ownerno, fname) values (1,'Alice'), 
                                                (2,'Bob'), 
                                                (3,'Charlie');

create table propertyforrent(ownerno integer);
insert into propertyforrent(ownerno) values (1), (2), (2), (3), (3);

查询 1:

with w as ( select ownerno, count(*) as property_count 
            from propertyforrent 
            group by ownerno )
select * 
from privateowner
where ownerno in( select ownerno 
                  from w 
                  where property_count=( select property_count
                                         from w 
                                         order by 1 desc limit 1) )

结果:

| OWNERNO |   FNAME |
---------------------
|       2 |     Bob |
|       3 | Charlie |

查看此 on SQL Fiddle

---

受@araqnid 的回答 (+1) 的启发，这里是另一个带有窗口函数的变体:

查询:

select ownerno, fname
from( select ownerno, fname, rank() over (order by count(*) desc) rnk
      from privateowner join propertyforrent using(ownerno) 
      group by ownerno, fname ) z
where rnk=1

结果:

| OWNERNO |   FNAME |
---------------------
|       3 | Charlie |
|       2 |     Bob |

查看此 on SQL Fiddle