table
ID Q P
-----------
10 1 2
15 5 3
20 2 6
25 7 4
40 4 3
45 5 5
50 9 2
60 1 1
65 4 8
70 6 3
ID间的差距都是5
但table里缺少了ID=30 ID=35 ID=55能够select出以下的结果,并把QP代入最近一笔的QP值吗?
ID Q P
-----------
10 1 2
15 5 3
20 2 6
25 7 4
30 7 4 <--Q P代入前一笔的QP
35 7 4 <--Q P代入前一笔的QP
40 4 3
45 5 5
50 9 2
55 9 2 <--Q P代入前一笔的QP
60 1 1
65 4 8
70 6 3sqlinsertselect
ID Q P
-----------
10 1 2
15 5 3
20 2 6
25 7 4
40 4 3
45 5 5
50 9 2
60 1 1
65 4 8
70 6 3
ID间的差距都是5
但table里缺少了ID=30 ID=35 ID=55能够select出以下的结果,并把QP代入最近一笔的QP值吗?
ID Q P
-----------
10 1 2
15 5 3
20 2 6
25 7 4
30 7 4 <--Q P代入前一笔的QP
35 7 4 <--Q P代入前一笔的QP
40 4 3
45 5 5
50 9 2
55 9 2 <--Q P代入前一笔的QP
60 1 1
65 4 8
70 6 3sqlinsertselect
;with cte(ID,Q,P) as
(
select 10,1,2
union all select 15,5,3
union all select 20,2,6
union all select 25,7,4
union all select 40,4,3
union all select 45,5,5
union all select 50,9,2
union all select 60,1,1
union all select 65,4,8
union all select 70,6,3
)
select a.num AS ID,Q=case when b.Q is not null then b.Q else (select top 1 Q from cte c where c.ID<a.num order by ID desc) end
,P=case when b.P is not null then b.P else (select top 1 P from cte c where c.ID<a.num order by ID desc) end
from
(
select distinct a.number*5 as num
from master..spt_values a
inner join (select MIN(id) as m1,MAX(id) as m2 from cte) b
on a.number between m1/5 and m2/5
)a
left join cte b on a.num=b.ID/*
ID Q P
10 1 2
15 5 3
20 2 6
25 7 4
30 7 4
35 7 4
40 4 3
45 5 5
50 9 2
55 9 2
60 1 1
65 4 8
70 6 3
*/
if object_id('tb') is not null
drop table tb
gocreate table tb(ID int,Q int,P int)insert into tb
select 10,1,2
union all select 15,5,3
union all select 20,2,6
union all select 25,7,4
union all select 40,4,3
union all select 45,5,5
union all select 50,9,2
union all select 60,1,1
union all select 65,4,8
union all select 70,6,3;with t
as
(
select min(id) as id,max(id) max_id from tbunion allselect id + 5 ,max_id
from t
where t.id < max_id
)select t.id,
case when tb.q is not null
then tb.q
else (select top 1 t2.q from tb t2
where t2.id < t.id
order by t2.id desc)
end as Q,
case when tb.P is not null
then tb.P
else (select top 1 t2.P from tb t2
where t2.id < t.id
order by t2.id desc)
end as P
from t
left join tb
on t.id = tb.id
--option(maxrecursion 10000)
/*
id Q P
10 1 2
15 5 3
20 2 6
25 7 4
30 7 4
35 7 4
40 4 3
45 5 5
50 9 2
55 9 2
60 1 1
65 4 8
70 6 3
*/