当前位置: 代码迷 >> Oracle管理 >> ORA-00937: 非单组分组函数
  详细解决方案

ORA-00937: 非单组分组函数

热度:93   发布时间:2016-04-24 05:59:55.0
ORA-00937: 非单组分组函数?
问题如题,
这是我的表结构:
CREATE   TABLE   BOOK
(
              ID   NUMBER,
              STUNAME   VARCHAR2(8),
              BOOKNAME   VARCHAR(20)
);
SQL   语句如下:

select   STUNAME,max(decode(BOOKNAME, 'BOOK1 ',COUNT(BOOKNAME)))       as       BOOK1
    ,max(decode(BOOKNAME, 'BOOK2 ',COUNT(BOOKNAME)))     as     BOOK2
    ,max(decode(BOOKNAME, 'BOOK3 ',COUNT(BOOKNAME)))     as     BOOK3
    ,max(decode(BOOKNAME, 'BOOK4 ',COUNT(BOOKNAME)))     as     BOOK4
    from   BOOK
    group   by   STUNAME
    union   all
select   STUNAME,max(decode(BOOKNAME, 'BOOK1 ',COUNT(BOOKNAME)))
    ,max(decode(BOOKNAME, 'BOOK2 ',COUNT(BOOKNAME)))
    ,max(decode(BOOKNAME, 'BOOK3 ',COUNT(BOOKNAME)))
    ,max(decode(BOOKNAME, 'BOOK4 ',COUNT(BOOKNAME)))
    from   BOOK
    group   by   STUNAME
    union   all
select   STUNAME,max(decode(BOOKNAME, 'BOOK1 ',COUNT(BOOKNAME)))
    ,max(decode(BOOKNAME, 'BOOK2 ',COUNT(BOOKNAME)))
    ,max(decode(BOOKNAME, 'BOOK3 ',COUNT(BOOKNAME)))
    ,max(decode(BOOKNAME, 'BOOK4 ',COUNT(BOOKNAME)))
    from   BOOK
    group   by   STUNAME;

------解决方案--------------------
--如:

create or replace function test_fun(STUNAMES varchar2) return varchar2 as
v_out varchar2(500):= ' ';
cursor c is select a.BOOKNAME from BOOK a where a.STUNAME=STUNAMES;
begin
for v_temp in c loop
v_out:=v_out|| ' '||v_temp.BOOKNAME;
end loop;
return v_out;
exception
when others then
return 'An error occured ';
end ;
/
select distinct STUNAME,test_fun(STUNAME) BOOK from BOOK