code level date
1 a 2007-05-01
2 b 2007-05-01
3 a 2007-05-01
4 c 2007-05-01
5 e 2007-05-01
6 f 2007-05-01
7 a 2007-05-01
8 f 2007-05-02
9 a 2007-05-02
10 c 2007-05-02
11 d 2007-05-02
12 g 2007-05-02
13 a 2007-05-02
14 e 2007-05-02
我想做的是以ID为顺序,把每天的记录的level字段的值前移到上一条,当天最后一条不处理,当天的第一条的LEVEL值不要了结果这样的
code level date
1 b 2007-05-01
2 a 2007-05-01
3 c 2007-05-01
4 e 2007-05-01
5 f 2007-05-01
6 a 2007-05-01
7 a 2007-05-01
8 a 2007-05-02
9 c 2007-05-02
10 d 2007-05-02
11 g 2007-05-02
12 a 2007-05-02
13 e 2007-05-02
14 e 2007-05-02
看到了么,结果中
1 b 2007-05-01 这个LEVEL值是原数据的第二条记录的植
7 a 2007-05-01 看到这个了么,当天的最后一条,所以保持不变化谢谢各位大虾............
1 a 2007-05-01
2 b 2007-05-01
3 a 2007-05-01
4 c 2007-05-01
5 e 2007-05-01
6 f 2007-05-01
7 a 2007-05-01
8 f 2007-05-02
9 a 2007-05-02
10 c 2007-05-02
11 d 2007-05-02
12 g 2007-05-02
13 a 2007-05-02
14 e 2007-05-02
我想做的是以ID为顺序,把每天的记录的level字段的值前移到上一条,当天最后一条不处理,当天的第一条的LEVEL值不要了结果这样的
code level date
1 b 2007-05-01
2 a 2007-05-01
3 c 2007-05-01
4 e 2007-05-01
5 f 2007-05-01
6 a 2007-05-01
7 a 2007-05-01
8 a 2007-05-02
9 c 2007-05-02
10 d 2007-05-02
11 g 2007-05-02
12 a 2007-05-02
13 e 2007-05-02
14 e 2007-05-02
看到了么,结果中
1 b 2007-05-01 这个LEVEL值是原数据的第二条记录的植
7 a 2007-05-01 看到这个了么,当天的最后一条,所以保持不变化谢谢各位大虾............
from tab a left outer join tab b on a.date=b.date and a.code+1=b.code
1 b 2007-05-01 00:00:00.000
2 a 2007-05-01 00:00:00.000
3 c 2007-05-01 00:00:00.000
4 e 2007-05-01 00:00:00.000
5 f 2007-05-01 00:00:00.000
6 a 2007-05-01 00:00:00.000
7 a 2007-05-01 00:00:00.000
8 a 2007-05-02 00:00:00.000
9 c 2007-05-02 00:00:00.000
10 d 2007-05-02 00:00:00.000
11 g 2007-05-02 00:00:00.000
12 a 2007-05-02 00:00:00.000
13 e 2007-05-02 00:00:00.000
14 e 2007-05-02 00:00:00.000
insert into t select 'b','2007-05-01'
insert into t select 'a','2007-05-01'
insert into t select 'c','2007-05-01'
insert into t select 'e','2007-05-01'
insert into t select 'f','2007-05-01'
insert into t select 'a','2007-05-01'insert into t select 'f','2007-05-02'
insert into t select 'a','2007-05-02'
insert into t select 'c','2007-05-02'
insert into t select 'd','2007-05-02'
insert into t select 'g','2007-05-02'
insert into t select 'a','2007-05-02'
insert into t select 'e','2007-05-02'update t set level=b.level from t a, (select * from t a where
exists( select 1 from t where a.code>code and a.date=date)) b
where a.date=b.date and a.code=b.code-1select * from tcode level date
----------- ----- --------------------
1 b 2007-05-01
2 a 2007-05-01
3 c 2007-05-01
4 e 2007-05-01
5 f 2007-05-01
6 a 2007-05-01
7 a 2007-05-01
8 a 2007-05-02
9 c 2007-05-02
10 d 2007-05-02
11 g 2007-05-02
12 a 2007-05-02
13 e 2007-05-02
14 e 2007-05-02(14 行受影响)
update tablename
set level = b.level
from tablename a
left join tablename b on a.id = b.id
where id <> any(select max(id) as id from tablename group by level,date)
update tablename
set level = b.level
from tablename a
left join (select * from tablename) b on a.code = b.code - 1
where not exists (select max(code) as code from tablename group by level,date)
insert into SORT select 'b','2007-05-01'
insert into SORT select 'a','2007-05-01'
insert into SORT select 'c','2007-05-01'
insert into SORT select 'e','2007-05-01'
insert into SORT select 'f','2007-05-01'
insert into SORT select 'a','2007-05-01'insert into SORT select 'f','2007-05-02'
insert into SORT select 'a','2007-05-02'
insert into SORT select 'c','2007-05-02'
insert into SORT select 'd','2007-05-02'
insert into SORT select 'g','2007-05-02'
insert into SORT select 'a','2007-05-02'
insert into SORT select 'e','2007-05-02'SELECT * FROM SORTUPDATE SORT SET level =D.LEVEL FROM SORT A ,
(SELECT * FROM SORT B WHERE EXISTS
(SELECT * FROM SORT C WHERE C.date = B.date AND C.CODE < B.CODE) ) D
WHERE A.DATE = D.DATE AND A.CODE = D.CODE-1SELECT * FROM SORT