MSSQL里面有一个ROW_NUMBER() OVER(ORDER BY trace_time)很容易实现,可是在ORACLE里面就要用ROWNUM来实现。具体SQL如下
SELECT x.emp_name,x.door_name,x.trace_time,x.seq-y.min_seq+1as seq FROM (SELECT emp_name, door_name, trace_time,ROWNUM AS seq FROM (SELECT emp_name,door_name,trace_time FROM emp_trace WHERE emp_name='张三' ORDERBY trace_time) ) x, ( SELECT emp_name, MIN (seq) min_seq FROM ( SELECT emp_name, door_name,trace_time, ROWNUM AS seq FROM ( SELECT emp_name,door_name,trace_time FROM emp_trace WHERE emp_name='张三' ORDERBY trace_time) ) GROUPBY emp_name ) y where x.emp_name = y.emp_name