在VARCHAR(900)列中获取MAX值 [英] Get MAX value in VARCHAR(900) column
问题描述
我有一个表格,我将ID存储为VARCHAR(900)这是一个主键列而不使用IDENTITY AUTOINCREMENT I have a table in which I am storing ID as VARCHAR(900) this is a primary key column and not using IDENTITY AUTOINCREMENT 这篇关于在VARCHAR(900)列中获取MAX值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
我创建了ID as IE-1-1
它继续像IE-1-85000一样,直到达到两个bigint数字的上限
现在我它有大约85000行
但它仍然显示IE-1-9作为最大值当我这样查询它
< pre lang =SQL> DECLARE @ TempICFEventID TABLE (ID SMALLINT IDENTITY ( 1 , 1 ),SplitValue VARCHAR (MAX))
INSERT INTO @ TempICFEventID VALUES (' IE-1-1')
SELECT MAX(SplitValue) FROM @ TempICFEventID
INSERT INTO @ TempICFEventID VALUES (' IE-1-2')
SELECT MAX(SplitValue) FROM @ TempICFEventID
INSERT INTO @ TempICFEventID VALUES (' IE-1-3')
SELECT MAX(SplitValue) FROM @ TempICFEventID
INSERT INTO @ TempICFEventID VALUES (' IE-1-4')
SELECT MAX(SplitValue) FROM @ TempICFEventID
INSERT INTO @ TempICFEventID VALUES (' IE-1-5')
SELECT MAX(SplitValue) FROM @ TempICFEventID
INSERT INTO @ TempICFEventID
SELECT MAX(SplitValue) FROM @ TempICFEventID
INSERT INTO @ TempICFEventID VALUES (' IE-1- 7')
SELECT MAX(SplitValue) FROM @ TempICFEventID
INSERT INTO @ TempICFEventID VALUES (' IE-1-8')
SELECT MAX(SplitValue)< span class =code-keyword> FROM @ TempICFEventID
INSERT < span class =code-keyword> INTO @ TempICFEventID VALUES ( IE-1-9')
SELECT MAX(SplitValue) FROM @ TempICFEventID
INSERT INTO @ TempICFEventID VALUES (' IE-1-10')
SELECT MAX(SplitValue) FROM @ TempICFEventID
INSERT INTO @ TempICFEventID VALUES (' IE-1-11')
SELECT MAX(SplitValue) FROM @ TempICFEventID
INSERT INTO @ TempICFEventID VALUES (' IE-1-12')
SELECT MAX(SplitValue) FROM @ TempICFEventID
INSERT INTO @ TempICFEventID VALUES (' IE-1-13')
SELECT MAX(SplitValue) FROM @ TempICFEventID
INSERT INTO @ TempICFEventID VALUES (' IE-1-14')
SELECT MAX(SplitValue) FROM @TempICFEventID
INSERT INTO @TempICFEventID VALUES (' IE-1 -15')
SELECT MAX(SplitValue) FROM @ TempICFEventID
INSERT INTO @ TempICFEventID VALUES (' IE-1-16')
SELECT MAX(SplitValue) FROM @ TempICFEventID
< span class =code-keyword> INSERT INTO @ TempICFEventID VALUES (' IE-1-17')
SELECT MAX(SplitValue) FROM @ TempICFEventID
INSERT INTO @ TempICFEventID VALUES (' IE-1-18')
SELECT MAX( SplitValue) FROM @ TempICFEventID
INSERT INTO @ TempICFEventID VALUES (< span class =code-string>' IE-1-84999')
SELECT MAX(SplitValue) FROM @ TempICFEventID
INSERT INTO @ TempICFEventID VALUES (' IE-1-85000')
SELECT MAX(SplitValue) FROM @ TempICFEventID
为什么会这样?
我尝试了什么:
这是我当前生成ID的逻辑
DECLARE @ Current VARCHAR ( 900 )
DECLARE @ Temp1 BIGINT
DECLARE @ Temp2 BIGINT
DECLARE @ Upper BIGINT
DECLARE @ Middle SMALLINT
DECLARE
SET @ Lower = -9223372036854775808
SET @ Middle = 0
SET @ Upper = 9223372036854775807
SELECT @ Current = MAX(Ref_ICFEvent_Id) FROM CaseMatrix_ICFEvent
PRINT @ Current
DECLARE @ TempICFEventID TABLE (ID SMALLINT IDENTITY ( 1 , 1 ),SplitValue VARCHAR (MAX))
IF ( @ Current IS < span class =code-keyword> NOT NULL )
BEGIN
INSERT INTO @ TempICFEventID ( SplitValue)
SELECT * FROM CaseMatrix_Split( @当前,' - ')
SELECT * FROM @ TempICFEventID
SELECT @ Temp1 = CONVERT ( BIGINT ,SplitValue) FROM @ TempICFEventID WHERE ID = 2
SELECT @ Temp2 = CONVERT ( BIGINT ,SplitValue) FROM @ TempICFEventID WHERE ID = 3
IF ( @ Temp1 IS NOT NULL AND @ Temp2 IS < span class =code-keyword> NOT NULL )
BEGIN
IF ( @ Temp1 <> @ Upper )
BEGIN
IF ( @ Temp2 <> @ Upper )
BEGIN
SET @ Temp2 = @ Temp2 + 1
SET < span class =code-sdkkeyword> @ Ref_ICFEvent_Id = ' IE - ' + < span class =code-keyword> CONVERT ( VARCHAR ( 900 ), @ Temp1 )+ ' - ' + CONVERT ( VARCHAR ( 900 ), @ Temp2 )
END
ELSE
BEGIN
SET @ Temp1 = @ Temp1 + 1
IF ( @ Temp1 <> @ Upper )
BEGIN
SET @ Temp2 = 1
SET @ Ref_ICFEvent_Id = ' IE- ' + CONVERT ( VARCHAR ( 900 ), @ Temp1 )+ ' - ' + CONVERT ( VARCHAR ( 900 ), @ Temp2 )
END
ELSE
BE GIN
SET @ Ref_ICFEvent_Id = ' MAXLIMIT'
END
END
END
ELSE
BEGIN
SET @ Ref_ICFEvent_Id = ' MAXLIMIT'
END
END
END
ELSE
BEGIN
SET @ Ref_ICFEvent_Id = ' IE-1-1'
END
按照此线程中marc_s提供的说明进行操作: sql server - 如何自动增加varchar [ ^ ]
IE-1-9
是最大值。
请记住,您将值存储为字符串,因此您正在使用字符串比较。
比较两个值, IE-1-9
和 IE-1-85000
:
'我'
vs '我'
:等于'E'
vs 'E'
:equal ' - '
vs ' - '
:等于'1'
vs '1'
:相等' - '
vs ' - '
:等于'9'
vs '8'
:'9'更大 'IE-1-9'
大于 'IE-1-8 ......'
I have created the ID as IE-1-1
It goes on like IE-1-85000 until it reaches the upper limit in both bigint numbers
Now I have around 85000 rows in it
BUT it still shows IE-1-9 as the max value when I query it like this
DECLARE @TempICFEventID TABLE(ID SMALLINT IDENTITY(1,1),SplitValue VARCHAR(MAX))
INSERT INTO @TempICFEventID VALUES ('IE-1-1')
SELECT MAX(SplitValue) FROM @TempICFEventID
INSERT INTO @TempICFEventID VALUES ('IE-1-2')
SELECT MAX(SplitValue) FROM @TempICFEventID
INSERT INTO @TempICFEventID VALUES ('IE-1-3')
SELECT MAX(SplitValue) FROM @TempICFEventID
INSERT INTO @TempICFEventID VALUES ('IE-1-4')
SELECT MAX(SplitValue) FROM @TempICFEventID
INSERT INTO @TempICFEventID VALUES ('IE-1-5')
SELECT MAX(SplitValue) FROM @TempICFEventID
INSERT INTO @TempICFEventID VALUES ('IE-1-6')
SELECT MAX(SplitValue) FROM @TempICFEventID
INSERT INTO @TempICFEventID VALUES ('IE-1-7')
SELECT MAX(SplitValue) FROM @TempICFEventID
INSERT INTO @TempICFEventID VALUES ('IE-1-8')
SELECT MAX(SplitValue) FROM @TempICFEventID
INSERT INTO @TempICFEventID VALUES ('IE-1-9')
SELECT MAX(SplitValue) FROM @TempICFEventID
INSERT INTO @TempICFEventID VALUES ('IE-1-10')
SELECT MAX(SplitValue) FROM @TempICFEventID
INSERT INTO @TempICFEventID VALUES ('IE-1-11')
SELECT MAX(SplitValue) FROM @TempICFEventID
INSERT INTO @TempICFEventID VALUES ('IE-1-12')
SELECT MAX(SplitValue) FROM @TempICFEventID
INSERT INTO @TempICFEventID VALUES ('IE-1-13')
SELECT MAX(SplitValue) FROM @TempICFEventID
INSERT INTO @TempICFEventID VALUES ('IE-1-14')
SELECT MAX(SplitValue) FROM @TempICFEventID
INSERT INTO @TempICFEventID VALUES ('IE-1-15')
SELECT MAX(SplitValue) FROM @TempICFEventID
INSERT INTO @TempICFEventID VALUES ('IE-1-16')
SELECT MAX(SplitValue) FROM @TempICFEventID
INSERT INTO @TempICFEventID VALUES ('IE-1-17')
SELECT MAX(SplitValue) FROM @TempICFEventID
INSERT INTO @TempICFEventID VALUES ('IE-1-18')
SELECT MAX(SplitValue) FROM @TempICFEventID
INSERT INTO @TempICFEventID VALUES ('IE-1-84999')
SELECT MAX(SplitValue) FROM @TempICFEventID
INSERT INTO @TempICFEventID VALUES ('IE-1-85000')
SELECT MAX(SplitValue) FROM @TempICFEventID
Why is this happening
What I have tried:
This is my current logic to generate the ID
DECLARE @Current VARCHAR(900)
DECLARE @Temp1 BIGINT
DECLARE @Temp2 BIGINT
DECLARE @Upper BIGINT
DECLARE @Middle SMALLINT
DECLARE @Lower BIGINT
SET @Lower = -9223372036854775808
SET @Middle = 0
SET @Upper = 9223372036854775807
SELECT @Current = MAX(Ref_ICFEvent_Id) FROM CaseMatrix_ICFEvent
PRINT @Current
DECLARE @TempICFEventID TABLE(ID SMALLINT IDENTITY(1,1),SplitValue VARCHAR(MAX))
IF(@Current IS NOT NULL)
BEGIN
INSERT INTO @TempICFEventID(SplitValue)
SELECT * FROM CaseMatrix_Split(@Current,'-')
SELECT * FROM @TempICFEventID
SELECT @Temp1 = CONVERT(BIGINT,SplitValue) FROM @TempICFEventID WHERE ID = 2
SELECT @Temp2 = CONVERT(BIGINT,SplitValue) FROM @TempICFEventID WHERE ID = 3
IF(@Temp1 IS NOT NULL AND @Temp2 IS NOT NULL)
BEGIN
IF(@Temp1 <> @Upper)
BEGIN
IF(@Temp2 <> @Upper)
BEGIN
SET @Temp2 = @Temp2 + 1
SET @Ref_ICFEvent_Id = 'IE-' + CONVERT(VARCHAR(900),@Temp1) + '-' + CONVERT(VARCHAR(900),@Temp2)
END
ELSE
BEGIN
SET @Temp1 = @Temp1 + 1
IF(@Temp1 <> @Upper)
BEGIN
SET @Temp2 = 1
SET @Ref_ICFEvent_Id = 'IE-' + CONVERT(VARCHAR(900),@Temp1) + '-' + CONVERT(VARCHAR(900),@Temp2)
END
ELSE
BEGIN
SET @Ref_ICFEvent_Id = 'MAXLIMIT'
END
END
END
ELSE
BEGIN
SET @Ref_ICFEvent_Id = 'MAXLIMIT'
END
END
END
ELSE
BEGIN
SET @Ref_ICFEvent_Id = 'IE-1-1'
END
Follow the instruction provided by marc_s in this thread: sql server - How to autoincrement a varchar[^]
IE-1-9
is the maximum value.
Remember, you're storing the values as strings, so you're using a string comparison.
Comparing two values, IE-1-9
and IE-1-85000
:
'I'
vs 'I'
: equal'E'
vs 'E'
: equal'-'
vs '-'
: equal'1'
vs '1'
: equal'-'
vs '-'
: equal'9'
vs '8'
: '9' is larger'IE-1-9'
is larger than 'IE-1-8...'