如何自动增加列中的值 [英] How to auto increment a value in a colum

问题描述

您好我有一个名为ElectricityDailyMeterReadings的表，其中有一个名为MachineID的列。

我正在尝试输入机器ID为M001，M002，M003 ..等等。通常我使用的是整数值对于我的主键和我使用简单的增量方法进行自动增量



例如，

Hello i have a table called ElectricityDailyMeterReadings which has a column called MachineID..
I am trying to enter machine ID as M001, M002, M003 ..etc.. Normally i have used integer values for my primary keys and i used simple incremental methods for auto increments

example,

UPDATE mytable
  SET logins = logins + 1
  WHERE id = 12

但是因为这是一个字符串我想知道如何写它..谢谢

But since this is a string i want to know how to write it.. Thank You

推荐答案

需要定制，就是这样。下面有一个样本很少你想要什么。

使用SQL Server自定义自动生成的序列 [ ^ ]

Customization needed, that's it. Below one has few samples what do you want.
Custom Auto-Generated Sequences with SQL Server[^]

你可以这样做，假设表名是mytable

You can do it this way suppose the table name is mytable

create table mytable(
id varchar(6) primary key,
name varchar(30),
someint int identity(0,1)
);

为此创建一个触发器它看起来像这样

for this create a trigger which looks something like this

create trigger [dbo].[dummy]
on [dbo].[mytable]
for insert 
as
declare @id varchar(6);
declare @someint int;
select @id=id from inserted;
select @someint=someint from inserted;
if(@someint<10)
begin
update mytable
set id='M'+'00'+convert(varchar,@someint) where id=@id
end
else
if(@someint>=10 and @someint<100)
begin
update mytable
set id='M'+'0'+convert(varchar,@someint) where id=@id
end
else 
if(@someint>=100 and @someint<1000)
begin
update mytable
set id='M'+convert(varchar,@someint) where id=@id
end
else-- Since the transaction exceeds the limit so rollback the transation
rollback

我认为这必须解决你的问题uestion

I think this must solve your question

我上面讲过的解决方案很好，但我找到了一个更好的方法来处理你的问题，因为我觉得在前一种情况下执行的时间会更多。 />
如上所述，假设我有一个名为mytable的表，就像

The solution i have told above is good but i have found a better way to deal with your problem as i felt that execution time taken in the previous case will be bit more.
As mentioned above suppose i have a table named mytable which is like

create table mytable(
id varchar(6) primary key,
name varchar(30),
someint int identity(0,1)
);

现在为此我创建一个这样的触发器

now for this i create a trigger like this

create trigger [dbo].[dummy1]
on [dbo].[mytable]
for insert 
as
declare @id varchar(6);
declare @someint int;
select @id=id from inserted;
select @someint=someint from inserted;
update mytable
set id='M'+right('000'+convert(varchar,@someint),3) where id=@id

此工作正常

This works fine

这篇关于如何自动增加列中的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！