该查询有效:
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
表和group by的查询并不是很热情room
)?
将子查询移到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中可能会有一些警告)
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句