Question

sqlite数据库中批量插入时间数据

需求是这样的，每一天都可以被分为节假日（type代号为1），工作日（type代号为2）。节假日为这一年的第19周，第20周，23周,4周的每一天。现在我想用sql语句生成自动一年的。求大神帮忙。
表的结构如下。

插入完数据库之后的效果如下





Answer 1

一步搞定！（其中tmp表为任何具有连续ID的超过366条记录的表，即从未删除记录的表）
drop table if exists a;
create table a as select date('now','start of year','+'||(rowid-1)||' day') date, (rowid-1)/7+1 type,(rowid-1)/7+1 week from tmp order by rowid limit 366;
update a set type=case type when 4 then 1 when 19 then 1 when 20 then 1 when 23 then 1 else 2 end;

追问

谢谢高手。希望大神能逐条解释一下这个SQL语句~

追答

呵呵，本次问题已经回答完毕

Answer 2

[code=SQL]
drop table if exists tmp;
create table tmp (
id INTEGER NOT NULL PRIMARY KEY, v int);

insert into tmp (v) values (1);
insert into tmp (v) select v from tmp;
insert into tmp (v) select v from tmp;
insert into tmp (v) select v from tmp;
insert into tmp (v) select v from tmp;
insert into tmp (v) select v from tmp;
insert into tmp (v) select v from tmp;
insert into tmp (v) select v from tmp;
insert into tmp (v) select v from tmp;
insert into tmp (v) select v from tmp;

drop table if exists a;
create table a as
select
date('now',
'start of year',
'+'||(rowid-1)||' day'
) date,
(rowid-1)%7 as weekday,
case (rowid-1)%7
when 0 then 2
when 6 then 2
else 1 end type
from tmp
order by rowid
limit 366;
[/code]