选择一列 DISTINCT SQL
补充:使用 SQL Server 2000 和 2005,因此必须同时工作。 另外,value_rk 不是数字/整数(错误:操作数数据类型 uniqueidentifier 对于 min 运算符无效)
当我不关心返回的其他列时,有没有办法进行单列“DISTINCT”匹配? 示例:
**Table**
Value A, Value L, Value P
Value A, Value Q, Value Z
我需要根据第一行(值 A)中的内容仅返回其中一行。 我仍然需要第二列和第三列的结果(无论如何,第二列实际上应该全面匹配,但第三列是一个唯一的键,我至少需要其中一个)。
这是我到目前为止所得到的,尽管它显然不起作用:
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
我正在 ColdFusion 中工作,所以如果有一个简单的解决方法,我也对此持开放态度。 我试图限制或“分组”第一列“值”。 value_rk 是我的大问题,因为每个值都是唯一的,但我只需要一个。
注意:value_rk 不是一个数字,因此这不起作用
更新:我有一个工作版本,它可能比纯 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>
...
现在您已经完成了,在 ColdFusion 中明确选择单个列。 任何纯粹的 SQL Server 2000/2005 建议仍然非常受欢迎:)
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)
Is there a way to do a single column "DISTINCT" match when I don't care about the other columns returned? Example:
**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).
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.
NOTE: value_rk is not a number, hence this DOES NOT WORK
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:
<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 :)
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(11)
这可能有效:
..未测试。
this might work:
.. not tested.
换句话说,查找不存在具有相同
value
和更大value_rk
的行a2
的行a1
。In other words, find the row
a1
for which no rowa2
exists with the samevalue
and a greatervalue_rk
.这应该适用于 PostgreSQL,我不知道你使用哪个 dbms。
PostgreSQL 文档
This should work for PostgreSQL, i don't know which dbms you use.
PostgreSQL Docs
这是您要找的吗?
如果 value_rk 是唯一的,那么这应该可行。
Is this what you're looking for?
If value_rk is unique, this should work.
好的,这是我的假设:
标准 SQL Server
value_rk 不是数值,但 value 和 attribute_definition_id 是数值。
如果其中一个字段不是数字,则需要更多考虑 - 请告诉我们。
Okay, here's my assumptions:
Standard SQL Server
value_rk is not a numeric value, but value and attribute_definition_id are numeric.
If one of those fields isn't numeric, then it'll require more thought - please let us know.
如果您愿意使用表变量,则可以将其全部保留在单个数据库调用中,如下所示:
本质上,您正在创建一个有限的记录集,其中表填充了“value”的唯一值,并让 SQL Server 使用以下命令填充空白:只是主表中的一场比赛。
编辑添加:此语法在 cfquery 中工作得很好。
If you are open to using table variables, you could keep it all within a single database call like this:
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.
Edited to add: This syntax works within cfquery just fine.
未经测试!
NOT TESTED!
我不确定我是否完全理解你的设置,但是像这样的东西会起作用吗:
同样,我不确定你想要限制哪一列,或者你想要如何限制它。
I'm not sure if I entirely understand your set-up, but would something like this work:
Again, I'm not real sure which column it is you're trying to limit, or how you're wanting to limit it.
没有我想要的那么优雅——它本质上就是你正在做的事情,只是用纯 SQL——但它可以工作并且可以全部用 SQL 完成。
您可以使用此方法添加 depVal2 和其他。
Less elegant than I would like---- it's essentially what you're doing, just in pure SQL--- but it works and can all be done in SQL.
You can add a depVal2 and others using this method.
我认为
有效
i think
worked
正如 John Fiala 所指出的,当您想要对列的子集执行“不同”操作时,SQL Server 中的规范答案是使用 group by 子句。 为什么这是正确的规范答案? 好吧,您想要拉入不属于您的“不同”组的列。 您到底想为这些辅助列拉入哪些行? 使用 group by 子句并为这些辅助列定义聚合函数可以使您的查询表现良好,因为您现在知道如何获取这些辅助列。 本文提供了更多详细信息:
http:// /weblogs.sqlteam.com/jeffs/archive/2007/10/12/sql-distinct-group-by.aspx
另外,值得注意的是 MIN 和 MAX 适用于文本和其他几种不适用的数据类型数值。
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:
http://weblogs.sqlteam.com/jeffs/archive/2007/10/12/sql-distinct-group-by.aspx
Also, it's worth noting that MIN and MAX work on text and several other data types that are not numeric values.