获取数据库中间隔中的最后一个ID [英] get the last Id in an interval in Database

问题描述

大家好
我在数据库中有一张表，其中存储了大量数据，我将此表设计为从ID 500到1000，例如对于老师的名字，从1001到2000，对于学生的名字.
现在，我想制作一个存储过程来添加新老师，我如何告诉他去这个时间间隔并获取它的最后一个ID并在其后插入???

有人可以帮我吗?
感谢

hi all
i have a a table in database saving in it a lot of data and i design this table to be from the Id 500 to 1000 is for teacher names for example and from 1001 to 2000 for student names.
Now, i want to make a stored procedure to add new teacher , how can i tell him go to this interval and get the last id of it and insert after it???

Can any One helps me in it, please????????

Thanks

推荐答案

您基本上需要重新设计数据库架构.这不是存储值的好方法.您应该有单独的表格来存储教师和学生的记录.

You basically need to redesign your database schema. This is not a good way to store values. You should have separate tables to store teacher and student records.

我认为这是一个糟糕的设计.每当插入记录时，都应该让数据库ID自动生成，而不应该自己控制唯一标识符.

相反，您应该创建2个看起来与此相似的表.

用户信息表
ID，名称，UserTypeId
1，"Dylan"，1
2，迪兰斯老师"，2

用户类型表
ID，说明
1，学生"
2，老师"

您的UserInformation表应带有用户类型"的标识符，该标识符将描述用户的类型，例如学生或老师.这应该是用户类型"表中主键的外键

现在，您不必担心要在特定的ID范围内插入，只需添加另一个描述用户的字段即可.如果您需要添加更多用户类型(例如教授"或"Exchange学生")，则很简单-只需将记录添加到用户类型中即可开始对其进行记录.

This is a bad design in my opinion. You should let the database ID be auto generated whenever a record is inserted, you shouldn''t be trying to control the unique identifiers yourself.

Instead, you should create 2 tables that look similar to this.

UserInformation Table
Id, Name, UserTypeId
1, "Dylan", 1
2, "Dylans Teacher", 2

User Type Table
Id, Description
1, "Student"
2, "Teacher"

Your UserInformation table should take an identifier for the ''User Type'' which will describe the kind of user, e.g. student or teacher. This should be a foreign key to the primary key in ''User Type'' table

Now, you don''t have to worry about inserting within a particular range of IDs, you just add another field that describes the user. If you need to add more user types (e.g ''Professor'' or ''Exchange Student''), simple - just add the record to user types and you can start recording against it.

你好....

如果您可以更改数据库设计，请进行更改，否则以SP作为ROLE(老师或学生)作为参数(然后根据条件插入)，从@@ identity获取最后一个ID，然后使用下一个ID插入值...





希望这对您有帮助....

hi there....

if u can change ur DB design change it otherwise take parameter from SP as ROLE(teacher or student) and then insert it based on condition) take last id from @@identity and then insert value with next id...





Hope this helps u....

这篇关于获取数据库中间隔中的最后一个ID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！