34,590
社区成员
发帖
与我相关
我的任务
分享
create table test
(id char(10),
pdate datetime,
pid varchar(10) ,
edit_num int)
select * from test
insert into test
select '103','2012-1-4 20:21:20','王五',0
union all
select '103','2012-1-2 22:21:23','王五',0
union all
select '103','2012-1-2 23:26:23','王五',0
union all
select '101','2012-1-2 23:26:23','張三',0
union all
select '101','2012-1-3 12:21:03','張三',0
union all
select '101','2012-1-3 12:21:03','張三',0
union all
select '102','2013-1-2 23:26:23','李四',0
union all
select '102','2013-1-3 12:21:03','李四',0
union all
select '102','2013-1-3 12:21:03','李四',0
USE tempdb
GO
--DROP TABLE test;
CREATE table test
(id char(10),
pdate datetime,
pid varchar(10) ,
edit_num int)
-- select * from test
insert into test
select '103','2012-1-4 20:21:20','王五',0
union all
select '103','2012-1-2 22:21:23','王五',0
union all
select '103','2012-1-2 23:26:23','王五',0
union all
select '101','2012-1-2 23:26:23','張三',0
union all
select '101','2012-1-3 12:21:03','張三',0
union all
select '101','2012-1-3 12:21:03','張三',0
union all
select '102','2013-1-2 23:26:23','李四',0
union all
select '102','2013-1-3 12:21:03','李四',0
union all
select '102','2013-1-3 12:21:03','李四',0
go
DECLARE Cur1 CURSOR LOCAL STATIC FOR
SELECT id,pdate FROM Test GROUP BY id,pdate
DECLARE @id char(10),@pdate DATETIME,@edit_num INT,@id2 char(10)
SET @id2=''
OPEN Cur1
FETCH NEXT FROM Cur1 INTO @id,@pdate
WHILE @@FETCH_STATUS=0
BEGIN
IF @id2<>@id
SET @edit_num=1
ELSE
SET @edit_num=@edit_num+1
SET @id2=@id
UPDATE Test SET edit_num=@edit_num WHERE id=@id AND pdate=@pdate;
FETCH NEXT FROM Cur1 INTO @id,@pdate
END
CLOSE Cur1
DEALLOCATE Cur1
GO
SELECT * FROM Test ORDER BY id,pdate
/*
id pdate pid edit_num
101 2012-01-02 23:26:23.000 張三 1
101 2012-01-03 12:21:03.000 張三 2
101 2012-01-03 12:21:03.000 張三 2
102 2013-01-02 23:26:23.000 李四 1
102 2013-01-03 12:21:03.000 李四 2
102 2013-01-03 12:21:03.000 李四 2
103 2012-01-02 22:21:23.000 王五 1
103 2012-01-02 23:26:23.000 王五 2
103 2012-01-04 20:21:20.000 王五 3
*/
--查询
select *,edit_num=DENSE_RANK()OVER(PARTITION BY id ORDER BY pdate) from test
--更新
UPDATE t SET edit_num=edit_num2 FROM (select *,edit_num2=DENSE_RANK()OVER(PARTITION BY id ORDER BY pdate) from test) AS t
[/quote]
謝謝,版主如果用游標或者函數如何寫循環呢?--查询
select *,edit_num=DENSE_RANK()OVER(PARTITION BY id ORDER BY pdate) from test
--更新
UPDATE t SET edit_num=edit_num2 FROM (select *,edit_num2=DENSE_RANK()OVER(PARTITION BY id ORDER BY pdate) from test) AS t
UPDATE a SET edit_num = b.edit_num FROM test a JOIN (SELECT id ,
pdate ,
pid ,
DENSE_RANK() OVER ( PARTITION BY pid ORDER BY pdate ) AS edit_num
FROM test)b ON a.pid = b.pid AND a.pdate = b.pdate
declare @getnum int ---賦值
declare @id varchar(10)
declare @pid varchar(10)
declare @edit_num int
set @getnum =1
--set @edit_num =0
declare @getdata datetime
declare my_cursor2 cursor for (select id ,pid from #test)
open my_cursor2
fetch next from my_cursor2 into @id, @pid
while @@FETCH_STATUS =0
begin
declare my_cursor cursor for (select edit_num, pdate from test)
open my_cursor
fetch next from my_cursor into @edit_num, @getdata
while @@FETCH_STATUS =0
begin
set @edit_num =@edit_num +@getnum
update test set edit_num =@edit_num where pdate =@getdata and id=@id and pid =@pid
set @edit_num =@edit_num +1
fetch next from my_cursor into @edit_num, @getdata
end
fetch next from my_cursor2 into @id,@pid
end
close my_cursor
deallocate my_cursor
close my_cursor2
deallocate my_cursor2
我這麼寫不行.但是不知道如何修改了
--查询
select *,edit_num=DENSE_RANK()OVER(PARTITION BY id ORDER BY pdate) from test
--更新
UPDATE t SET edit_num=edit_num2 FROM (select *,edit_num=DENSE_RANK()OVER(PARTITION BY id ORDER BY pdate) from test) AS t