SELECT empno, counter, MIN(sale_date) start_date, MAX(sale_date) end_date, SUM(sale_num) num
FROM (SELECT t1.*, sale_date - row_number() over(PARTITION BY empno, counter ORDER BY sale_date) flag FROM a t1)
GROUP BY empno, counter, flag
FROM (SELECT t1.*, sale_date - row_number() over(PARTITION BY empno, counter ORDER BY sale_date) flag FROM a t1)
GROUP BY empno, counter, flag
2 FROM (SELECT t1.*, sale_date - row_number() over(PARTITION BY empno, counter ORDER BY sale_date) flag FROM a t1)
3 GROUP BY empno, counter, flag
4 /EMPNO COUNTER START_DATE END_DATE NUM
-------------------- -------------------- ----------- ----------- ----------
aaa cc 2005-1-4 2005-1-5 60
aaa cc 2005-1-8 2005-1-8 20
aaa dd 2005-1-6 2005-1-7 30
bbb cc 2005-1-7 2005-1-7 30
bbb dd 2005-1-6 2005-1-6 10
把连续的那段时间减去ROW_NUMBER()就刚好是个固定值.这样就可以把连续的分到一组,实在是高.
-------------------- -------------------- ----------- ----------
aaa cc 2005-1-4 40
aaa cc 2005-1-5 20
bbb dd 2005-1-6 10
aaa dd 2005-1-6 20
bbb cc 2005-1-7 30
aaa dd 2005-1-7 10
aaa cc 2005-1-8 20
aaa cc 2005-1-4 11
aaa cc 2005-1-5 11
aaa cc 2005-1-6 2210 rows selectedSQL>
SQL> SELECT empno, counter, MIN(sale_date) start_date, MAX(sale_date) end_date, SUM(sale_num) num
2 FROM (SELECT t1.*, sale_date - dense_rank() over(PARTITION BY empno, counter ORDER BY sale_date) flag FROM a t1)
3 GROUP BY empno, counter, flag
4 /EMPNO COUNTER START_DATE END_DATE NUM
-------------------- -------------------- ----------- ----------- ----------
aaa cc 2005-1-4 2005-1-6 104
aaa cc 2005-1-8 2005-1-8 20
aaa dd 2005-1-6 2005-1-7 30
bbb cc 2005-1-7 2005-1-7 30
bbb dd 2005-1-6 2005-1-6 10至于你说的营业员休息是什么意思,还不是很明白,把测试数据贴出来会比较清楚