一个SQL问题 [英] An SQL question
问题描述
这更像是一个SQL问题,但我想知道如何在
SQL Server中执行此操作。
在给定的表格中我有两列重要的。第一个是帐号
号(它是一个外键)。另一个是agent_id。组合
必须是唯一的。我想做的是为给定的帐号自动增加agent_id
。示例:
帐号代理ID
1000 1
1000 2
1100 1
1200 1
1200 2
1200 3
当我向帐号1200添加新代理时,我想用agent_id自动提出
,我知道我可以选择
账号并返回agent_id的MAX。然后我可以增加
值,并使用该对创建新的代理。但是,我想知道你是否可以在SQL中自动执行此操作?
雪莉
This is more of an SQL question, but I would like to know how to do it in
SQL Server.
In a given table I have two columns of importance. The first is account
number (and it is a foreign key). The other is agent_id. The combination
must be unique. What I would like to do is to autoincrement the agent_id
for a given account number. Example:
Account Number Agent ID
1000 1
1000 2
1100 1
1200 1
1200 2
1200 3
When I add a new agent to account number 1200, I would like it to come up
automatically with 2 for the agent_id.. I know I could do a select on
account number and return MAX of agent_id. I could then increment that
value and use that pair for new agent creation. However, I wonder if thee
is a way to do that automatically in SQL?
Shelly
推荐答案
好吧,要获得lastAgentID号码,你可以
从代理商中选择最大值(AgentID)
"雪莉" < sh ************ @ asap-consult.comwrote in message
news:13 ************* @ corp。 supernews.com ...
Well, to get the lastAgentID number you could
Select max(AgentID) From Agent
"Shelly" <sh************@asap-consult.comwrote in message
news:13*************@corp.supernews.com...
这是一个更多的SQL问题,但我想知道如何在
SQL中做到这一点服务器。
在给定的表中,我有两列重要性。第一个是帐号
号(它是一个外键)。另一个是agent_id。组合
必须是唯一的。我想做的是为给定的帐号自动增加agent_id
。示例:
帐号代理ID
1000 1
1000 2
1100 1
1200 1
1200 2
1200 3
当我向帐号1200添加新代理时,我想用agent_id自动提出
,我知道我可以选择
账号并返回agent_id的MAX。然后我可以增加
值,并使用该对创建新的代理。但是,我想知道你是否可以在SQL中自动执行此操作?
Shelly
This is more of an SQL question, but I would like to know how to do it in
SQL Server.
In a given table I have two columns of importance. The first is account
number (and it is a foreign key). The other is agent_id. The combination
must be unique. What I would like to do is to autoincrement the agent_id
for a given account number. Example:
Account Number Agent ID
1000 1
1000 2
1100 1
1200 1
1200 2
1200 3
When I add a new agent to account number 1200, I would like it to come up
automatically with 2 for the agent_id.. I know I could do a select on
account number and return MAX of agent_id. I could then increment that
value and use that pair for new agent creation. However, I wonder if thee
is a way to do that automatically in SQL?
Shelly
" Just Me" < news.microsoft.com写信息
新闻:Og ************** @ TK2MSFTNGP02.phx.gbl ...
"Just Me" <news.microsoft.comwrote in message
news:Og**************@TK2MSFTNGP02.phx.gbl...
好吧,要获得lastAgentID号码,你可以
从代理商中选择最大值(AgentID)
Well, to get the lastAgentID number you could
Select max(AgentID) From Agent
就像我说的,我知道我可以做到这一点(当然,添加条款WHERE
accountNumber = theccountNumber),但我的问题是SQL可以做到这一点
自动将insert语句放入Agent表中?如果可以,那么
我可以执行一个ExecuteScalar并从插入查询中返回该值
,而不必连续两次查询并确定种族的可能性
条件,其他人在
同时将代理添加到同一帐户。
Shelly
Like I said, I know I can do this (adding, of course, the clause WHERE
accountNumber=theaccountNumber) , but my question is can SQL do this
automatically on the insert statement into the Agent table? If it can, then
I can do an ExecuteScalar and return that value from the insert query
without having to do two queries in succession with the possiblity of a race
condition where someone else is adding an agent to the same account at the
same time.
Shelly
>
" Shelly" < sh ************ @ asap-consult.comwrote in message
news:13 ************* @ corp。 supernews.com ...
>
"Shelly" <sh************@asap-consult.comwrote in message
news:13*************@corp.supernews.com...
>这是一个SQL问题,但我想知道如何在SQL Server中执行此操作。
在给定的表中,我有两列重要性。第一个是帐号
号(它是一个外键)。另一个是agent_id。
组合必须是唯一的。我想做的是自动增加给定帐号的agent_id。示例:
帐号代理ID
1000 1
1000 2
1100 1
1200 1
1200 2
1200 3
当我向帐号1200添加一个新的代理时,我希望它自动出现
给2为agent_id ..我知道我可以选择
帐号并返回agent_id的MAX。然后我可以增加该值并使用该对来创建新的代理。但是,我想知道你是否可以在SQL中自动执行此操作?
Shelly
>This is more of an SQL question, but I would like to know how to do it in
SQL Server.
In a given table I have two columns of importance. The first is account
number (and it is a foreign key). The other is agent_id. The
combination must be unique. What I would like to do is to autoincrement
the agent_id for a given account number. Example:
Account Number Agent ID
1000 1
1000 2
1100 1
1200 1
1200 2
1200 3
When I add a new agent to account number 1200, I would like it to come up
automatically with 2 for the agent_id.. I know I could do a select on
account number and return MAX of agent_id. I could then increment that
value and use that pair for new agent creation. However, I wonder if
thee is a way to do that automatically in SQL?
Shelly
好的,现在我明白了你想要实现的目标。我认为答案是
没有。
" Shelly" < sh ************ @ asap-consult.comwrote in message
news:13 ************* @ corp。 supernews.com ...
OK, now I understand what you are trying to acheive. I think the answer is
no there is not.
"Shelly" <sh************@asap-consult.comwrote in message
news:13*************@corp.supernews.com...
>
" Just Me" < news.microsoft.com写信息
新闻:Og ************** @ TK2MSFTNGP02.phx.gbl ...
>
"Just Me" <news.microsoft.comwrote in message
news:Og**************@TK2MSFTNGP02.phx.gbl...
>嗯,要获得lastAgentID号码,你可以
从代理商中选择max(AgentID)
>Well, to get the lastAgentID number you could
Select max(AgentID) From Agent
就像我说的,我知道我可以做到这一点(当然,添加条款WHERE
accountNumber = theccountNumber),但我的问题是SQL可以做到这一点
自动在insert语句中进入Agent表?如果可以的话,那么我可以执行一个ExecuteScalar并从插入查询中返回该值
,而不必连续执行两个查询,并且可以使用
比赛条件,其他人在同一个账户中添加代理商
。
Shelly
Like I said, I know I can do this (adding, of course, the clause WHERE
accountNumber=theaccountNumber) , but my question is can SQL do this
automatically on the insert statement into the Agent table? If it can,
then I can do an ExecuteScalar and return that value from the insert query
without having to do two queries in succession with the possiblity of a
race condition where someone else is adding an agent to the same account
at the same time.
Shelly
>>
" Shelly" < sh ************ @ asap-consult.comwrote in message
新闻:13 ************* @ corp.supernews.com。 ..
>>
"Shelly" <sh************@asap-consult.comwrote in message
news:13*************@corp.supernews.com...
>>这更像是一个SQL问题,但我想知道如何在SQL Server中这样做。
在给定的表中,我有两列重要性。第一个是帐号
号(它是一个外键)。另一个是agent_id。
组合必须是唯一的。我想做的是自动增加给定帐号的agent_id。示例:
帐号代理ID
1000 1
1000 2
1100 1
1200 1
1200 2
1200 3
当我向帐号1200添加一个新的代理时,我希望它自动以2为agent_id自动启动..我知道我可以选择
帐号并返回agent_id的MAX。然后我可以增加该值并使用该对来创建新的代理。但是,我想知道如果你是一种在SQL中自动执行此操作的方法吗?
Shelly
>>This is more of an SQL question, but I would like to know how to do it
in SQL Server.
In a given table I have two columns of importance. The first is account
number (and it is a foreign key). The other is agent_id. The
combination must be unique. What I would like to do is to autoincrement
the agent_id for a given account number. Example:
Account Number Agent ID
1000 1
1000 2
1100 1
1200 1
1200 2
1200 3
When I add a new agent to account number 1200, I would like it to come
up automatically with 2 for the agent_id.. I know I could do a select
on account number and return MAX of agent_id. I could then increment
that value and use that pair for new agent creation. However, I wonder
if thee is a way to do that automatically in SQL?
Shelly
这篇关于一个SQL问题的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!