table某栏位有法令变化 能插入理应存在的row吗
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
------解决方案--------------------
------解决方案--------------------
是这样不，这里用了递归来生成序列数实现：
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
/*
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
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)