开发人员反映序列不能使用，自己回来测试了一下
select test.sequence.nextval,mgr,sum(sal) from emp group by mgr
ORA-02287: 此处不允许序号
果然，在外面再包一层就可以了
SQL> select test_sequence.nextval,mgr,sm from (select mgr,sum(sal) sm from emp group by mgr );
NEXTVAL MGR SM
———- —– ———-
27 7839 8275
28 7782 1300
29 7698 6550
30 7902 5800
31 7566 6000
32 7788 1100
6 rows selected
看来序列使用时有限制的
看到书上说有这么多限制
Restrictions on Sequence Values You cannot use CURRVAL and NEXTVAL in the
following constructs:
■ A subquery in a DELETE, SELECT, or UPDATE statement
■ A query of a view or of a materialized view
■ A SELECT statement with the DISTINCT operator
■ A SELECT statement with a GROUP BY clause or ORDER BY clause –这个就是我遇到的那种情况
■ A SELECT statement that is combined with another SELECT statement with the
UNION, INTERSECT, or MINUS set operator
■ The WHERE clause of a SELECT statement
■ The DEFAULT value of a column in a CREATE TABLE or ALTER TABLE statement
■ The condition of a CHECK constrain
总结，无法使用序列的CURRVAL 和NEXTVAL的情况为：
■ A subquery in a DELETE, SELECT, or UPDATE statement
（子查询包括在DELETE,SELECT,UPDATE语句中）
■ A query of a view or of a materialized view
（对视图和物化视图的查询）
■ A SELECT statement with the DISTINCT operator
（SELECT查询中用到了DISTINCT）
■ A SELECT statement with a GROUP BY clause or ORDER BY clause –这个就是我遇到的那种情况
（SELECT查询中用到了GROUP BY ,ORDER BY )
■ A SELECT statement that is combined with another SELECT statement with the
UNION, INTERSECT, or MINUS set operator
(SELECT查询和其他SELECT查询结合，并且用到了UNION,INTERSECT,MINUS等操作）
■ The WHERE clause of a SELECT statement
（SELECT查询语句中的where条件不能用序列
例子：select 1 from dual where t_emp_calendar_seq.nextval = 95）
■ The DEFAULT value of a column in a CREATE TABLE or ALTER TABLE statement
（？？）
■ The condition of a CHECK constrain
（在CHECK限制条件中不能用）