试试
select a.nn+b.nn from
(select count(distinct userid) nn from task a where a.user_id='user1' and a.start_date='2003-12-03 0:0:0') a,
(select count(distinct b.start_date) nn from task a,task b,task c where a.user_id='user1' and a.user_id=b.user_id and b.user_id=c.user_id
and a.start_date='2003-12-03 0:0:0' and b.start_date<=a.start_date
and c.start_date<=a.start_date and b.start_date+1=c.start_date) b
你的日期字段中不知道时间是不是0时0分0秒? 不是的话需要另外处理一下。
写得比较复杂了,如果数据量大的话,性能我可不敢保证,呵呵。
select a.nn+b.nn from
(select count(distinct userid) nn from task a where a.user_id='user1' and a.start_date='2003-12-03 0:0:0') a,
(select count(distinct b.start_date) nn from task a,task b,task c where a.user_id='user1' and a.user_id=b.user_id and b.user_id=c.user_id
and a.start_date='2003-12-03 0:0:0' and b.start_date<=a.start_date
and c.start_date<=a.start_date and b.start_date+1=c.start_date) b
你的日期字段中不知道时间是不是0时0分0秒? 不是的话需要另外处理一下。
写得比较复杂了,如果数据量大的话,性能我可不敢保证,呵呵。
select a.nn+b.nn from
(select count(distinct a.user_id) nn from task a where a.user_id='user1'
and A.start_date='2003-12-03 0:0:0') a,
(select count(distinct b.start_date) nn from task a, task b
where a.user_id='user1' and a.user_id=b.user_id
and b.start_date<='2003-12-03 0:0:0' and A.start_date+1=b.start_date) b
select count(1) from
(select start_date-lead(start_date,1,sysdate) over(oder by start_date desc) num from task where start_date<=sysdate order by start_date desc)
where num=1