在一列上选择DISTINCT | 在一列上选择DISTINCT

本文介绍了在一列上选择DISTINCT的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

使用SQL Server，我有...

Using SQL Server, I have...

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？

This query isn't getting me there. How can I SELECT DISTINCT on just one column?

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

推荐答案

假设您正在使用SQL在Server 2005或更高版本中，您可以将CTE与ROW_NUMBER（）结合使用：

Assuming that you're on SQL Server 2005 or greater, you can use a CTE with ROW_NUMBER():

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

这篇关于在一列上选择DISTINCT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！