user_cd(pk) user_type job_date
000001 001 2015/05/01
000001 001 2015/05/02
000001 001 2015/05/03
000001 002 2015/05/04
000001 002 2015/05/05
000001 002 2015/05/06
000002 001 2015/05/01
000002 001 2015/05/02
000002 001 2015/05/03
000002 002 2015/05/04
000002 002 2015/05/05
000002 002 2015/05/06
000002 003 2015/05/07
000002 003 2015/05/08
000002 003 2015/05/09
000002 001 2015/05/10
000002 001 2015/05/11
000002 001 2015/05/12
用sql能检索出这种数据么？
000001 001 2015/05/01~2015/05/03
000001 002 2015/05/04~2015/05/06
000002 001 2015/05/01~2015/05/03
000002 002 2015/05/04~2015/05/06
000002 003 2015/05/07~2015/05/09
000002 001 2015/05/10~2015/05/12
或者只能在检索结果中遍历才能得到？
------解决思路----------------------
WITH t1(user_cd,user_type,job_date,rn1,rn2) AS (
SELECT user_cd,user_type,job_date,
ROW_NUMBER() OVER(PARTITION BY user_cd ORDER BY job_date) rn1,
ROW_NUMBER() OVER(PARTITION BY user_cd ORDER BY user_type,job_date) rn2
FROM table1
)
SELECT user_cd,
user_type,
MIN(job_date)
------解决思路----------------------
'~'
------解决思路----------------------
MAX(job_date),
rn1-rn2
FROM t1
GROUP BY user_cd, user_type,rn1-rn2