筛选使用子查询计算的值

Question

此查询有效：

select r.id, name, description, private, auth,
(select count (*) from message m where m.room = r.id) as messageCount
from room r left join room_auth a on a.room=r.id and a.player='11'
where private is false or auth is not null;

这个没有：

select r.id, name, description, private, auth,
(select count (*) from message m where m.room = r.id) as messageCount
from room r left join room_auth a on a.room=r.id and a.player='11'
where private is false or auth is not null or messageCount>1000;

我收到此错误：

ERREUR:  the « messageCount » column doesn't exit

如何干净有效地在messageCount上添加条件？或者更一般地说，如何实现预期的结果（由于room表中的列数和连接，我并不真正热衷于直接查询message表并按room分组的查询？

Answer 1

将子查询移动到 where 子句：

select sometable.id from sometable
where id in (select id from someothertable)

小提琴示例：

http://sqlfiddle.com/#!12/02c79/1

应用于您的查询：

select 
  r.id, 
  name, 
  description, 
  private, 
  auth, 
  (select count (*) from message m where m.room = r.id) as messageCount
from room r 
  left join room_auth a on a.room = r.id and a.player = '11'
where 
  private is false or 
  auth is not null or 
  (select count (*) from message m where m.room = r.id) > 1000;

（免责声明 - 不确定这是否会完美运行，因为我是 MSSQL 人，所以 Postgre 中可能会有一些警告）

Answer 2

select 中的东西是在from和where之后评估的（而且，据我所知，group by和having，或者至少在最新版本的 Postgres 之前是这样）。

您还需要在 where 子句中输入完整的子查询，因为引用该列时未定义该列：

select r.id, name, description, private, auth,
(select count (*) from message m where m.room = r.id) as messageCount
from room r left join room_auth a on a.room=r.id and a.player='11'
where private is false or auth is not null
   or (select count (*) from message m where m.room = r.id)>1000;

您还可以使用 join/group by/having 子句来执行相同的操作，同时避免使用相关的子查询，因为后者的性能会很糟糕。

最后，您可以 - 事实上，应该 - 使用触发器等方式维护房间内的计数。这样，您就可以在其上放置索引，并在private上也有索引，并使用 OR 位图索引扫描来获取行auth。