如何使用计数与区分ON

I have this query that already works, i need to write count query for this (need pagination), and i have issues using count with DISTINCT ON, anyone knows correct syntax and way to use it, i already googled but was not able to find any count queries with DISTINCT ON

我已经有了这个查询，我需要为它写count查询(需要分页)，而且我在使用count时遇到了问题，没有人知道正确的语法和使用方法，我已经在谷歌上搜索过，但是无法找到任何计数查询

As result i'm expecting total number of rows from this query that already works

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

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)

1 个解决方案

#1

If you want to count the number of rows, use a subquery:

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

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

If you have an aversion to subqueries, you can always do:

如果你讨厌子查询，你总是可以做到:

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

This returns the same value assuming that csd.team_contest_id is not NULL.

这将返回与csd相同的值。team_contest_id不是零。

#1