高手请进：mysql 如何产生连续字段

以下是Oracle下的，希望对你有帮助。

你的 time 在这里写出 date1，表名叫 temp_test。

1、生成某个国家的连续日期

with x as (select start_date + level-1 start_date

from (select country, min(date1) start_date, max(date1) end_date

from temp_test a

where a.country = 'usa'

group by a.country) temp

connect by level <= (end_date - start_date) + 1

) select * from x

2、过滤掉已有的日期

select start_date from (with x as (select start_date + level-1 start_date

from (select country, min(date1) start_date, max(date1) end_date

from temp_test a

where a.country = 'usa'

group by a.country) temp

connect by level <= (end_date - start_date) + 1

) select * from x ) xx

where xx.start_date not in (select date1 from temp_test a where a.country = 'usa')

3、插入。

insert into temp_test

(date1, country, people)

select start_date, 'usa', 0

from (with x as (select start_date + level - 1 start_date

from (select country,

min(date1) start_date,

max(date1) end_date

from temp_test a

where a.country = 'usa'

group by a.country) temp

connect by level <= (end_date - start_date) + 1)

select *

from x) xx

where xx.start_date not in

(select date1 from temp_test a where a.country = 'usa')

/*求连续旷工三天以上的数据*/

declare @t table(name varchar(10), [date] datetime,n int default(1))

insert into @t(name,date) select '张三','2011.09.01'

union select '张三','2011.09.08'

union select '张三','2011.09.09'

union select '张三','2011.09.10'

union select '李四','2011.09.06'

union select '李四','2011.09.09'

union select '李四','2011.09.12'

union select '李四','2011.09.15'

union select '小五','2011.08.06'

select * from @t

--select name,COUNT(*) 次数

-- from @t group by name having(COUNT(*)>3)

declare @nm varchar(10),@d datetime,@n int=1,@lastNm varchar(10)='',@lastD datetime='1900.01.01',@lastN int =1

declare cur cursor for select name ,[date],n from @t order by name,date

open cur

fetch next from cur into @nm,@d,@n

while (@@FETCH_STATUS =0 ) begin

if @lastNm =@nm and @d=@lastD+1 begin

update @t set n = @lastN +1 where name=@nm and [date]=@d

select @lastN = n from @t where name=@nm and [date]=@d

set @lastNm=@nm

set @lastD =@d

end

else begin

set @lastNm = @nm

set @lastD =@d

set @lastN = @n

end

fetch next from cur into @nm,@d,@n

end

close cur

deallocate cur

select * from @t where n>=3

---