您可以尝试这个:
SELECT COUNT(ID) AS USER_RC
, SUM(CASE WHEN BLOCKED=TRUE THEN 1 ELSE 0 END) AS BLOCKED_RC
, SUM(CASE WHEN BLOCKED=TRUE THEN 0 ELSE 1 END) AS UNBLOCKED_RC
FROM TX1;
或者如果您更喜欢(它只使用两个聚合函数):
SELECT A.*, USER_RC-BLOCKED_RC AS UNBLOCKED_RC
FROM (SELECT COUNT(ID) AS USER_RC, SUM(CASE WHEN BLOCKED=TRUE THEN 1 ELSE 0 END) AS BLOCKED_RC
FROM TX1) A
;
示例数据:
INSERT INTO TX1 VALUES (1,'aaa',FALSE);
INSERT INTO TX1 VALUES (2,'bbb',TRUE);
INSERT INTO TX1 VALUES (3,'ccc',TRUE);
INSERT INTO TX1 VALUES (4,'ddd',TRUE);
INSERT INTO TX1 VALUES (5,'eee',FALSE);
输出:
user_rc blocked_rc unblocked_rc
5 3 2