如何在分页之前获得全部不同行的计数?

SELECT

    someColumnA,
    someColumnB,

FROM someTable

[A COUPLE JOINS HERE]

[A NICE WHERE STATEMENT]

ORDER BY someColumnA DESC

Let's say this query returns 1000 results. When I apply a DISTINCT it will condense down to let's say 300.

假设这个查询返回1000个结果。当我应用一个不同的，它会冷凝，比如说300。

What I want to do is only return 10 results using paging to act a preivew to my users and I want to include a total count of the number of DISTINCT ROWS. If I add paging with a count over in the select it will return the number of rows BEFORE THE DISTINCT.

我想要做的只是返回10个结果，使用分页来对我的用户执行一个preivew，并且我想要包括所有不同行数的总数。如果我在select中添加带有计数的分页，它将返回不同的行之前的行数。

Here is the final query I have so far

这是我到目前为止的最后一个查询

SELECT DISTINCT

    count(*) OVER() as total,
    someColumnA,
    someColumnB,

FROM someTable

[A COUPLE JOINS HERE]

[A NICE WHERE STATEMENT]

ORDER BY someColumnA DESC

OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY

total = 1000 and not the 300 I want. Is there a simple way to accomplish this or will I have to resort to using a sub select to fetch all 300 distinct rows to then count that and fetch the first 10?

总数= 1000而不是我想要的300。是否有一种简单的方法来实现这个目标，或者我将不得不使用sub select来获取所有300个不同的行，然后对它们进行计数并获取前10行?

2 个解决方案

#1

You can try using group by instead of distinct

你可以用group by代替distinct

SELECT 
    count(*) OVER() as total,
    someColumnA,
    someColumnB,

FROM someTable

[A COUPLE JOINS HERE]

[A NICE WHERE STATEMENT]

GROUP BY someColumnA, someColumnB
ORDER BY someColumnA DESC

OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY

#2

You can use CTE

您可以使用CTE

;WITH CTE_Distinct
AS
(
   SELECT DISTINCT
      someColumnA,
      someColumnB
  FROM someTable
  [A COUPLE JOINS HERE]
  [A NICE WHERE STATEMENT]
)

SELECT
    count(*) OVER() as total, 
    someColumnA,
    someColumnB 
FROM CTE_Distinct
ORDER BY someColumnA DESC
OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY

Hope, It helps you.

希望,它可以帮助你。

#1