postgreSQL根据表返回count有次要记录或零记录的记录

Question

我正试图恢复发生未成年人的合同数量，而不是在表中指定。为此，我有两个表，一个存储合同数据和hired_frequency，另一个存储合同发生次数，显示我们使用合同的次数。在这种情况下，我无法创建一个SQL(在Postgres)，返回所有的行，没有发生，其中有发生轻微的hired_frequency描述。

表1 -合同

ID  | hired_frequency
527 | 1
528 | 3
526 | 5
555 | 1
413 | 3 -- not show this
500 | 1 -- not show this

表2 -出现次数

itinerary_fixed_id | contract_id
2443 | 527
2443 | 528
123  | 526
123  |555

My select (not working)

SELECT
* 
FROM
contracts 
WHERE
hired_frequency > (
SELECT COUNT
( * ) 
FROM
occurrences 
WHERE
contracts.ID = occurrences.contract_id 
GROUP BY
contract_id 
)

我创建了一个小提琴来测试…http://sqlfiddle.com/!17/95cd2/1/0

Answer 1

试试

SELECT * 
FROM contracts a
WHERE ID NOT IN      ( SELECT b.contract_id 
FROM occurrences b )
OR hired_frequency > ( SELECT COUNT(c.contract_id) 
FROM occurrences c
WHERE a.ID = c.contract_id
GROUP BY c.contract_id);

Answer 2

当您加入合同时。ID =次数。Contract_id和它的分组将聚合并只获得1作为值，因为没有dup。你所需要的只是内连接

SELECT
* 
FROM
contracts 
WHERE
hired_frequency > (
SELECT COUNT
( * ) 
FROM
occurrences 
WHERE
contracts.ID = occurrences.contract_id 
)