使用空值与最大值设计重复数据消除sql语句

Question

我正在尝试雕刻一条SQL语句来描述一个表。

该表有三个键:用作业务键的key1、key2、key3。日期也在使用中。

规则是(假设在key1、key2、key3中有匹配)：

If all rows have dates, retain only MAX(date)
If one row has a date, and others are NULL, retain only row with date
If all rows has date = NULL, keep all rows.

我一直使用这段代码作为基础：

WITH CTE AS(
   SELECT [key1], [key2], [key3], [date],
       RN = ROW_NUMBER()OVER(PARTITION BY [key1], [key2], [key3], [date] ORDER BY [date] desc)
   FROM dbo.Table1
)
DELETE FROM CTE WHERE RN > 1

我没有受过怎样在sql语句上应用规则的教育。任何智慧都将不胜感激。

重复数据消除示例：

CASE 1: before dedupication:            
key1    key2    key3    date
1   A   1   null
1   A   1   null
1   A   1   null

after deduplication:            
key1    key2    key3    date
1   A   1   null
1   A   1   null
1   A   1   null

CASE 2: before dedupication:            
key1    key2    key3    date
1   A   1   1/1/2016
1   A   1   1/1/2016
1   A   1   1/1/2016

after deduplication:            
key1    key2    key3    date
1   A   1   1/1/2016

CASE 3: before dedupication:            
key1    key2    key3    date
1   A   1   1/1/2016
1   A   1   1/2/2016
1   A   1   1/3/2016

after deduplication:            
key1    key2    key3    date
1   A   1   1/3/2016

CASE 4: before deduplication            
1   A   1   1/1/2016
1   A   1   1/1/2016
1   A   1   null

after deduplication:            
key1    key2    key3    date
1   A   1   1/1/2016


CASE 5: before deduplication            
1   A   1   1/1/2016
1   A   1   1/2/2016
1   A   1   null

after deduplication:            
key1    key2    key3    date
1   A   1   1/2/2016

Answer 1

我相信，您就快成功了，只是需要一个位置来排除满足规则#3的行，使其不被删除。

 ;WITH CTE AS(
        SELECT [key1], [key2], [key3], [date],
           RN = ROW_NUMBER()OVER(PARTITION BY [key1], [key2], [key3] ORDER BY isnull([date], '19000101' desc)
        FROM dbo.Table1 t1
        WHERE EXISTS ( SELECT * 
                       FROM dbo.Table1 t2 
                       WHERE t1.key1=t2.key1 
                         and t1.key2=t2.key2 
                         and t1.key3=t2.key3 
                         and t1.[date] IS NOT NULL
                     )
    )
    DELETE FROM CTE WHERE RN > 1

Answer 2

WITH CTE AS(
   SELECT [key1], [key2], [key3], [date],
       RN = ROW_NUMBER() OVER (PARTITION BY [key1], [key2], [key3] ORDER BY [date] desc),
       mxD = max([date]) OVER (PARTITION BY [key1], [key2], [key3])
   FROM dbo.Table1
)
DELETE FROM CTE WHERE RN > 1 and mxD is not null