序号自增的问题

Question

select nvl((select max(no)
             from MET_OPS_REGNO
            where year = to_number(to_char(to_date('2015-12-20 00:00:00',
                                                   'yyyy-MM-dd hh24:mi:ss'),
                                           'yyyy'))),
           0) + 1
  from dual;

Answer 1

select nvl((select max(nvl(no, 0))
             from MET_OPS_REGNO
            where year = to_number(to_char(to_date('2014-12-20 00:00:00',
                                                   'yyyy-MM-dd hh24:mi:ss'),
                                           'yyyy'))),
           0) + 1
  from dual

Answer 2

为什么非要套两层呢。。直接NVL(MAX(NO), 0) + 1不就可以了么。。试了一下，可以
WITH MET_OPS_REGNO AS( 
SELECT '2014' YEAR,1 no,'001' ID FROM dual UNION ALL 
SELECT '2014' YEAR,2 no,'002' ID FROM dual UNION ALL 
SELECT '2014' YEAR,3 no,'003' ID FROM dual
 ) 
 SELECT NVL(MAX(NO), 0) + 1
   FROM MET_OPS_REGNO
  WHERE YEAR = TO_NUMBER(TO_CHAR(TO_DATE('2016-12-20 00:00:00',
                                         'yyyy-MM-dd hh24:mi:ss'),
                                 'yyyy'))