GROUP BY "School"."name"
HAVING count... > 0
      and count... > 0
ORDER BY "B" desc

此外，还可以创建列 A , B , C ，和 D 可通过将查询作为另一个查询的表表达式括起来进行操作 SELECT . 在这外面 SELECT 通过添加筛选 predicate 可以很容易地筛选出行：

WHERE "A" <> 0 and "B" <> 0 and "C" <> 0 and "D" <> 0

因此，您的查询可能如下所示：

SELECT
  *,
  count(*) over() as total_rows
from (
  SELECT "School"."name" AS "School",
        count(distinct "User"."id") filter (where "public"."user_tokens"."app_name" = 'A') AS "A",
        count(distinct "User"."id") filter (where "public"."user_tokens"."app_name" = 'B') AS "B",
        count(distinct "User"."id") filter (where "public"."user_tokens"."app_name" = 'C') AS "C",
        count(distinct "User"."id") filter (where "public"."user_tokens"."app_name" = 'D') AS "D"
  FROM "public"."user_tokens"
  LEFT JOIN "public"."users" "User" ON "public"."user_tokens"."user_id" = "User"."id"
  LEFT JOIN "public"."user_roles" "User_2" ON "public"."user_tokens"."user_id" = "User_2"."user_id"
  LEFT JOIN "public"."roles" "Role" ON "User_2"."role_id" = "Role"."id" LEFT JOIN "public"."schools" "School" ON "User_2"."school_id" = "School"."id"
  GROUP BY "School"."name"
) x
WHERE "A" <> 0 and "B" <> 0 and "C" <> 0 and "D" <> 0
ORDER BY "B" desc

如果您希望避免查询中的表达式冗余，那么这对于更复杂的表达式非常有用。

你有用处吗 having 从句并重复表达

having count(distinct "User"."id") filter (where "public"."user_tokens"."app_name" = 'A') > 0 and
       count(distinct "User"."id") filter (where "public"."user_tokens"."app_name" = 'B') > 0 and
       count(distinct "User"."id") filter (where "public"."user_tokens"."app_name" = 'C') > 0 and
       count(distinct "User"."id") filter (where "public"."user_tokens"."app_name" = 'D')

尽管postgres允许在 GROUP BY ，它不允许在 HAVING 子句（在我看来，扩展sql标准的一种奇怪的方式）。
现在，您还可以简化和改进查询。首先，你需要火柴，所以用 inner join . 第二，使用表别名。我还去掉了双引号——这是个非常糟糕的主意：

SELECT s.name AS School,
        count(distinct u.id) filter (where ut.app_name = 'A') AS A,
        count(distinct u.id) filter (where ut.app_name = 'B') AS B,
        count(distinct u.id) filter (where ut.app_name = 'C') AS C,
        count(distinct u.id) filter (where ut.app_name = 'D') AS D
FROM "public"."user_tokens" ut JOIN
     "public"."users" u
      ON ut.user_id = u.id JOIN
      "public"."user_roles" ur
      ON ut.user_id = ur.user_id JOIN
      "public"."roles" r
      ON ur.role_id = r.id JOIN
      "public"."schools" s
      ON ur.school_id = s.id
GROUP BY s.name
having count(distinct u.id) filter (where ut.app_name = 'A') > 0 and
       count(distinct u.id) filter (where ut.app_name = 'B') > 0 and
       count(distinct u.id) filter (where ut.app_name = 'C') > 0 and
       count(distinct u.id) filter (where ut.app_name = 'D') 
ORDER BY "B" desc