数据库中有两张表
第一个表中有这样一条记录
id start end
1 2007-1-1 2007-1-5想根据两个日期向另一张表中插入相应的记录,如
id date
1 2007-1-1
1 2007-1-2
1 2007-1-3
1 2007-1-4
1 2007-1-5应该怎么写呢?
第一个表中有这样一条记录
id start end
1 2007-1-1 2007-1-5想根据两个日期向另一张表中插入相应的记录,如
id date
1 2007-1-1
1 2007-1-2
1 2007-1-3
1 2007-1-4
1 2007-1-5应该怎么写呢?
解决方案 »
- 谢谢:我想请教一个数据拆分的问题!
- 如何设置“数据库中内容被挂马清除工具”,在线等!
- 一对多的查询
- 存储过程问题
- 如何用SQL语句找出根结点下的所有子树。
- 触发器的问题?
- 简单的问题,sqlserver中的主键列信息存在哪个系统表呀、
- 请教各位如何写这个where条件令到这条语句可以根据一例为模糊条件进行修改本表里另一例的数据
- 我在一段程序中要从一个SQL/SERVER中拷贝数据(Image->Ole对象)到ACCESS,为什么会出错?
- 关于在SQL SERVER中的一个"很难"的查询问题!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
- sql查询
- 网页上注册之后数据写不进数据库?这是怎么回事??
select a.列1,a.列2.... from 源表1 a,源表2 b
where a.[date] between b.[start] and [end] 其中要和目标表的列1,2...对应好
select a.列1,a.列2.... from 源表1 a,源表2 b
where a.[date] between b.[start] and [b.end]
如果你的时间列是字符型 ,
insert 目标表 (列1,列2...)
select a.列1,a.列2.... from 源表1 a,源表2 b
where cast(a.[date] as datetime) between cast(b.[start] as datetime) and cast(b.[end] as datetime)
declare @ta table(id int, start datetime, [end] datetime)
insert @ta
select 1, '2007-1-1' , '2007-1-5'
--用表变量或用临时表
declare @start datetime,@end datetime
select @start=start from @ta
select @end=[end] from @ta
declare @tmp table(date varchar(10))
while convert(varchar(10),@start,120)!>convert(varchar(10),@end,120)
begin
insert @tmp select convert(varchar(10),@start,120)
set @start=@start+1
end
select ta.id,tb.date
from @tmp tb left join @ta ta
on tb.date between convert(varchar(10),ta.start,120)and convert(varchar(10),ta.[end],120)
id date
----------- ----------
1 2007-01-01
1 2007-01-02
1 2007-01-03
1 2007-01-04
1 2007-01-05(5 行受影响)
drop table #t1
create table #t1
(id int,
start datetime,
[end] datetime
)
insert into #t1
select '1','2007-1-1','2007-1-5'
select * from #t1drop table #t2
create table #t2
(id int,
date datetime
)
insert into #t2
select '1','2007-1-1' union all select '1','2007-1-2' union all select '1','2007-1-3' union all select '1','2007-1-4' union all select '1','2007-1-5'
select * from #t2插入语句:
create table newtable(id int identity(1,1),newdate datetime)--建立新表insert newtable (newdate) --插
select b.date from #t1 a,#t2 b
where b.[date] between a.[start] and a.[end] select * from newtable
---------------
id newdate
1 2007-01-01 00:00:00.000
2 2007-01-02 00:00:00.000
3 2007-01-03 00:00:00.000
4 2007-01-04 00:00:00.000
5 2007-01-05 00:00:00.000
declare @ta table(id int, start datetime, [end] datetime)
insert @ta select 1, '2007-1-1' , '2007-1-5'
union all select 2, '2007-2-1' , '2007-2-5'
--用表变量或用临时表
declare @start datetime,@end datetime
select @start=min(start) from @ta
select @end=max([end]) from @ta
declare @tmp table(date varchar(10))
while convert(varchar(10),@start,120)!>convert(varchar(10),@end,120)
begin
insert @tmp select convert(varchar(10),@start,120)
set @start=@start+1
endselect * from (select ta.id,tb.date
from @tmp tb left join @ta ta
on tb.date between convert(varchar(10),ta.start,120)and convert(varchar(10),ta.[end],120)
)test where id is not null
id date
----------- ----------
1 2007-01-01
1 2007-01-02
1 2007-01-03
1 2007-01-04
1 2007-01-05
2 2007-02-01
2 2007-02-02
2 2007-02-03
2 2007-02-04
2 2007-02-05(10 行受影响)
insert @ta select 1, '2007-1-1' , '2007-1-5'
union all select 2, '2007-1-4' , '2007-1-10'
--用表变量或用临时表
declare @start datetime,@end datetime
select @start=min(start) from @ta
select @end=max([end]) from @ta
declare @tmp table(date varchar(10))
while convert(varchar(10),@start,120)!>convert(varchar(10),@end,120)
begin
insert @tmp select convert(varchar(10),@start,120)
set @start=@start+1
endselect * from (select ta.id,tb.date
from @tmp tb left join @ta ta
on tb.date between convert(varchar(10),ta.start,120)and convert(varchar(10),ta.[end],120)
)test where id is not null
id date
----------- ----------
1 2007-01-01
1 2007-01-02
1 2007-01-03
1 2007-01-04
1 2007-01-05
2 2007-01-04
2 2007-01-05
2 2007-01-06
2 2007-01-07
2 2007-01-08
2 2007-01-09
2 2007-01-10(12 行受影响)
insert A select 1, '2007-01-01', '2007-01-05'
union all select 2, '2007-01-08', '2007-01-10'
go
create table B(id int, [date] datetime)
godeclare @id int, @start datetime, @end datetime
declare cur cursor local
for
select * from A
open cur
fetch next from cur into @id, @start, @end
while @@fetch_status=0
begin
while @start<=@end
begin
insert B select @id, @start
set @start=@start+1
end fetch next from cur into @id, @start, @end
endclose cur
deallocate cur
goselect * from B
go--result
id date
----------- ------------------------------------------------------
1 2007-01-01 00:00:00.000
1 2007-01-02 00:00:00.000
1 2007-01-03 00:00:00.000
1 2007-01-04 00:00:00.000
1 2007-01-05 00:00:00.000
2 2007-01-08 00:00:00.000
2 2007-01-09 00:00:00.000
2 2007-01-10 00:00:00.000(8 row(s) affected)
存储过程?