在PostgreSQL中使用select子查询时，如何获得总计数而不是从另一列上具有子句的列的单个计数?

Question

我有一个名为'billingpatencounter'的表，它看起来像下面的表-

tbody> <<tr>

mrno	patencounterid
N23	38
N23	39
N23	40

WHERE不能使用聚合。但是您可以使用子查询的结果进行比较。为了清晰起见，我加了两行'N22'。

CREATE TABLE billingpatencounter(
mrno TEXT,
patencounterid INT
);
INSERT INTO billingpatencounter(mrno, patencounterid) VALUES    
('N23', 38),
('N23', 39),
('N22', 38),
('N22', 39),
('N23', 40)
;
SELECT * FROM billingpatencounter;

输出

mrno | patencounterid
------+----------------
N23  |             38
N23  |             39
N22  |             38
N22  |             39
N23  |             40

按mrno列分组。由于patencounterid有不同的值，这将导致聚集在each row上。我删除了请求中的分组，因为这里不需要它。请求本身:

SELECT   COUNT(patencounterid)
FROM     billingpatencounter
WHERE    mrno = 'N23' AND patencounterid > (SELECT MIN(patencounterid)
FROM   billingpatencounter
WHERE  mrno = 'N23'
) 
;

输出

count
-------
2