table某栏位有规律变化 能插入理应存在的row吗？
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 3 sql insert select
[解决办法]
;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
/*
IDQP
1012
1553
2026
2574
3074
3574
4043
4555
5092
5592
6011
6548
7063
*/
if object_id('tb') is not null
drop table tb
go
create 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 tb
union all
select 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)
/*
idQP
1012
1553
2026
2574
3074
3574
4043
4555
5092
5592
6011
6548
7063
*/
