如何使用count with Distinct ON

Question

我有这个已经工作的查询，我需要为此编写计数查询(需要分页)，我有问题使用count与DISTINCT ON，任何人知道正确的语法和使用它的方法，我已经谷歌，但无法找到任何计数查询与DISTINCT ON

因此，我期望这个查询中已经工作的总行数

select DISTINCT on (csd.team_contest_id)
        c.id as contestId
            from contest as c
            left join company_sponsor_team as csd on csd.contest_id = c.id
            left join sponsor as s on s.id = c.sponsor_id
            left join team as d on d.id = c.team_id
            left join player as m on c.creator_id = m.id
            WHERE c.id = c.id 
            AND ((c.team_id is not null and c.sponsor_id is null and lower(d.name) LIKE LOWER(CONCAT('%TAN%')))
            OR (c.team_id is not null and c.sponsor_id is not null and lower(s.name) LIKE LOWER(CONCAT('%TAN%')))
            OR (c.team_id is null and lower(s.name) LIKE LOWER(CONCAT('%TAN%')))) AND (CURRENT_DATE < c.archive_date)

Answer 1

如果要计算行数，请使用子查询：

select count(*)
from ( <your query here> ) x;

如果对子查询有反感，可以随时这样做：

select count(distinct csd.team_contest_id)
from . . .  <the rest of your query here>;

假设csd.team_contest_id不是NULL，则返回相同的值。