SQL 在一列上选择 DISTINCT
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/966176/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SELECT DISTINCT on one column
提问by mmcglynn
Using SQL Server, I have...
使用 SQL Server，我有...
ID SKU PRODUCT
=======================
1 FOO-23 Orange
2 BAR-23 Orange
3 FOO-24 Apple
4 FOO-25 Orange
I want
我想要
1 FOO-23 Orange
3 FOO-24 Apple
This query isn't getting me there. How can I SELECT DISTINCT on just one column?
这个查询没有让我到那里。如何仅在一列上选择 DISTINCT？
SELECT
[ID],[SKU],[PRODUCT]
FROM [TestData]
WHERE ([PRODUCT] =
(SELECT DISTINCT [PRODUCT] FROM [TestData] WHERE ([SKU] LIKE 'FOO-%'))
ORDER BY [ID]
回答by Aaron Alton
Assuming that you're on SQL Server 2005 or greater, you can use a CTE with ROW_NUMBER():
假设您使用的是 SQL Server 2005 或更高版本，您可以将 CTE 与 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
回答by Jakob Egger
The simplest solution would be to use a subquery for finding the minimum ID matching your query. In the subquery you use GROUP BYinstead of DISTINCT:
最简单的解决方案是使用子查询来查找与您的查询匹配的最小 ID。在您使用的子查询中，GROUP BY而不是DISTINCT：
SELECT * FROM [TestData] WHERE [ID] IN (
SELECT MIN([ID]) FROM [TestData]
WHERE [SKU] LIKE 'FOO-%'
GROUP BY [PRODUCT]
)
回答by KM.
try this:
尝试这个：
SELECT
t.*
FROM TestData t
INNER JOIN (SELECT
MIN(ID) as MinID
FROM TestData
WHERE SKU LIKE 'FOO-%'
) dt ON t.ID=dt.MinID
EDIT
once the OP corrected his samle output (previously had only ONE result row, now has all shown), this is the correct query:
编辑
一旦 OP 更正了他的 samle 输出（以前只有一个结果行，现在都显示了），这是正确的查询：
declare @TestData table (ID int, sku char(6), product varchar(15))
insert into @TestData values (1 , 'FOO-23' ,'Orange')
insert into @TestData values (2 , 'BAR-23' ,'Orange')
insert into @TestData values (3 , 'FOO-24' ,'Apple')
insert into @TestData values (4 , 'FOO-25' ,'Orange')
--basically the same as @Aaron Alton's answer:
SELECT
dt.ID, dt.SKU, dt.Product
FROM (SELECT
ID, SKU, Product, ROW_NUMBER() OVER (PARTITION BY PRODUCT ORDER BY ID) AS RowID
FROM @TestData
WHERE SKU LIKE 'FOO-%'
) AS dt
WHERE dt.RowID=1
ORDER BY dt.ID
回答by KM.
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'
回答by Bartosz X
I know it was asked over 6 years ago, but knowledge is still knowledge. This is different solution than all above, as I had to run it under SQL Server 2000:
我知道 6 年前有人问过这个问题，但知识仍然是知识。这是与上述所有解决方案不同的解决方案，因为我必须在 SQL Server 2000 下运行它：
DECLARE @TestData TABLE([ID] int, [SKU] char(6), [Product] varchar(15))
INSERT INTO @TestData values (1 ,'FOO-23', 'Orange')
INSERT INTO @TestData values (2 ,'BAR-23', 'Orange')
INSERT INTO @TestData values (3 ,'FOO-24', 'Apple')
INSERT INTO @TestData values (4 ,'FOO-25', 'Orange')
SELECT DISTINCT [ID] = ( SELECT TOP 1 [ID] FROM @TestData Y WHERE Y.[Product] = X.[Product])
,[SKU]= ( SELECT TOP 1 [SKU] FROM @TestData Y WHERE Y.[Product] = X.[Product])
,[PRODUCT]
FROM @TestData X
回答by Ivan
Here is a version, basically the same as a couple of the other answers, but that you can copy paste into your SQL server Management Studio to test, (and without generating any unwanted tables), thanks to some inline values.
这是一个版本，与其他几个答案基本相同，但是由于一些内联值，您可以将粘贴复制到 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]
)
Result
结果
ID SKU PRODUCT
1 FOO-23 Orange
3 FOO-24 Apple
I have ignored the following ...
我忽略了以下...
WHERE ([SKU] LIKE 'FOO-%')
as its only part of the authors faulty code and not part of the question. It's unlikely to be helpful to people looking here.
作为作者错误代码的唯一部分，而不是问题的一部分。这对看这里的人不太可能有帮助。
回答by Anna Karthi
Try this:
尝试这个：
SELECT * FROM [TestData] WHERE Id IN(SELECT DISTINCT MIN(Id) FROM [TestData] GROUP BY Product)
SELECT * FROM [TestData] WHERE Id IN(SELECT DISTINCT MIN(Id) FROM [TestData] GROUP BY Product)
