如何在Oracle的CLOB列上使用GROUP BY? [英] How to use GROUP BY on a CLOB column with Oracle?

问题描述

我正在尝试将GROUP BY函数与oracle中的MAX结合使用.我阅读了很多文档，试图弄清楚如何格式化Oracle的请求始终返回:

I'm trying to combine the GROUP BY function with a MAX in oracle. I read a lot of docs around, try to figure out how to format my request by Oracle always returns:

ORA-00979:不是按表达式分组"

ORA-00979: "not a group by expression"

这是我的要求:

SELECT A.T_ID, B.T, MAX(A.V) 
FROM bdd.LOG A, bdd.T_B B
WHERE B.T_ID = A.T_ID
GROUP BY A.T_ID
HAVING MAX(A.V) < '1.00';

有什么建议吗?

编辑:我的字段的数据类型似乎有些棘手.

EDIT It seems to got some tricky part with the datatype of my fields.

• T_ID是VARCHAR2
• A.V是VARCHAR2
• B.T是CLOB

• T_ID is VARCHAR2
• A.V is VARCHAR2
• B.T is CLOB

推荐答案

经过一些修复，看来主要问题出在group by

After some fixes it seems that the major issue was in the group by

您必须在SELECT和GROUP BY

我也仅采用CLOB的子字符串来使其工作.工作要求是:

I also take only a substring of the CLOB to get it works. THe working request is :

    SELECT TABLE_A.ID,
       TABLE_A.VA,
       B.TRACE
FROM
(SELECT A.T_ID ID,
          MAX(A.V) VA
   FROM BDD.LOG A
   GROUP BY A.T_ID HAVING MAX(A.V) <= '1.00') TABLE_A,
                                                                BDD.T B
WHERE TABLE_A.ID = B.T_id;

这篇关于如何在Oracle的CLOB列上使用GROUP BY?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！