选择一列 DISTINCT SQL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/251278/
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 one column DISTINCT SQL
提问by Organiccat
Added: Working with SQL Server 2000 and 2005, so has to work on both. Also, value_rk is not a number/integer (Error: Operand data type uniqueidentifier is invalid for min operator)
补充：使用 SQL Server 2000 和 2005，所以必须同时使用。此外，value_rk 不是数字/整数（错误：操作数数据类型 uniqueidentifier 对最小运算符无效）
Is there a way to do a single column "DISTINCT" match when I don't care about the other columns returned? Example:
当我不关心返回的其他列时，有没有办法进行单列“DISTINCT”匹配？例子：
**Table**
Value A, Value L, Value P
Value A, Value Q, Value Z
I need to return only one of these rows based on what is in the first one (Value A). I still need results from the second and third columns (the second should actually match all across the board anyway, but the third is a unique key, which I need at least one of).
我只需要根据第一行（值 A）中的内容返回这些行中的一行。我仍然需要第二列和第三列的结果（无论如何，第二列实际上应该完全匹配，但第三列是唯一键，我至少需要其中一个）。
Here's what I've got so far, although it doesn't work obviously:
这是我到目前为止所得到的，虽然它显然不起作用：
SELECT value, attribute_definition_id, value_rk
FROM attribute_values
WHERE value IN (
SELECT value, max(value_rk)
FROM attribute_values
)
ORDER BY attribute_definition_id
I'm working in ColdFusion so if there's a simple workaround in that I'm open to that as well. I'm trying to limit or "group by" the first column "value". value_rk is my big problem since every value is unique but I only need one.
我在 ColdFusion 工作，所以如果有一个简单的解决方法，我也愿意。我试图限制或“分组”第一列“值”。value_rk 是我的大问题，因为每个值都是唯一的，但我只需要一个。
NOTE: value_rk is not a number, hence this DOES NOT WORK
注意：value_rk 不是数字，因此这不起作用
UPDATE: I've got a working version, it's probably quite a bit slower than a pure SQL version, but honestly anything working at this point is better than nothing. It takes the results from the first query, does a second query except limiting it's results to one, and grabs a matching value_rk for the value that matches. Like so:
更新：我有一个工作版本，它可能比纯 SQL 版本慢很多，但老实说，此时任何工作都比没有好。它从第一个查询中获取结果，执行第二个查询，但将其结果限制为一个，并为匹配的值获取匹配的 value_rk。像这样：
<cfquery name="queryBaseValues" datasource="XXX" timeout="999">
SELECT DISTINCT value, attribute_definition_id
FROM attribute_values
ORDER BY attribute_definition_id
</cfquery>
<cfoutput query="queryBaseValues">
<cfquery name="queryRKValue" datasource="XXX">
SELECT TOP 1 value_rk
FROM attribute_values
WHERE value = '#queryBaseValues.value#'
</cfquery>
<cfset resourceKey = queryRKValue.value_rk>
...
So there you have it, selecting a single column distinctly in ColdFusion. Any pure SQL Server 2000/2005 suggestions are still very welcome :)
这样就可以了，在 ColdFusion 中明确地选择单个列。仍然非常欢迎任何纯 SQL Server 2000/2005 建议:)
采纳答案by walming
this might work:
这可能有效：
SELECT DISTINCT a.value, a.attribute_definition_id,
(SELECT TOP 1 value_rk FROM attribute_values WHERE value = a.value) as value_rk
FROM attribute_values as a
ORDER BY attribute_definition_id
.. not tested.
.. 未测试。
回答by Bill Karwin
SELECT a1.value, a1.attribute_definition_id, a1.value_rk
FROM attribute_values AS a1
LEFT OUTER JOIN attribute_values AS a2
ON (a1.value = a2.value AND a1.value_rk < a2.value_rk)
WHERE a2.value IS NULL
ORDER BY a1.attribute_definition_id;
In other words, find the row a1for which no row a2exists with the same valueand a greater value_rk.
换句话说，找到a1不a2存在具有相同value和更大的行的行value_rk。
回答by Patryk Kordylewski
This should work for PostgreSQL, i don't know which dbms you use.
这应该适用于 PostgreSQL，我不知道您使用的是哪个 dbms。
SELECT DISTINCT ON (value)
value,
attribute_definition_id,
value_rk
FROM
attribute_values
ORDER BY
value,
attribute_definition_id
回答by John Fiala
Okay, here's my assumptions:
好的，这是我的假设：
Standard SQL Server
标准 SQL 服务器
value_rk is not a numeric value, but value and attribute_definition_id arenumeric.
value_rk 不是数值，但 value 和 attribute_definition_id是数值。
SELECT value_rk, MIN(value) as value, MIN(attribute_definition_id) as attribute_definition_id
FROM attribute_values
GROUP BY value_rk
ORDER BY MIN(attribute_definition_id)
If one of those fields isn't numeric, then it'll require more thought - please let us know.
如果这些字段之一不是数字，则需要更多考虑 - 请告诉我们。
回答by gfrizzle
Is this what you're looking for?
这是你要找的吗？
SELECT value, attribute_definition_id, value_rk
FROM attribute_values av1
WHERE value_rk IN (
SELECT max(value_rk)
FROM attribute_values av2
WHERE av2.value = av1.value
)
ORDER BY attribute_definition_id
If value_rk is unique, this should work.
如果 value_rk 是唯一的，这应该可以工作。
回答by Dane
If you are open to using table variables, you could keep it all within a single database call like this:
如果您愿意使用表变量，则可以将其全部保存在单个数据库调用中，如下所示：
DECLARE @attribute_values TABLE (value int, attribute_definition_id int, value_rk uniqueidentifier)
INSERT INTO @attribute_values (value)
SELECT DISTINCT value FROM attribute_values
UPDATE @attribute_values
SET attribute_definition_id = av2.attribute_definition_id,
value_rk = av2.value_rk
FROM @attribute_values av1
INNER JOIN attribute_values av2 ON av1.value = av2.value
SELECT value, attribute_definition_id, value_rk FROM @attribute_values
Essentially you are creating a limited recordset with the table filled with unique values of 'value', and letting SQL Server fill in the gaps using just one of the matches from the main table.
从本质上讲，您正在创建一个有限的记录集，其中填充了唯一值“value”的表，并让 SQL Server 仅使用主表中的匹配项之一来填补空白。
Edited to add: This syntax works within cfquery just fine.
编辑添加：此语法在 cfquery 中工作得很好。
回答by Adam
I'm not sure if I entirely understand your set-up, but would something like this work:
我不确定我是否完全理解你的设置，但像这样的工作：
SELECT value, attribute_definition_id, value_rk
FROM attribute_values
GROUP BY value
ORDER BY attribute_definition_id;
Again, I'm not real sure which column it is you're trying to limit, or how you're wanting to limit it.
同样，我不确定您要限制哪个列，或者您想如何限制它。
回答by Chris Cudmore
SELECT value, attribute_definition_id, value_rk
FROM attribute_values
WHERE value, value_rk IN (
SELECT value, max(value_rk)
FROM attribute_values
GROUP BY value
)
ORDER BY attribute_definition_id
NOT TESTED!
未测试！
回答by user1133937
i think
我认为
SELECT DISTINCT a.value, a.attribute_definition_id,
(SELECT TOP 1 value_rk FROM attribute_values WHERE value = a.value) as value_rk
FROM attribute_values as a
ORDER BY attribute_definition_id
worked
工作过
回答by Corwin Joy
As noted by John Fiala, the canonical answer in SQL server is to use a group by clause when you want to perform a "distinct" operation over a subset of columns. Why is this the correct canonical answer? Well, you want to pull in columns that are not part of your "distinct" group. Exactly what rows do you want to pull in for these subsidiary columns? Using a group by clause and defining aggregate functions for these subsidiary columns makes your query well-behaved in the sense that you now know how these subsidiary columns are obtained. This article gives more details:
正如 John Fiala 所指出的，当您想要对列的子集执行“不同”操作时，SQL 服务器中的规范答案是使用 group by 子句。为什么这是正确的规范答案？好吧，您想加入不属于您的“独特”组的列。您到底要为这些辅助列拉入哪些行？使用 group by 子句并为这些附属列定义聚合函数使您的查询表现良好，因为您现在知道如何获得这些附属列。这篇文章提供了更多细节：
http://weblogs.sqlteam.com/jeffs/archive/2007/10/12/sql-distinct-group-by.aspx
http://weblogs.sqlteam.com/jeffs/archive/2007/10/12/sql-distinct-group-by.aspx
SELECT value_rk, MIN(value) as value,
MIN(attribute_definition_id) as attribute_definition_id
FROM attribute_values
GROUP BY value_rk
Also, it's worth noting that MIN and MAX work on text and several other data types that are not numeric values.
此外，值得注意的是 MIN 和 MAX 适用于文本和其他几种不是数值的数据类型。
