group by与ORA-02287: sequence number not allowed here

--创建表
SQL> create table t_test(a int);
 
Table created

--插入数据
SQL> insert into t_test values(1);
 
1 row inserted
 
SQL> commit;
 
Commit complete
 
SQL> insert into t_test values(2);
 
1 row inserted
 
SQL> commit;
 
Commit complete
 
SQL> select * from t_test;
 
                                      A
---------------------------------------
                                      1
                                      2
--正常group by运行正常
SQL> select a,count(a) from t_test group by a;
 
                                      A   COUNT(A)
--------------------------------------- ----------
                                      1          1
                                      2          1
--添加常量字符串列也可用于group by
SQL> select a,count(a),'x' from t_test group by a;
 
                                      A   COUNT(A) 'X'
--------------------------------------- ---------- ---
                                      1          1 x
                                      2          1 x
 
--创建序列
SQL> create sequence seq_rpt start with 1;
 
Sequence created

--group by不能直接使用序列
SQL> select a,count(a),'x',seq_rpt.nextval from t_test group by a;
 
select a,count(a),'x',seq_rpt.nextval from t_test group by a
 
ORA-02287: sequence number not allowed here
 
--包装group by可使用sequence
SQL> select a,juji,x,seq_rpt.nextval from (select a,count(a) as juji,'x' as x from t_test group by a);
 
                                      A       JUJI X    NEXTVAL
--------------------------------------- ---------- - ----------
                                      1          1 x          1
                                      2          1 x          2

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9240380/viewspace-755336/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9240380/viewspace-755336/