sql-server - 在一列上选择 Distinct 并消除 Select Distinct 中的空值？

Question

关注这个question我有...

    ID  SKU PRODUCT
    =======================
    1   FOO-23  Orange
    2   BAR-23  Orange
    3   FOO-24  Apple
    4   FOO-25  Orange
    5   FOO-25  null
    6   FOO-25  null

预期结果:

1   FOO-23  Orange
3   FOO-24  Apple
5   FOO-25  null
6   FOO-25  null

这个查询没有让我到达那里。如何仅在一列上 SELECT DISTINCT 并消除 SELECT DISTINCT 中的 null？

SELECT  *
FROM    (SELECT ID, SKU, Product,
                ROW_NUMBER() OVER (PARTITION BY PRODUCT ORDER BY ID) AS RowNumber
         FROM   MyTable
         WHERE  SKU LIKE 'FOO%') AS a
WHERE   a.RowNumber = 1

Answer 1

也许一种方法是将WITH TIES与条件PARTITION结合使用

示例

Declare @YourTable Table ([ID] int,[SKU] varchar(50),[PRODUCT] varchar(50))
Insert Into @YourTable Values 
 (1,'FOO-23','Orange')
,(2,'BAR-23','Orange')
,(3,'FOO-24','Apple')
,(4,'FOO-25','Orange')
,(5,'FOO-25',NULL)
,(6,'FOO-25',NULL)

Select top 1 with ties * 
 From @YourTable
 Where SKU Like 'FOO%'
 Order By Row_Number() over (Partition By IsNull(Product,NewID()) Order By ID)

返回

ID  SKU     PRODUCT
6   FOO-25  NULL
5   FOO-25  NULL
3   FOO-24  Apple
1   FOO-23  Orange