******sql的UPDATE问题,小小问题,只是我不会,在线等********
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 看到这个了么,当天的最后一条,所以保持不变化
谢谢各位大虾............
[解决办法]
--result
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
[解决办法]
create table t(code int identity,level varchar(5),date varchar(20))
insert into t select 'a ', '2007-05-01 '
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-1
select * from t
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
(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)
[解决办法]
create table SORT (code int identity,level varchar(5),date varchar(20))
insert into SORT select 'a ', '2007-05-01 '
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 SORT
UPDATE 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-1
SELECT * FROM SORT