搜索
编程论坛 → 数据库技术 → 『 SQL Server论坛 』 → 请教sql出现负数的原因！谢谢！
标题：请教sql出现负数的原因！谢谢！
只看楼主
liker_yang
Rank: 1
等　级：新手上路
帖　子：9
专家分：0
注　册：2007-4-9
楼主
 问题点数：0 回复次数：8 
请教sql出现负数的原因！谢谢！

数据库一直用的好好的，昨天晚上因为一些原因就删除重建，所有使用的数据都是原来的数据，只不过datafix一下，保留最初设计的时候的数据，但是在往里面加东西的时候却出现了这个问题！怪
如有高手请帮忙解决一下!
小弟先行谢过！

这是现在的数据

原来，没有出问题的数据是

搜索更多相关主题的帖子: sql　负数　
2007-04-09 10:20
liker_yang
Rank: 1
等　级：新手上路
帖　子：9
专家分：0
注　册：2007-4-9
第 2 楼
 得分:0 
就是iid这个列出了问题！
请大家帮我解决下，谢谢！

2007-04-09 10:21
Kendy123456
Rank: 10Rank: 10Rank: 10
等　级：贵宾
威　望：62
帖　子：2720
专家分：0
注　册：2007-1-3
第 3 楼
 得分:0 
2张表排序不一样 啥也看不出来

2007-04-09 14:41
liker_yang
Rank: 1
等　级：新手上路
帖　子：9
专家分：0
注　册：2007-4-9
第 4 楼
 得分:0 

这个。。。。。
主要是iid这列啊，。
上面的负数，下面的正常的啊。


2007-04-09 14:43
雨之神
Rank: 1
等　级：新手上路
帖　子：62
专家分：0
注　册：2005-11-23
第 5 楼
 得分:0 


int类型的长度:

-2^31 (-2,147,483,648) 到 2^31 - 1 (2,147,483,647)

自己想去.


是为众神之神
2007-04-09 17:03
棉花糖ONE
Rank: 7Rank: 7Rank: 7
等　级：贵宾
威　望：32
帖　子：2987
专家分：0
注　册：2006-7-13
第 6 楼
 得分:0 
但是逸出的话,是不会有数据显示的,编译无法通过

26403021 sql群 博客 blog./user15/81152/index.shtml
2007-04-09 17:05
xiyou419
Rank: 1
等　级：新手上路
威　望：2
帖　子：104
专家分：0
注　册：2007-3-18
第 7 楼
 得分:0 
要是只是以前的数据出现了这情况,你可以用SQL语句把他全改回去.

道德往往可以弥补智慧的缺陷;但智慧永远不能填补道德的空白.
2007-04-09 17:35
liker_yang
Rank: 1
等　级：新手上路
帖　子：9
专家分：0
注　册：2007-4-9
第 8 楼
 得分:0 

问题已解决，谢谢各位。下面将解决的思路说下。有什么不对的地方请指教，
CREATE TABLE [Item] (
[PID] [char] (10) COLLATE Finnish_Swedish_CI_AS NOT NULL ,
[IID] [int] NULL ,
[Index] [smallint] NOT NULL ,
[Prefix] [tinyint] NOT NULL ,
[Info] [tinyint] NOT NULL ,
[Num] [int] NOT NULL ,
[MaxEnd] [tinyint] NOT NULL ,
[CurEnd] [tinyint] NOT NULL ,
[SetGem] [tinyint] NULL ,
[XAttack] [tinyint] NULL ,
[XMagic] [tinyint] NULL ,
[XDefense] [tinyint] NULL ,
[XHit] [tinyint] NULL ,
[XDodge] [tinyint] NULL ,
[Protect] [tinyint] NULL ,
[UpgrLevel] [tinyint] NULL ,
[UpgrRate] [tinyint] NULL ,
[IID2] [int] IDENTITY (1, 1) NOT NULL
) ON [PRIMARY]
GO
以上是那个表的创建代码！


/****** Object: Stored Procedure dbo.Insert_Item Script Date: 2006-10-25 14:32:40 ******/
CREATE procedure Insert_Item
@UID int
as

declare @PID int
declare @IID int
declare @Job tinyint
select @IID = max( IID) from Item

declare Player_Cursor cursor for
select [PID] from Player where [UID] = @UID
open Player_Cursor

fetch next from Player_Cursor into @PID
while (@@FETCH_STATUS =0)
begin
select @Job = [Class] from Player where [PID] = @PID
if ( @Job = 0)
begin
set @IID = @IID + 1
insert into Item (PID, IID, [Index], Prefix, Info, Num, MaxEnd, CurEnd, SetGem) values ( @PID, @IID, 1, 0, 8, 1, 100, 100, 0)
set @IID = @IID + 1
insert into Item (PID, IID, [Index], Prefix, Info, Num, MaxEnd, CurEnd, SetGem) values ( @PID, @IID, 2, 0, 8, 1, 100, 100, 0)
set @IID = @IID + 1
insert into Item (PID, IID, [Index], Prefix, Info, Num, MaxEnd, CurEnd, SetGem) values ( @PID, @IID, 3, 0, 8, 1, 100, 100, 0)
set @IID = @IID + 1
insert into Item (PID, IID, [Index], Prefix, Info, Num, MaxEnd, CurEnd, SetGem) values ( @PID, @IID, 4, 0, 8, 1, 100, 100, 0)
set @IID = @IID + 1
insert into Item (PID, IID, [Index], Prefix, Info, Num, MaxEnd, CurEnd, SetGem) values ( @PID, @IID, 5, 0, 8, 1, 100, 100, 0)
set @IID = @IID + 1
insert into Item (PID, IID, [Index], Prefix, Info, Num, MaxEnd, CurEnd, SetGem) values ( @PID, @IID, 6, 0, 8, 1, 100, 100, 0)
set @IID = @IID + 1
insert into Item (PID, IID, [Index], Prefix, Info, Num, MaxEnd, CurEnd, SetGem) values ( @PID, @IID, 7, 0, 8, 1, 100, 100, 0)
set @IID = @IID + 1
insert into Item (PID, IID, [Index], Prefix, Info, Num, MaxEnd, CurEnd, SetGem) values ( @PID, @IID, 8, 0, 8, 1, 100, 100, 0)
set @IID = @IID + 1
insert into Item (PID, IID, [Index], Prefix, Info, Num, MaxEnd, CurEnd, SetGem) values ( @PID, @IID, 9, 0, 8, 1, 100, 100, 0)
set @IID = @IID + 1
insert into Item (PID, IID, [Index], Prefix, Info, Num, MaxEnd, CurEnd, SetGem) values ( @PID, @IID, 10, 0, 8, 1, 100, 100, 0)
set @IID = @IID + 1
insert into Item (PID, IID, [Index], Prefix, Info, Num, MaxEnd, CurEnd, SetGem) values ( @PID, @IID, 11, 0, 8, 1, 100, 100, 0)
set @IID = @IID + 1
insert into Item (PID, IID, [Index], Prefix, Info, Num, MaxEnd, CurEnd, SetGem) values ( @PID, @IID, 12, 0, 8, 1, 100, 100, 0)
set @IID = @IID + 1
insert into Item (PID, IID, [Index], Prefix, Info, Num, MaxEnd, CurEnd, SetGem) values ( @PID, @IID, 13, 0, 8, 1, 100, 100, 0)
set @IID = @IID + 1
insert into Item (PID, IID, [Index], Prefix, Info, Num, MaxEnd, CurEnd, SetGem) values ( @PID, @IID, 14, 0, 8, 1, 100, 100, 0)
set @IID = @IID + 1
insert into Item (PID, IID, [Index], Prefix, Info, Num, MaxEnd, CurEnd, SetGem) values ( @PID, @IID, 15, 0, 8, 1, 100, 100, 0)
set @IID = @IID + 1
insert into Item (PID, IID, [Index], Prefix, Info, Num, MaxEnd, CurEnd, SetGem) values ( @PID, @IID, 16, 0, 8, 1, 100, 100, 0)
set @IID = @IID + 1
insert into Item (PID, IID, [Index], Prefix, Info, Num, MaxEnd, CurEnd, SetGem) values ( @PID, @IID, 17, 0, 8, 1, 100, 100, 0)
set @IID = @IID + 1
insert into Item (PID, IID, [Index], Prefix, Info, Num, MaxEnd, CurEnd, SetGem) values ( @PID, @IID, 18, 0, 8, 1, 100, 100, 0)
set @IID = @IID + 1
insert into Item (PID, IID, [Index], Prefix, Info, Num, MaxEnd, CurEnd, SetGem) values ( @PID, @IID, 19, 0, 8, 1, 100, 100, 0)
set @IID = @IID + 1
insert into Item (PID, IID, [Index], Prefix, Info, Num, MaxEnd, CurEnd, SetGem) values ( @PID, @IID, 20, 0, 8, 1, 100, 100, 0)
set @IID = @IID + 1
insert into Item (PID, IID, [Index], Prefix, Info, Num, MaxEnd, CurEnd, SetGem) values ( @PID, @IID, 21, 0, 8, 1, 100, 100, 0)
set @IID = @IID + 1
insert into Item (PID, IID, [Index], Prefix, Info, Num, MaxEnd, CurEnd, SetGem) values ( @PID, @IID, 28, 0, 8, 1, 100, 100, 0)
set @IID = @IID + 1
insert into Item (PID, IID, [Index], Prefix, Info, Num, MaxEnd, CurEnd, SetGem) values ( @PID, @IID, 29, 0, 8, 1, 100, 100, 0)
set @IID = @IID + 1
insert into Item (PID, IID, [Index], Prefix, Info, Num, MaxEnd, CurEnd, SetGem) values ( @PID, @IID, 30, 0, 8, 1, 100, 100, 0)
set @IID = @IID + 1
insert into Item (PID, IID, [Index], Prefix, Info, Num, MaxEnd, CurEnd, SetGem) values ( @PID, @IID, 49, 0, 8, 1, 100, 100, 0)
set @IID = @IID + 1
insert into Item (PID, IID, [Index], Prefix, Info, Num, MaxEnd, CurEnd, SetGem) values ( @PID, @IID, 50, 0, 8, 1, 100, 100, 0)
set @IID = @IID + 1
insert into Item (PID, IID, [Index], Prefix, Info, Num, MaxEnd, CurEnd, SetGem) values ( @PID, @IID, 51, 0, 8, 1, 100, 100, 0)
set @IID = @IID + 1
insert into Item (PID, IID, [Index], Prefix, Info, Num, MaxEnd, CurEnd, SetGem) values ( @PID, @IID, 52, 0, 8, 1, 100, 100, 0)
set @IID = @IID + 1
insert into Item (PID, IID, [Index], Prefix, Info, Num, MaxEnd, CurEnd, SetGem) values ( @PID, @IID, 53, 0, 8, 1, 100, 100, 0)
set @IID = @IID + 1
insert into Item (PID, IID, [Index], Prefix, Info, Num, MaxEnd, CurEnd, SetGem) values ( @PID, @IID, 59, 0, 8, 1, 100, 100, 0)
set @IID = @IID + 1
insert into Item (PID, IID, [Index], Prefix, Info, Num, MaxEnd, CurEnd, SetGem) values ( @PID, @IID, 60, 0, 8, 1, 100, 100, 0)
set @IID = @IID + 1
insert into Item (PID, IID, [Index], Prefix, Info, Num, MaxEnd, CurEnd, SetGem) values ( @PID, @IID, 61, 0, 8, 1, 100, 100, 0)
end -- if end
else if ( @Job = 2)
begin
set @IID = @IID + 1
insert into Item (PID, IID, [Index], Prefix, Info, Num, MaxEnd, CurEnd, SetGem) values ( @PID, @IID, 22, 0, 8, 1, 100, 100, 0)
set @IID = @IID + 1
insert into Item (PID, IID, [Index], Prefix, Info, Num, MaxEnd, CurEnd, SetGem) values ( @PID, @IID, 23, 0, 8, 1, 100, 100, 0)
set @IID = @IID + 1
insert into Item (PID, IID, [Index], Prefix, Info, Num, MaxEnd, CurEnd, SetGem) values ( @PID, @IID, 24, 0, 8, 1, 100, 100, 0)
set @IID = @IID + 1
insert into Item (PID, IID, [Index], Prefix, Info, Num, MaxEnd, CurEnd, SetGem) values ( @PID, @IID, 25, 0, 8, 1, 100, 100, 0)
set @IID = @IID + 1
insert into Item (PID, IID, [Index], Prefix, Info, Num, MaxEnd, CurEnd, SetGem) values ( @PID, @IID, 26, 0, 8, 1, 100, 100, 0)
set @IID = @IID + 1
insert into Item (PID, IID, [Index], Prefix, Info, Num, MaxEnd, CurEnd, SetGem) values ( @PID, @IID, 27, 0, 8, 1, 100, 100, 0)
set @IID = @IID + 1
insert into Item (PID, IID, [Index], Prefix, Info, Num, MaxEnd, CurEnd, SetGem) values ( @PID, @IID, 32, 0, 8, 1, 100, 100, 0)
set @IID = @IID + 1
insert into Item (PID, IID, [Index], Prefix, Info, Num, MaxEnd, CurEnd, SetGem) values ( @PID, @IID, 33, 0, 8, 1, 100, 100, 0)
set @IID = @IID + 1
insert into Item (PID, IID, [Index], Prefix, Info, Num, MaxEnd, CurEnd, SetGem) values ( @PID, @IID, 34, 0, 8, 1, 100, 100, 0)
set @IID = @IID + 1
insert into Item (PID, IID, [Index], Prefix, Info, Num, MaxEnd, CurEnd, SetGem) values ( @PID, @IID, 35, 0, 8, 1, 100, 100, 0)
set @IID = @IID + 1
insert into Item (PID, IID, [Index], Prefix, Info, Num, MaxEnd, CurEnd, SetGem) values ( @PID, @IID, 36, 0, 8, 1, 100, 100, 0)
set @IID = @IID + 1
insert into Item (PID, IID, [Index], Prefix, Info, Num, MaxEnd, CurEnd, SetGem) values ( @PID, @IID, 37, 0, 8, 1, 100, 100, 0)
set @IID = @IID + 1
insert into Item (PID, IID, [Index], Prefix, Info, Num, MaxEnd, CurEnd, SetGem) values ( @PID, @IID, 38, 0, 8, 1, 100, 100, 0)
set @IID = @IID + 1
insert into Item (PID, IID, [Index], Prefix, Info, Num, MaxEnd, CurEnd, SetGem) values ( @PID, @IID, 39, 0, 8, 1, 100, 100, 0)
set @IID = @IID + 1
insert into Item (PID, IID, [Index], Prefix, Info, Num, MaxEnd, CurEnd, SetGem) values ( @PID, @IID, 40, 0, 8, 1, 100, 100, 0)
set @IID = @IID + 1
insert into Item (PID, IID, [Index], Prefix, Info, Num, MaxEnd, CurEnd, SetGem) values ( @PID, @IID, 41, 0, 8, 1, 100, 100, 0)
set @IID = @IID + 1
insert into Item (PID, IID, [Index], Prefix, Info, Num, MaxEnd, CurEnd, SetGem) values ( @PID, @IID, 42, 0, 8, 1, 100, 100, 0)
set @IID = @IID + 1
insert into Item (PID, IID, [Index], Prefix, Info, Num, MaxEnd, CurEnd, SetGem) values ( @PID, @IID, 43, 0, 8, 1, 100, 100, 0)
set @IID = @IID + 1
insert into Item (PID, IID, [Index], Prefix, Info, Num, MaxEnd, CurEnd, SetGem) values ( @PID, @IID, 44, 0, 8, 1, 100, 100, 0)
set @IID = @IID + 1
insert into Item (PID, IID, [Index], Prefix, Info, Num, MaxEnd, CurEnd, SetGem) values ( @PID, @IID, 45, 0, 8, 1, 100, 100, 0)
set @IID = @IID + 1
insert into Item (PID, IID, [Index], Prefix, Info, Num, MaxEnd, CurEnd, SetGem) values ( @PID, @IID, 46, 0, 8, 1, 100, 100, 0)
set @IID = @IID + 1
insert into Item (PID, IID, [Index], Prefix, Info, Num, MaxEnd, CurEnd, SetGem) values ( @PID, @IID, 54, 0, 8, 1, 100, 100, 0)
set @IID = @IID + 1
insert into Item (PID, IID, [Index], Prefix, Info, Num, MaxEnd, CurEnd, SetGem) values ( @PID, @IID, 55, 0, 8, 1, 100, 100, 0)
set @IID = @IID + 1
insert into Item (PID, IID, [Index], Prefix, Info, Num, MaxEnd, CurEnd, SetGem) values ( @PID, @IID, 56, 0, 8, 1, 100, 100, 0)
set @IID = @IID + 1
insert into Item (PID, IID, [Index], Prefix, Info, Num, MaxEnd, CurEnd, SetGem) values ( @PID, @IID, 57, 0, 8, 1, 100, 100, 0)
set @IID = @IID + 1
insert into Item (PID, IID, [Index], Prefix, Info, Num, MaxEnd, CurEnd, SetGem) values ( @PID, @IID, 58, 0, 8, 1, 100, 100, 0)
set @IID = @IID + 1
insert into Item (PID, IID, [Index], Prefix, Info, Num, MaxEnd, CurEnd, SetGem) values ( @PID, @IID, 62, 0, 8, 1, 100, 100, 0)
set @IID = @IID + 1
insert into Item (PID, IID, [Index], Prefix, Info, Num, MaxEnd, CurEnd, SetGem) values ( @PID, @IID, 63, 0, 8, 1, 100, 100, 0)
set @IID = @IID + 1
insert into Item (PID, IID, [Index], Prefix, Info, Num, MaxEnd, CurEnd, SetGem) values ( @PID, @IID, 64, 0, 8, 1, 100, 100, 0)
end -- else if end

fetch next from Player_Cursor into @PID
end -- while end

close Player_Cursor
deallocate Player_Cursor

GO
这个是往这个表里添加东西的时候存储过程。
根据徐强大哥的分析是往里面添加的时候iid取的是maxiid的数据，而我如果直接往里面添加东西的时候第一个是空的，所以它将自动取int数据类型的最小数，即-2147483647，所以就会产生负数，强哥给我的解决方法是，在添加数据之前插入一段iid为1的代码，那么iid在取值的时候取到的就是这个1了。而不是从-2147483647开始的。然后将iid设置成主键，所有的iid取值就会根据maxiid的数值来取了。
问题解决，谢谢大家！


2007-04-11 09:38
初学Delphi
Rank: 3Rank: 3
等　级：新手上路
威　望：9
帖　子：330
专家分：0
注　册：2006-4-4
第 9 楼
 得分:0 
汗死，应该不是这个意思吧，如果插进去的第一个是null那么 取最大值还是null
+1还是null永远都是null了
2007-04-11 14:41



参与讨论请移步原网站贴子：https://bbs.bccn.net/thread-130282-1-1.html




关于我们 | 广告合作 | 编程中国 | 清除Cookies | TOP | 手机版

编程中国 版权所有，并保留所有权利。
Powered by Discuz, Processed in 0.043347 second(s), 9 queries.
Copyright©2004-2024, BCCN.NET, All Rights Reserved