Fguid fid fpartsno fnum
1 11 a 1
2 11 a 1
3 11 a 1
4 2 b 2
5 2 b 2
6 3 c 7
7 4 5 8
当前fid和fpratsno和下一条的fid,fpartsno比较相同就更新fnum+1
Fguid fid fpartsno fnum
1 11 a 1
2 11 a 2
3 11 a 3
4 2 b 1
5 2 b 2
6 3 c 7
7 4 5 8
=========================
我写的游标,不对,那个地方需要改一下就可以了?请高手指点一下!
DECLARE @a INT
DECLARE @b varchar(64)
DECLARE @c varchar(64)
DECLARE @d varchar(64)
DECLARE @e varchar(64)
SET @a = 1
SET @b=''
SET @c=''
SET @d=''
SET @e=''
DECLARE c1 CURSOR --声明静态游标
FOR
SELECT fid,fpartsno FROM CME_PARTSCODEDETAIL order by fid
FOR UPDATE OF [FNum]--声明要更新的列
OPEN c1
FETCH NEXT FROM c1 into @b,@c
WHILE @@FETCH_STATUS = 0
BEGIN
if @d =@b and @c =@e
begin
UPDATE CME_PARTSCODEDETAIL SET FNum = @a WHERE CURRENT OF c1 --使用where current of myCursor是为了只更新当前行
SET @a = @a + 1
end
else
begin
SET @a = 1
end
FETCH NEXT FROM c1 into @d,@e
END
CLOSE c1
DEALLOCATE c1
1 11 a 1
2 11 a 1
3 11 a 1
4 2 b 2
5 2 b 2
6 3 c 7
7 4 5 8
当前fid和fpratsno和下一条的fid,fpartsno比较相同就更新fnum+1
Fguid fid fpartsno fnum
1 11 a 1
2 11 a 2
3 11 a 3
4 2 b 1
5 2 b 2
6 3 c 7
7 4 5 8
=========================
我写的游标,不对,那个地方需要改一下就可以了?请高手指点一下!
DECLARE @a INT
DECLARE @b varchar(64)
DECLARE @c varchar(64)
DECLARE @d varchar(64)
DECLARE @e varchar(64)
SET @a = 1
SET @b=''
SET @c=''
SET @d=''
SET @e=''
DECLARE c1 CURSOR --声明静态游标
FOR
SELECT fid,fpartsno FROM CME_PARTSCODEDETAIL order by fid
FOR UPDATE OF [FNum]--声明要更新的列
OPEN c1
FETCH NEXT FROM c1 into @b,@c
WHILE @@FETCH_STATUS = 0
BEGIN
if @d =@b and @c =@e
begin
UPDATE CME_PARTSCODEDETAIL SET FNum = @a WHERE CURRENT OF c1 --使用where current of myCursor是为了只更新当前行
SET @a = @a + 1
end
else
begin
SET @a = 1
end
FETCH NEXT FROM c1 into @d,@e
END
CLOSE c1
DEALLOCATE c1
解决方案 »
- 为什么我检索最大值时最大是99?明明有超过100的数!
- 请帮看下这个题目,我不懂???
- 向英文操作系统下的英文sqlserver数据库写入中文要在N,我用的参数怎么办?该怎么写?
- 求一段sql 拼接方法 双循环 存储过程
- 关于MSSQL2000更新问题.
- 有个棘手的MSSQL2005表结构及海量数据的查询性能问题
- 如何将数据库数据变动情况发送到qq
- VB访问sql server 数据库一般采用什么方法
- 需要得到当前月份的两位描述,如“05”“11”等,但是通过dapart()得到的只有“5”“11”,如何得出“05”?
- 一个ASP程序运行时,SQL发生超时,求高手指点?
- 怎样在Java中创建临时表,Java与sql server2005连接
- SQL Server 2005,有个ndf文件居然有117GB,怎么搞定它?
--> 生成测试数据表: [tb]
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([Fguid] [int],[fid] [int],[fpartsno] [nvarchar](10),[fnum] [int])
INSERT INTO [tb]
SELECT '1','11','a','1' UNION ALL
SELECT '2','11','a','1' UNION ALL
SELECT '3','11','a','1' UNION ALL
SELECT '4','2','b','2' UNION ALL
SELECT '5','2','b','2' UNION ALL
SELECT '6','3','c','7' UNION ALL
SELECT '7','4','5','8'--SELECT * FROM [tb]-->SQL查询如下:
DECLARE @fid INT, @fpartsno NVARCHAR(10), @i INT
UPDATE tb
SET fnum = @i,
@i = CASE
WHEN @fid<>fid AND @fpartsno<>fpartsno THEN fnum
ELSE ISNULL(@i, 0)+1
END,
@fid = fid,
@fpartsno = fpartsno
SELECT *
FROM tb
/*
Fguid fid fpartsno fnum
----------- ----------- ---------- -----------
1 11 a 1
2 11 a 2
3 11 a 3
4 2 b 2
5 2 b 3
6 3 c 7
7 4 5 8(7 行受影响)
*/按照描述应该是这个结果吧
Fguid fid fpartsno fnum
----------- ----------- ---------- -----------
1 11 a 1
2 11 a 2
3 11 a 3
4 2 b 1
5 2 b 2
6 3 c 7
7 4 5 8
--> 生成测试数据表: [tb]
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([Fguid] [int],[fid] [int],[fpartsno] [nvarchar](10),[fnum] [int])
INSERT INTO [tb]
SELECT '1','11','a','1' UNION ALL
SELECT '2','11','a','1' UNION ALL
SELECT '3','11','a','1' UNION ALL
SELECT '4','2','b','2' UNION ALL
SELECT '5','2','b','2' UNION ALL
SELECT '6','3','c','7' UNION ALL
SELECT '7','4','5','8'--SELECT * FROM [tb]-->SQL查询如下:
DECLARE @fid INT, @fpartsno NVARCHAR(10), @i INT
UPDATE tb
SET fnum = @i,
@i = CASE
WHEN @fid<>fid AND @fpartsno<>fpartsno THEN 1
ELSE ISNULL(@i, 0)+1
END,
@fid = fid,
@fpartsno = fpartsno
WHERE EXISTS(
SELECT 1
FROM tb t
WHERE t.fid = tb.fid
AND tb.fpartsno = t.fpartsno
AND fguid<>tb.Fguid
)
SELECT *
FROM tb
/*
Fguid fid fpartsno fnum
----------- ----------- ---------- -----------
1 11 a 1
2 11 a 2
3 11 a 3
4 2 b 1
5 2 b 2
6 3 c 7
7 4 5 8(7 行受影响)
*/那就这样。
--> 生成测试数据表: [tb]
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([Fguid] [int],[fid] [int],[fpartsno] [nvarchar](10),[fnum] [int])
INSERT INTO [tb]
SELECT '1','11','a','1' UNION ALL
SELECT '2','11','a','1' UNION ALL
SELECT '3','11','a','1' UNION ALL
SELECT '4','2','b','2' UNION ALL
SELECT '5','2','b','2' UNION ALL
SELECT '6','3','c','7' UNION ALL
SELECT '7','4','5','8'--SELECT * FROM [tb]-->SQL查询如下:
UPDATE tb
SET fnum = (
SELECT COUNT(1)
FROM tb t
WHERE t.fid = tb.fid
AND tb.fpartsno = t.fpartsno
AND fguid<= tb.Fguid
)
WHERE EXISTS(
SELECT 1
FROM tb t
WHERE t.fid = tb.fid
AND tb.fpartsno = t.fpartsno
AND fguid<>tb.Fguid
)
SELECT *
FROM tb
/*
Fguid fid fpartsno fnum
----------- ----------- ---------- -----------
1 11 a 1
2 11 a 2
3 11 a 3
4 2 b 1
5 2 b 2
6 3 c 7
7 4 5 8(7 行受影响)
*/也可以这样。