获取max + 1而不删除SQL Server中的零 [英] Get max + 1 without removing zeroes in SQL server

问题描述

您好，

我有一个varchar字段包含此格式为00001，00002等的数字
当我尝试使用Max获取下一个数字时
（字段）+ 1我得到一个整数3例如。

如何获得resutl00003而不是3？



谢谢。



我尝试过：



回复修改评论。删除评论。

这是一个工作示例我刚刚想出来，但我认为必须有一个更简单的方法：

SELECT TOP（1）{fn REPEAT （0，LEN（ItemId） - LEN（MAX（ItemId）+ 1））} + CAST（MAX（ItemId）+ 1 AS varchar（7））AS Expr1

FROM Items

GROUP BY ItemId

ORDER BY ItemId DESC

最后一个查询给出了正确的结果0004916

解决方案

为此，您需要将其转换为整数，递增，并将其转换回字符串：

  SELECT   RIGHT （'  00000' +  CONVERT （ VARCHAR ，MAX（ CONVERT （ INT ，Field））+  1 ）， 5 ） FROM  MyTable 

但是......你的IDENTITY字段是整数，让SQL处理增量，然后将其转换为前导零字符串，你会好得多当你需要它时。

一种方法是使用Computed列：

  CREATE   TABLE  [dbo]。[MyTable]（
 [Id] [ int ]  IDENTITY （ 1 ， 1  ） NOT   NULL ，
 [Field]  AS （ RIGHT （'  00000' + CONVERT（[ VARCHAR ]，[Id]，（ 0 ）），（ 5 ））），
 [ Desc ] [ nchar ]（ 10 ） NOT   NULL  
） ON  [ PRIMARY ] 
  GO  

这样，当您插入一行时，将为您创建最新值，并且前导零sting版本将自动创建。

你发布了任何代码 - 所以它有点难以帮助...我会说这个，我不断惊讶于今天的计算机科学家不区分



a）数字如何存储在内存中

b）数字如何存储在数据库中

c）如何为人类消费提供一个数字 - 在这种情况下，我的意思是领先零等



看看如何：使用前导零填充数字 [ ^ ]

< blockquote>你正在做的事情的问题是它可以在多用户环境中失败。



你必须使用SELECT来获得当前的最高值然后INSERT将下一个数字放入表中。如果第二个客户端在第一个客户端进入INSERT之前执行SELECT会出现问题？你突然有两个客户使用相同的号码。



使用自动递增ID字段。前导零是一个用户界面而不是数据库中的ID号。

Hello,
I have a varchar field contains numbers in this format "00001" , "00002" etc
when I try to get the next number by using Max(Field) + 1 I get an integer "3" for example.
how can I get the resutl "00003" instead of "3"?

Thank you.

What I have tried:

Reply Modify the comment. Delete the comment.
here's an working example I've just figure it out, but i think there's must be an easier way:
SELECT TOP (1) { fn REPEAT(0, LEN(ItemId) - LEN(MAX(ItemId) + 1)) } + CAST(MAX(ItemId) + 1 AS varchar(7)) AS Expr1
FROM Items
GROUP BY ItemId
ORDER BY ItemId DESC
the last query gives the correct result "0004916"

解决方案

To do that, you need to convert it to an integer, increment it, and convert it back to a string:

SELECT RIGHT('00000' + CONVERT(VARCHAR, MAX(CONVERT(INT,Field)) + 1), 5) FROM MyTable

But...you're a lot better off having an IDENTITY field which is integer and letting SQL handle the increment, then converting it to a leading-zero string when you need it.
One way to do this is to use a Computed column:

CREATE TABLE [dbo].[MyTable](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Field]  AS (RIGHT('00000'+CONVERT([VARCHAR],[Id],(0)),(5))),
	[Desc] [nchar](10) NOT NULL
) ON [PRIMARY]
GO

That way, the "latest value" will be created for you when you insert a row, and the leading zero sting version will be automatically created.

you havnt posted any code - so its a bit hard to help ... I'll say this, Im constantly amazed that computer scientists today dont distinguish between

a) how a number is stored in memory
b) how a number is stored in a database
c) how a number may be presented for human consumption - in this case, I mean leading zeros etc

have a look at How to: Pad a Number with Leading Zeros[^]

The problem with what you're doing is that it CAN and WILL fail in a multiuser environment.

You have to use a SELECT to get the current top value and then an INSERT to put the next number into the table. The problem is what if a second client executes the SELECT before the first client gets to the INSERT? You suddenly have two clients using the same number.

Use auto-incrementing ID fields instead. Leading zeros is more of a user interface thing instead of an ID number in the database.

这篇关于获取max + 1而不删除SQL Server中的零的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！