在包含CLOB列的选择查询中消除重复的结果 [英] Eliminate duplicate results in a select query that contains CLOB column

问题描述

这是选择查询：

select orderid,ordernum,orderdate,orderxml from orders

查询返回具有相同ordernum的多行。我尝试使用DISTINCT和Group BY，但是orderxml是Clob列。
如何在查询中消除重复的ordernum？

The query returns multiple rows with same ordernum. I tried to use DISTINCT and Group BY but orderxml is a clob column. How can I eliminate duplicate ordernums in my query?

推荐答案

您可以使用解析函数来识别单个<每个 ordernum 的code> orderid -可能是最小值或最大值，但是其他功能是否可用，取决于您的需要-在子查询，然后进行筛选以仅获取具有已标识ID的行：

You could use an analytic function to identify a single orderid for each ordernum - probably either min or max, but other functions are available, it depends on what you need - in a subquery, and then filter to only get the rows with the identified IDs:

select orderid, ordernum, orderdate, orderxml
from (
  select orderid, ordernum, orderdate, orderxml,
    max(orderid) over (partition by ordernum) as maxorderid
  from orders
)
where orderid = maxorderid;

内联视图从表中获取所有列和行，但在结果中添加了额外的列在具有相同订单号的所有行中具有最大ID的ID。 （您可以在其中或课程中添加任何其他想要的过滤器。）

The inline view gets all the columns and rows from your table, but adds an additional column to its result that has the maximum ID across all rows with the same order number. (You can add any other filters you want in there, or course).

外部过滤器仅匹配与该最大值匹配的每个订单号的ID。

The outer filter then only matches the ID for each order number that matches that maximum value.

这假定 orderid 是唯一的-至少对于 ordernum 但大概是全球范围。您说 orderdate 对于 ordernum 总是相同的，但是您可以将其包含在分区中，如果可能的话，

This assumes that orderid is unique - at least for an ordernum but presumably globally. You said the orderdate is always the same for the ordernum but you could include that in the partition by if not, possibly modifying the analytic function used.

（然后研究如何以及为什么要获取重复项，并尝试停止它们；然后仔细地从表中删除重复项。 ）

(And then investigate how and why you are getting duplicates, and try to stop them; then remove the duplicates from your table - carefully...)

这篇关于在包含CLOB列的选择查询中消除重复的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！