SQL Server DISTINCT 分页与 ROW_NUMBER() 不不同

Question

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

）我正在寻找的是一个工作原理相同但没有重复行的查询，因为 num 是唯一的：

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 人员，所以我不习惯这些内部查询等等..

更新：是的，num 用于分页。

Answer 1

好像我最近发表的关于 ROW_NUMBER() 是 SELECT 什么DENSE_RANK() 是SELECT DISTINCT。您的 CTE 必须替换为：

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< 中的所有列/code> 和来自 BASE_PROXY_VIEW_WITH_TARGET，以重现与 DISTINCT 关键字相同的顺序。这将为每个重复记录集分配一个排名，这样 DISTINCT 将再次起作用。

在 参考博客文章，我还添加了一个 SQLFiddle 在一个更简单的示例中说明了这一点。

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

解释为什么DISTINCT在计算窗口函数后删除重复行可以在这篇文章中找到。

Answer 2

对此信心有限，因为我无法测试甚至编译，但这是我的想法的一般要点......

WITH t AS
(
   SELECT [insert your fields here], ROW_NUMBER() OVER (ORDER BY Date) AS num 
   FROM (
     SELECT DISTINCT *[insert your fields here]
     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)) as X
)
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