如何删除分页中的重复项 [英] How do I remove duplicates in paging

问题描述

table1 &表2:

table1 & table2:

table1 &表 2 http://aftabfarda.parsfile.com/1.png

SELECT     *
FROM         (SELECT DISTINCT dbo.tb1.ID, dbo.tb1.name, ROW_NUMBER() OVER (ORDER BY tb1.id DESC) AS row
FROM         dbo.tb1 INNER JOIN
                      dbo.tb2 ON dbo.tb1.ID = dbo.tb2.id_tb1) AS a
WHERE     row BETWEEN 1 AND 7
ORDER BY id DESC

结果:

结果... http://aftabfarda.parsfile.com/3.png

(id 11 重复 3 次)

(id 11 Repeated 3 times)

我怎样才能得到这个输出:

How can I have this output:

ID  name    row
--  ------  ---
11  user11  1
10  user10  2
9   user9   3
8   user8   4
7   user7   5
6   user6   6
5   user5   7

推荐答案

替代 @Andomar 的建议，您可以使用 DENSE_RANK 而不是 ROW_NUMBER 并首先对行进行排名(在子查询中)，然后 apply DISTINCT(在外部查询中):

Alternatively to @Andomar's suggestion, you could use DENSE_RANK instead of ROW_NUMBER and rank the rows first (in the subquery), then apply DISTINCT (in the outer query):

SELECT DISTINCT
  ID,
  name,
  row
FROM (
  SELECT
    t1.ID,
    t1.name,
    DENSE_RANK() OVER (ORDER BY t1.ID DESC) AS row
  FROM dbo.tb1 t1
    INNER JOIN dbo.tb2 t2 ON t1.ID = t2.id_tb1
) AS a
WHERE row BETWEEN 1 AND 7
ORDER BY ID DESC

相似，但不完全相同，虽然两者可能归结为相同的查询计划，但我不确定.我认为值得测试.

Similar, but not quite the same, although both might boil down to the same query plan, I'm just not sure. Worth testing, I think.

当然，您也可以尝试 semi-join 而不是适当的 join，形式为 IN 或 EXISTS，首先要防止重复:

And, of course, you could also try a semi-join instead of a proper join, in the form of either IN or EXISTS, to prevent duplicates in the first place:

SELECT
  ID,
  name,
  row
FROM (
  SELECT
    ID,
    name,
    ROW_NUMBER() OVER (ORDER BY ID DESC) AS row
  FROM dbo.tb1
  WHERE ID IN (SELECT id_tb1 FROM dbo.tb2)
  /* Or:
  WHERE EXISTS (
    SELECT *
    FROM dbo.tb2
    WHERE id_tb1 = dbo.tb1.ID
  )
  */
) AS a
WHERE row BETWEEN 1 AND 7
ORDER BY ID DESC

这篇关于如何删除分页中的重复项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！