sql - 过滤使用子查询计算的值

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)

示例 fiddle :

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 中可能有一些警告)