Oracle-如何在除BLOB之外的所有字段上执行SELECT DISTINCT? [英] Oracle - How can I perform a SELECT DISTINCT on all fields except a BLOB?

问题描述

我正在尝试在Oracle中执行SELECT DISTINCT查询，如下所示:

I'm trying to perform a SELECT DISTINCT query in Oracle, like this :

SELECT 
    MOVIES.TITLE, CERTIFICATIONS.ID, PROJECTION.DAY, TIME_SLOTS.SLOT, 
    PROJECTION.REMAINING_SEATS, IMAGES.IMAGE 
FROM 
    [...]

它不起作用，因为"IMAGES.IMAGE"列是BLOB.我想将此字段从DISTINCT中排除(因为我不需要它是唯一的)，而且我也不知道如何.我也尝试了GROUP BY子句，但是如果我尝试对除BLOB之外的所有字段进行GROUP，Oracle将返回此错误:

It doesn't work because the column "IMAGES.IMAGE" is a BLOB. I would like to exclude this field from the DISTINCT (because I don't need it to be unique), and I don't know how. I also tried with a GROUP BY clause, but if I try to GROUP on all the fields except the BLOB, Oracle returns this error:

ORA-00979:不是GROUP BY表达式

ORA-00979: not a GROUP BY expression

如果我在GROUP BY子句中添加该字段，则Oracle返回此:

And if I add the field in the GROUP BY clause, then Oracle returns this :

ORA-00932:数据类型不一致:预期-获得BLOB

ORA-00932: inconsistent datatypes: expected - got BLOB

我该怎么办?

在此先感谢您的帮助！

推荐答案

SELECT DISTINCT MOVIES.TITLE, CERTIFICATIONS.ID, PROJECTION.DAY, TIME_SLOTS.SLOT, PROJECTION.REMAINING_SEATS
FROM [...]

Distinct应用于SELECT列表中的所有列.是的，您不能在GROUP BY，UNION，DISTINCT等中使用LOB，因为Oracle不知道如何比较不同的LOB

Distinct is applied to all columns from the SELECT list. And yes, you cannot use LOBs in GROUP BY, UNION, DISTINCT etc because Oracle doesn't know how to compare different LOBs

如果您也想检索BLOB，则可以尝试如下操作:

If you want to retrieve BLOB as well you may try something like this:

SELECT MOVIES.TITLE, CERTIFICATIONS.ID, 
       PROJECTION.DAY, TIME_SLOTS.SLOT, PROJECTION.REMAINING_SEATS, IMAGES.IMAGE
FROM (     
  SELECT MOVIES.TITLE, CERTIFICATIONS.ID, 
         PROJECTION.DAY, TIME_SLOTS.SLOT, PROJECTION.REMAINING_SEATS, IMAGES.IMAGE,
         row_number() over (partition by MOVIES.TITLE, CERTIFICATIONS.ID, PROJECTION.DAY, TIME_SLOTS.SLOT, PROJECTION.REMAINING_SEATS 
                            order by PROJECTION.DAY, TIME_SLOTS.SLOT) RW
  FROM [...]
) WHERE RW = 1;  

但是您应该了解您在寻找什么.例如，上面的查询对除BLOB列之外的所有列进行分组，按两列对它们进行排序，并为该组中的每一行分配一个行号.结果查询仅检索每个组中的第一行

But you should understand what are you looking for. For example, the query above group all the columns except a BLOB column, order them by some two columns and assign a row number to each row in the group. The resulting query retrieves only the first row in each group

这篇关于Oracle-如何在除BLOB之外的所有字段上执行SELECT DISTINCT?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！