求一复杂查询,100送

[shuai45]

id name date number
1 a 2009-01-01 1
2 b 2009-01-01 2
1 a 2009-01-03 4
2 b 2009-01-03 4

把相关数据缺少的时间（date）补上，同时缺少的数据（number）的值为：当前的number=上个月的num。
如果
id name date number
1 a 2009-01-01 1
2 b 2009-01-01 2
1 a 2009-01-02 1
2 b 2009-01-02 2
1 a 2009-01-03 4
2 b 2009-01-03 4
1 a 2009-01-04 4
2 b 2009-01-04 4

时间按照一周7来补，且按照date排序

[百年树人]

如果连续缺了多天呢，继续往上取值？

[dawugui]

1 a 2009-01-04 4
2 b 2009-01-04 4
数据补到什么时候?

[华夏小卒]

这种要求。最好给个补得范围比较合理

[--小F--]

先MARK

[nianran520]

时间按照一周7来补?啥意思？

[shuai45]

只是本年度1月份的
2009-01-01 补到2009-01-08 就可以了.
就是该年该月的7天

[华夏小卒]

---------------------------------------------
--> Author : js_szy
--> Target : 各位大大,小卒就是想要一朵花
--> Date   : 2009-12-01 14:57:03
--> Version: SQL Server 2005
---------------------------------------------
											    
--> 测试数据: @tb
declare @tb table (id int,name varchar(1),date datetime,number int)
insert into @tb
select 1,'a','2009-01-01',1 union all
select 2,'b','2009-01-01',2 union all
select 1,'a','2009-01-03',4 union all
select 2,'b','2009-01-03',4

--> sql 2005
;with szy as 
(	select distinct id,name,t.date from @tb 
	cross apply(
		select date=dateadd(day,number,'2009-01-01') 
		from master..spt_values 
		where type='p' and number between 0 and 6 
	)t
)

select s.*,number=isnull(number,(select max(number) from @tb where name=s.name and date<s.date))
from szy s 
left join @tb b
on s.name=b.name and s.date=b.date
order by  s.date


id          name date                    number
----------- ---- ----------------------- -----------
1           a    2009-01-01 00:00:00.000 1
2           b    2009-01-01 00:00:00.000 2
1           a    2009-01-02 00:00:00.000 1
2           b    2009-01-02 00:00:00.000 2
1           a    2009-01-03 00:00:00.000 4
2           b    2009-01-03 00:00:00.000 4
1           a    2009-01-04 00:00:00.000 4
2           b    2009-01-04 00:00:00.000 4
1           a    2009-01-05 00:00:00.000 4
2           b    2009-01-05 00:00:00.000 4
1           a    2009-01-06 00:00:00.000 4
2           b    2009-01-06 00:00:00.000 4
1           a    2009-01-07 00:00:00.000 4
2           b    2009-01-07 00:00:00.000 4

(14 行受影响)

[shuai45]

牛叉

[华夏小卒]

        where type='p' and number between 0 and 6    --这个6改成7 ，8号的数据就有了


要使用日期范围的话，要再复杂些

[feixianxxx]

学习

[yanele]

路过,学习!

[dawugui]

create table tb(id int,name varchar(1),date datetime,number int)
insert into tb
select 1,'a','2009-01-01',1 union all
select 2,'b','2009-01-01',2 union all
select 1,'a','2009-01-03',4 union all
select 2,'b','2009-01-03',4
go

declare @sdate datetime
declare @edate datetime
set @sdate = '2009-01-01'
set @edate = '2009-01-07'

select t1.id,
       t1.name,
       t1.dt date    , 
       case when exists(select 1 from tb t2 where t2.id = t1.id and t2.date = t1.dt) 
            then (select number from tb t2 where t2.id = t1.id and t2.date = t1.dt) 
            else (select top 1 number from tb t2 where t2.id = t1.id and t2.date < t1.dt order by t2.date desc)
       end date from
(
select m.id , m.name , n.dt from
(select distinct id , name from tb) m,
(select 
    dateadd(dd,num,@sdate) dt
from 
    (select isnull((select count(1) from sysobjects where id<t.id),0) as num from sysobjects t) a
where
    dateadd(dd,num,@sdate)<=@edate
) n
) t1
order by id , name , dt
drop table tb

/*
id          name date                                                   date        
----------- ---- ------------------------------------------------------ ----------- 
1           a    2009-01-01 00:00:00.000                                1
1           a    2009-01-02 00:00:00.000                                1
1           a    2009-01-03 00:00:00.000                                4
1           a    2009-01-04 00:00:00.000                                4
1           a    2009-01-05 00:00:00.000                                4
1           a    2009-01-06 00:00:00.000                                4
1           a    2009-01-07 00:00:00.000                                4
2           b    2009-01-01 00:00:00.000                                2
2           b    2009-01-02 00:00:00.000                                2
2           b    2009-01-03 00:00:00.000                                4
2           b    2009-01-04 00:00:00.000                                4
2           b    2009-01-05 00:00:00.000                                4
2           b    2009-01-06 00:00:00.000                                4
2           b    2009-01-07 00:00:00.000                                4

（所影响的行数为 14 行）

*/

[xman_78tom]

create table #tab (id int,[name] char(1),date varchar(10),number int);
insert into #tab
 select 1,'a','2009-01-01',1 union all select 2,'b','2009-01-01',2 union all
 select 1,'a','2009-01-03',4 union all select 2,'b','2009-01-03',4

-- 感觉这样做效率很差。还不如用游标+临时表。
;with t as(
select distinct b.id,b.[name],
date=dateadd(dd,a.[number],min(date) over (partition by b.id,b.[name]))
from master.dbo.spt_values a,#tab b
where a.[type]='P' and a.[number]<7)
select t.id,t.name,t.date,
isnull(a.[number],(select top(1) number from #tab 
where date<t.date and id=t.id and [name]=t.[name] order by date desc))
from t left join #tab a 
on t.id=a.id and t.name=a.name and t.date=a.date
order by t.date

/*
1	a	2009-01-01 00:00:00.000	1
2	b	2009-01-01 00:00:00.000	2
1	a	2009-01-02 00:00:00.000	1
2	b	2009-01-02 00:00:00.000	2
1	a	2009-01-03 00:00:00.000	4
2	b	2009-01-03 00:00:00.000	4
1	a	2009-01-04 00:00:00.000	4
2	b	2009-01-04 00:00:00.000	4
1	a	2009-01-05 00:00:00.000	4
2	b	2009-01-05 00:00:00.000	4
1	a	2009-01-06 00:00:00.000	4
2	b	2009-01-06 00:00:00.000	4
1	a	2009-01-07 00:00:00.000	4
2	b	2009-01-07 00:00:00.000	4
*/

[xman_78tom]

insert into #tab
 select id,[name],convert(varchar(10),dateadd(dd,7,min(date)),120),
 (select number from #tab where date=max(t.date) and id=t.id and [name]=t.[name])
 from #tab t group by id,[name]

declare cur cursor for select * from #tab order by id,[name],date
open cur

declare @id int,@name char(1),@date varchar(10),@number int
fetch next from cur into @id,@name,@date,@number
declare @olddate datetime,@newdate datetime,@oldnumber int
set @olddate=@date
while @@fetch_status=0
begin
 set @newdate=dateadd(dd,1,@olddate)
 while @newdate<@date
 begin
  insert into #tab values(@id,@name,convert(varchar(10),@newdate,120),@oldnumber)
  set @newdate=dateadd(dd,1,@newdate)
 end
 select @oldnumber=@number,@olddate=@date
 fetch next from cur into @id,@name,@date,@number
end

close cur
deallocate cur

[sych888]

学习

[kldeng_05]

先MARK再说~