如何忽略列中不包含数据的重复行

Question

我有一个包含重复行的表，但是某些重复行具有的列不包含同一列的数据。我怎样才能删除/只忽略那些列空白的行？在某些情况下：

Name  Employee# Location City 
----------------------------------------- 
BowerT  48999  NJ Foods 
BowerT  48999  NJ Foods Pearl 
BowerT  48999  NJ Foods Johns 
BowerT  48999  NJ Foods Johns 

我使用的是CTE删除重复，但是，如果第二，第三或第四行有我需要该列的数据，我失去了它，因为它们比列1时。

;With hrEmployee as 
(
    Select 
     *, 
     Row_Number() Over (Partition BY Employee_Number order by Employee_Number) As RowNumber 
    From 
     [dbo].[hrEmployee] 
    Where 
     Employee_Number = '48999'  
) 
Delete hrEmployees 
where RowNumber > 1 

我在想什么？

Answer 1

这里是整个例子的相关代码的变化是：

ROW_NUMBER() OVER (PARTITION BY Employee_Number ORDER BY 
      CASE WHEN ISNULL(City,'') = '' THEN 1 ELSE 0 END 
     ) as RowNumber 

什么，做仅仅是为了你的，你要保留，说什么如果纽约市为空或“”的结果（空白）使它最后。您可以通过在您的ORDER BY中指定不同顺序来排序您的结果。

DECLARE @Table AS TABLE (Name VARCHAR(10), Employee_Number INT, Location VARCHAR(20), City VARCHAR(20)) 
INSERT INTO @Table VALUES ('BowerT',48999,'NJ Foods',NULL) 
,('BowerT',48999,'NJ Foods','Pearl') 
,('BowerT',48999,'NJ Foods','Johns') 
,('BowerT',48999,'NJ Foods','Johns') 

SELECT * 
FROM 
    @Table 

;WITH hrEmployee AS (
     SELECT 
      * 
      ,ROW_NUMBER() OVER (PARTITION BY Employee_Number ORDER BY 
       CASE WHEN ISNULL(City,'') = '' THEN 1 ELSE 0 END 
      ) as RowNumber 
     FROM 
      @Table 
     where Employee_Number = '48999' 
) 

DELETE 
FROM 
    hrEmployee 
WHERE 
    RowNumber > 1 

SELECT * 
FROM 
    @Table