使用SQL Server,我有…

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

我想要的

1   FOO-23  Orange
3   FOO-24  Apple

这个问题并没有让我明白。我如何在一列上选择DISTINCT ?

SELECT 
[ID],[SKU],[PRODUCT]
FROM [TestData] 
WHERE ([PRODUCT] = 
(SELECT DISTINCT [PRODUCT] FROM [TestData] WHERE ([SKU] LIKE 'FOO-%')) 
ORDER BY [ID]

当前回答

假设您使用的是SQL Server 2005或更高版本,您可以使用带有ROW_NUMBER()的CTE:

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

其他回答

试试这个:

SELECT * FROM [TestData] WHERE Id IN(FROM [TestData] GROUP BY Product)   

最简单的解决方案是使用子查询来查找与查询匹配的最小ID。在子查询中使用GROUP BY而不是DISTINCT:

SELECT * FROM [TestData] WHERE [ID] IN (
   SELECT MIN([ID]) FROM [TestData]
   WHERE [SKU] LIKE 'FOO-%'
   GROUP BY [PRODUCT]
)
SELECT min (id) AS 'ID', min(sku) AS 'SKU', Product
    FROM TestData
    WHERE sku LIKE 'FOO%' -- If you want only the sku that matchs with FOO%
    GROUP BY product 
    ORDER BY 'ID'

下面是一个版本,基本上与其他几个答案相同,但是由于一些内联值,您可以将其复制粘贴到SQL server Management Studio中进行测试(并且不会生成任何不需要的表)。

WITH [TestData]([ID],[SKU],[PRODUCT]) AS
(
    SELECT *
    FROM (
        VALUES
        (1,   'FOO-23',  'Orange'),
        (2,   'BAR-23',  'Orange'),
        (3,   'FOO-24',  'Apple'),
        (4,   'FOO-25',  'Orange')
    )
    AS [TestData]([ID],[SKU],[PRODUCT])
)

SELECT * FROM [TestData] WHERE [ID] IN 
(
    SELECT MIN([ID]) 
    FROM [TestData] 
    GROUP BY [PRODUCT]
)

结果

ID  SKU     PRODUCT
1   FOO-23  Orange
3   FOO-24  Apple

我忽略了以下几点…

WHERE ([SKU] LIKE 'FOO-%')

因为这只是作者错误代码的一部分,而不是问题的一部分。这对找这里的人没什么帮助。

假设您使用的是SQL Server 2005或更高版本,您可以使用带有ROW_NUMBER()的CTE:

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