使用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]
当前回答
我知道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
其他回答
最简单的解决方案是使用子查询来查找与查询匹配的最小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 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)
我知道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
