sql-server - 在一列上选择“不相同”，并在“选择不相同？”中消除空值。

遵循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并在null中消除SELECT DISTINCT？

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

最佳答案

在这里使用John Cappelletti的样本数据是另一种方法。您真正需要的就是将OR谓词添加到where子句中。

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  *
FROM
(
    SELECT ID
        , SKU
        , Product
        , ROW_NUMBER() OVER (PARTITION BY PRODUCT ORDER BY ID) AS RowNumber
    FROM   @YourTable
    WHERE  SKU LIKE 'FOO%'
) AS a
WHERE  a.RowNumber = 1
    OR a.PRODUCT IS NULL --This was the only part you were missing

关于sql-server - 在一列上选择“不相同”，并在“选择不相同？”中消除空值。，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/48283655/