源表如下：第一行cj 变成第二行，第二行的，就成第三行、、、、、
现在要还原成如下:
就是把向上错位的，移上去，还原。
如何通过SQL语句实现，求大神们帮忙！
------解决方案--------------------
WITH a1 (xm,cj) AS
(
SELECT 'a',90 UNION ALL
SELECT 'b',50 UNION ALL
SELECT 'c',60 UNION ALL
SELECT 'd',70 UNION ALL
SELECT 'e',80
)
SELECT a.xm,ISNULL(b.cj,c.cj) cj
FROM a1 a
OUTER APPLY
(
SELECT TOP 1 cj FROM a1 WHERE xm>a.xm ORDER BY xm
) b
OUTER APPLY
(
SELECT TOP 1 cj FROM a1 ORDER BY xm
) c
------解决方案--------------------
这个是查询：
--drop table tb
create table tb(xm varchar(10),cj int)
insert into tb
SELECT 'a',90 UNION ALL
SELECT 'b',50 UNION ALL
SELECT 'c',60 UNION ALL
SELECT 'd',70 UNION ALL
SELECT 'e',80
go
--下面t1.rownum % 5,其中的5就是记录的条数,如果有10条记录,就是10
;with t
as
(
select *,
ROW_NUMBER() over(order by getdate()) as rownum
from tb
)
select t1.xm,t1.cj,t2.cj as '还原后的cj'
from t t1
inner join t t2
on t1.rownum%5+1 = t2.rownum
/*
xm cj 还原后的cj
a 90 50
b 50 60
c 60 70
d 70 80
e 80 90
*/
------解决方案--------------------
还原cj值
--drop table tb
create table tb(xm varchar(10),cj int)
insert into tb
SELECT 'a',90 UNION ALL
SELECT 'b',50 UNION ALL
SELECT 'c',60 UNION ALL
SELECT 'd',70 UNION ALL
SELECT 'e',80
go
--下面t1.rownum % 5,其中的5就是记录的条数,如果有10条记录,就是10
;with t
as
(
select *,
ROW_NUMBER() over(order by getdate()) as rownum
from tb
),
tt
as
(
select t1.xm,t1.cj,t2.cj as restore_cj
from t t1
inner join t t2
on t1.rownum%5+1 = t2.rownum
)
--还原记录
update tt
set cj = restore_cj
--再次查询,已经把记录都还原了
select * from tb
/*
xm cj
a 50
b 60
c 70
d 80
e 90
*/