代码之家 › 专栏 › 技术社区 › Egor Skriptunoff

SELECT UDAF(AGGR())中的奇怪行为

oracle10g oracle11g oracle sql

Egor Skriptunoff · 技术社区 · 6 年前

有两个select语句:

select       max(min(str)) from (select 0 id, 'a' str from dual) group by id having min(str) = 'a';
select strconcat(min(str)) from (select 0 id, 'a' str from dual) group by id having min(str) = 'a';

唯一的区别是外层聚合函数: max() strconcat() .
你可以替换 strconcat()

前一条语句按预期工作:它返回string 'a'
后一种说法:
-(在Oracle 10g上)给出错误的结果(null而不是字符串 “a” )
-(在Oracle 11g上)提高 ORA-00979: not a GROUP BY expression

你能解释一下这种行为吗?

1 回复 | 直到 6 年前

Littlefoot 6 年前

10克:

看来 WM_CONCAT 可供我使用(是的,无文件记录,但在这种情况下无所谓)或 STRCONCAT

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE    10.2.0.5.0      Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production

SQL> select       max(min(str)) result
  2  from (select 0 id, 'a' str from dual)
  3  group by id having min(str) = 'a';

R
-
a

SQL> -- returns NULL, just as you've said
SQL> select wm_concat(min(str)) result
  2  from (select 0 id, 'a' str from dual)
  3  group by id having min(str) = 'a';

RESULT
---------------------------------------------------------------------


SQL> -- but, if we put it a level "up", the result is OK
SQL> select wm_concat(minstr) result
  2  from (select min(str) minstr
  3        from (select 0 id, 'a' str from dual)
  4        group by id having min(str) = 'a'
  5       );

RESULT
---------------------------------------------------------------------
a

SQL>

11克:

好的,两个查询(我正在使用 listagg 此处):

SQL> select       max(min(str)) result
  2  from (select 0 id, 'a' str from dual)
  3  group by id having min(str) = 'a';

R
-
a

SQL>
SQL> select listagg(min(str), ',') within group (order by null) result
  2  from (select 0 id, 'a' str from dual)
  3  group by id having min(str) = 'a';

RESULT
-----------------------------------------------------------------------
a

SQL>