不区分ROW_NUMBER()的SQL Server DISTINCT分页 [英] SQL Server DISTINCT pagination with ROW_NUMBER() not distinct

问题描述

好，所以基本上我的DISTINCT通过使用ROW_NUMBER()变得无用，我需要避免这种情况，因为它会导致重复的结果(当然，除了唯一的数字！)

Ok, so basically my DISTINCT is rendered useless by using ROW_NUMBER(), I need to avoid this as it's causing duplicate results (apart from a unique num of course!)

所以我要寻找的是一个查询，该查询将保持相​​同，但由于num是唯一的，所以没有重复的行:

So what I'm looking for is a query that'll work the same but not have duplicate rows because num is unique:

WITH t AS
(
   SELECT DISTINCT *, ROW_NUMBER() OVER (ORDER BY Date) AS num 
   FROM Original_Import 
   LEFT JOIN eqcas.dbo.BASE_PROXY_VIEW_WITHTARGET ON ADName = Targetuser
   WHERE (BaseProxy = 'agmc' OR ADName = 'agmc')
      AND (Commited IS NULL OR Commited = 0)
)
SELECT DISTINCT ID, num, ADName, Description_User, Description_Amex, Amount, Date
FROM t 
WHERE (t.BaseProxy = 'agmc' OR t.ADName = 'agmc') 
   AND num BETWEEN 0 AND 20
   AND (Commited IS NULL OR Commited = 0)
ORDER BY Date

修复起来可能微不足道，但由于我不是SQL Server专家，所以我不习惯这些内部查询等.

It's probably fairly trivial to fix, but seeing as I'm not a SQL Server guy I'm not used to these inner queries etc..

更新:是的，num用于分页.

推荐答案

我最近关于

Seems like I'm two years late with my recent blog post about ROW_NUMBER() being to SELECT what DENSE_RANK() is to SELECT DISTINCT. Your CTE would have to be replaced by this:

WITH t AS
(
   SELECT DISTINCT *, DENSE_RANK() OVER (ORDER BY Date, ...) AS num 
   FROM Original_Import 
   LEFT JOIN eqcas.dbo.BASE_PROXY_VIEW_WITHTARGET ON ADName = Targetuser
   WHERE (BaseProxy = 'agmc' OR ADName = 'agmc')
      AND (Commited IS NULL OR Commited = 0)
)
SELECT ...

在上面的查询中，DENSE_RANK()的ORDER BY子句将需要列出Original_Import和BASE_PROXY_VIEW_WITH_TARGET的所有列，以重现与DISTINCT关键字相同的顺序.这样，每个重复的记录集将恰好分配一个等级，这样DISTINCT将再次起作用.

In the above query, the DENSE_RANK()'s ORDER BY clause will need to list all columns from Original_Import and from BASE_PROXY_VIEW_WITH_TARGET, to reproduce the same ordering as the DISTINCT keyword. That will assign exactly one rank per duplicate record set, such that DISTINCT will work again.

在 SQLFiddle 在一个更简单的示例中对此进行了说明.

In the referenced blog post, I have also included a link to a SQLFiddle illustrating this in a more trivial example.

SELECT DISTINCT
  v, 
  DENSE_RANK() OVER (w) row_number
FROM t
WINDOW w AS (ORDER BY v)
ORDER BY v, row_number

An explanation why DISTINCT removes duplicate rows after window functions having been calculated can be found in this post here.

这篇关于不区分ROW_NUMBER()的SQL Server DISTINCT分页的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！