关于一SQL查询语句写法 ,求高手

[lirong]

现有一查询需求即根据时间查询：查询条件 2011-04-11 到 2011-04-13 如果表当前查询时间没记录，如何获取相最近时间的记录填充结果？
表数据结构如下：
cid cdate cnum
0001 2011-04-12 12
0001 2011-04-11 11
0002 2011-04-12 20
0002 2011-04-11 18
0003 2011-04-10 30

要求得到的查询结果

cid cdate cnum
0001 2011-04-13 12
0001 2011-04-12 12
0001 2011-04-11 11
0002 2011-04-13 20
0002 2011-04-12 20
0002 2011-04-11 18
0003 2011-04-13 30
0003 2011-04-12 30
0003 2011-04-11 30

[fanzhouqi]

结果又问题吧

[快溜]

[Quote=引用楼主 dulong007 的回复:]
现有一查询需求即根据时间查询：查询条件 2011-04-11 到 2011-04-13 如果表当前查询时间没记录，如何获取相最近时间的记录填充结果？
表数据结构如下：
cid cdate cnum
0001 2011-04-12 12
0001 2011-04-11 11
0002 2011-04-12 20
0002 2011-04-11 18
000……
[/Quote]该什么条件就什么条件，改查多少就查多少，这什么业务查询，无语。

[叶子]

create function generateTimeV2
(
    @begin_date datetime,
    @end_date datetime
)
returns @t table(date datetime)
as
begin
    insert into @t
    select dateadd(dd,number,@begin_date) AS date 
    from master..spt_values 
    where type='p' and dateadd(dd,number,@begin_date)<=@end_date
    return
END

declare @t table (cid varchar(4),cdate datetime,cnum int)
insert into @t
select '0001','2011-04-12',12 union all
select '0001','2011-04-11',11 union all
select '0002','2011-04-12',20 union all
select '0002','2011-04-11',18 union all
select '0003','2011-04-10',30

SELECT aa.date,aa.cid,bb.cnum FROM (
select 
convert( varchar(10),date,120) AS date,b.cid from dbo.generateTimeV2('2011-04-11','2011-04-13')
CROSS JOIN (select distinct cid from @t) b
) aa LEFT JOIN @t bb
ON aa.date=bb.cdate AND aa.cid=bb.cid
/*
date       cid  cnum
---------- ---- -----------
2011-04-11 0001 11
2011-04-11 0002 18
2011-04-11 0003 NULL
2011-04-12 0001 12
2011-04-12 0002 20
2011-04-12 0003 NULL
2011-04-13 0001 NULL
2011-04-13 0002 NULL
2011-04-13 0003 NULL
*/

[叶子]

2011-04-11 到 2011-04-13 作为条件得不到10号的30

[AcHerat]

create table tb(cid varchar(10),cdate datetime,cnum int)
insert into tb
select '0001' ,'2011-04-12' ,12 union all
select '0001' ,'2011-04-11' ,11 union all
select '0002' ,'2011-04-12' ,20 union all
select '0002' ,'2011-04-11' ,18 union all
select '0003' ,'2011-04-10' ,30
go

declare @start datetime
declare @end datetime
set @start = '2011-04-11'
set @end = '2011-04-13'
select distinct a.cid,dateadd(dd,b.number,@start) as date
	into #tb
from tb a,master..spt_values b
where b.[type] = 'p' and b.number between 0 and datediff(dd,@start,@end)

select a.cid,a.date,(case when b.cnum is null then (select cnum from tb 
											where cdate = (select max(cdate) from tb where cid = a.cid) and cid = a.cid) 
					else b.cnum end) cnum
from #tb a left join tb b on a.cid = b.cid and a.date = b.cdate
order by a.cid,a.date

drop table tb,#tb

/*

cid        date                    cnum
---------- ----------------------- -----------
0001       2011-04-11 00:00:00.000 11
0001       2011-04-12 00:00:00.000 12
0001       2011-04-13 00:00:00.000 12
0002       2011-04-11 00:00:00.000 18
0002       2011-04-12 00:00:00.000 20
0002       2011-04-13 00:00:00.000 20
0003       2011-04-11 00:00:00.000 30
0003       2011-04-12 00:00:00.000 30
0003       2011-04-13 00:00:00.000 30

(9 行受影响)

[dawugui]

create table tb(cid varchar(10),cdate datetime,cnum int)
insert into tb values('0001', '2011-04-12', 12)
insert into tb values('0001', '2011-04-11', 11)
insert into tb values('0002', '2011-04-12', 20)
insert into tb values('0002', '2011-04-11', 18)
insert into tb values('0003', '2011-04-10', 30)
go

declare @sdate datetime
declare @edate datetime
set @sdate = '2011-04-11'
set @edate = '2011-04-13'

select p.cid , p.dt cdate , cnum = (case when p.dt = q.cdate and p.cid = q.cid then q.cnum
else (select top 1 cnum from tb o where p.cid = o.cid order by o.cdate desc) end) from
(
select m.* , n.cid from
(
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
) m,
(
select distinct cid from tb
) n
) p left join tb q
on p.dt = q.cdate and p.cid = q.cid
order by p.cid , cdate desc

drop table tb

/*
cid        cdate                                                  cnum        
---------- ------------------------------------------------------ ----------- 
0001       2011-04-13 00:00:00.000                                12
0001       2011-04-12 00:00:00.000                                12
0001       2011-04-11 00:00:00.000                                11
0002       2011-04-13 00:00:00.000                                20
0002       2011-04-12 00:00:00.000                                20
0002       2011-04-11 00:00:00.000                                18
0003       2011-04-13 00:00:00.000                                30
0003       2011-04-12 00:00:00.000                                30
0003       2011-04-11 00:00:00.000                                30

（所影响的行数为 9 行）

*/

[昵称被占用了]

结果是对的
说思路吧，谁帮忙写下
用系统表产生序列，继而生成需要范围内的所有日期，再左连接表数据，左连接条件用cid和小于等于自己日期的最大cdate

[昵称被占用了]

晕，发现打字没有写语句快，以后干脆不回复了

[lirong]

[Quote=引用 4 楼 maco_wang 的回复:]
2011-04-11 到 2011-04-13 作为条件得不到10号的30
[/Quote]
差不多是这样写，但是为什么 cid 0001 和 0002 的记录 cnum 也为空呢？
2011-04-13 0001 NULL
2011-04-13 0002 NULL
2011-04-13 0003 NULL

[lirong]

谢谢 maco_wang，AcHerat，Haiwer ，经过测试问题已经解决了。