如何使用row_number()筛选具有多个联接的重复项？

Question

我需要在其他列的基础上过滤副本。这是我正在尝试的代码。不幸的是，它不能工作，因为查询永远不会完成。

Select r1, r2, r3...,rb1,..., rc1,...  
From (
  Select *, ROW_NUMBER() OVER (PARTITION BY r1 order by r6 DESC) AS rownbr  
  From
    table1
    join table2 ON r2=rb1  
    Join table3 ON r6=rc1  
    Join table4 ON rx=rdx
) source  
Where  
  rownbr=1

有什么想法吗？

Answer 1

从语法上讲，您似乎得到了正确的查询。但是，在没有任何where子句的情况下连接4个表时，您可能会试图检索太多的数据。可以改进查询的几件简单的事情是：

• 仅指定子查询中需要的列。我敢肯定你不需要四张桌子上的所有东西。
• 找出是否可以更具体地处理要在where子句中获取和使用的数据。例如，您可能只需要t1.createdate > '01-01-2016'中的数据
• 如果这些仍然没有帮助，您将不得不开始查看索引。

    Select r1, r2, r3,rb1  
    From (
      Select * -- consider specifying only the columns that you need. Pretty sure you don't need every column from all 4 tables.
      , ROW_NUMBER() OVER (PARTITION BY r1 order by r6 DESC) AS rownbr  
      From table1 as t1
        join table2 as t2 ON t1.r2 = t2.rb1  
        Join table3 as t3 ON t3.r6 = t2.rc1  
        Join table4 as t4 ON t4.rx = t3.rdx
        where --may be something like date
        t1.createdate > '01-01-2016'
    ) source  
    Where  source.rownbr=1

Answer 2

抱歉还不能加评论..。

假设您的联接是正确的，为什么不直接使用group呢？

    Select r1, r2, r3...,rb1,..., rc1,...  
From (
  Select *
  From
    table1
    join table2 ON r2=rb1  
    Join table3 ON r6=rc1  
    Join table 4 ON rx=rdx
) source  
group by r1, r2, r3...,rb1,..., rc1,...  

Answer 3

创建视图的可能解决方案：

CREATE VIEW TABLAEX
AS
select lastname, convert(varchar, birthdate, 103) "birthday",
CONVERT(varchar, hiredate, 103) "hiredate", city,  
(DATEDIFF(dd, birthdate, hiredate) + 1)   
-(DATEDIFF(wk, birthdate, hiredate) * 2)  
-(CASE WHEN DATENAME(dw, birthdate) = 'Sunday' THEN 1 ELSE 0 END)  
-(CASE WHEN DATENAME(dw, hiredate) = 'Saturday' THEN 1 ELSE 0 END) "working days",  
 case when city like '%London%' then 'vecino'  
 when city like '%Seattle%' then 'lejos'  
 else 'null'  
 end as val,   
 case when (DATEDIFF(dd, birthdate, hiredate) + 1)  
 -(DATEDIFF(wk, birthdate, hiredate) * 2)  
 -(CASE WHEN DATENAME(dw, birthdate) = 'Sunday' THEN 1 ELSE 0 END)  
 -(CASE WHEN DATENAME(dw, hiredate) = 'Saturday' THEN 1 ELSE 0 END) > 8000    then 'viejo'  
 else 'joven'  
 end as edad,
 ROW_NUMBER() OVER (PARTITION BY city order by hiredate) AS rownbr
 from   
 HR.Employees;
 GO
 SELECT DISTINCT lastname, birthday, hiredate, "working days", edad, val
 from TABLAEX
 WHERE rownbr=1;
 GO 
 DROP VIEW TABLAEX;