如何在SQL中从子查询结果中设置变量1值

I'm not so good with SQL, so help me please. In my stored procedure I'm getting a parameter. Then, based on this parameter I want to get ID from another table and put this ID into my another variable, but the problem is that ID could be multiple based on this parameter.

我不太擅长SQL，所以请帮帮我。在我的存储过程中，我得到一个参数。然后，基于这个参数，我想从另一个表中获取ID，并将这个ID放到另一个变量中，但问题是ID可以基于这个参数进行多个操作。

DECLARE @RateID int;

SET @RateID = (
    Select [dbo].[Rate].RateID
    from [dbo].[Rate]
    Where [dbo].[Rate].Rate = 160       
)

Here I'm getting the error, because Subquery returned more than 1 value How to get the first value from subquery result and set to the @RateID?

这里我得到了错误，因为子查询返回了大于1的值如何从子查询结果中获取第一个值并将其设置为@RateID?

4 个解决方案

#1

You may want to try it like this:

你可以这样尝试:

DECLARE @RateID int;

SET @RateID = (
    Select Top 1 [dbo].[Rate].RateID
    from [dbo].[Rate]
    Where [dbo].[Rate].Rate = 160       
)

As in your present query there may be the case that you table has more than 1 row which satisfies the condition Rate = 160, so the select query will return more than 1 row which you cannot store in a single variable.

在当前查询中，可能会出现这样的情况:您的表有超过一行，满足条件率= 160，因此select查询将返回多于一行，您不能将其存储在单个变量中。

However in MYSQL you have to use LIMIT 1 as TOP 1 will not work in MYSQL.

但是在MYSQL中，必须使用LIMIT 1，因为TOP 1在MYSQL中不能工作。

#2

If you only want the first result you can do

如果你只想要你能做的第一个结果。

Select TOP 1 [dbo].[Rate].RateID

in place of

在的地方

Select [dbo].[Rate].RateID

#3

You can use Top query like SET @RateID = ( Select Top 1 [dbo].[Rate].RateID from [dbo].[Rate] Where [dbo].[Rate].Rate = 160 )

您可以使用Top查询，如SET @RateID =(选择Top 1 [dbo].[Rate]。RateID从[dbo]。(率),[dbo]。(率)。率= 160)

#4

You can use the following syntax:

您可以使用以下语法:

Select top 1
  @RateId = RateId
from
  Rate
where
  Rate.Rate = @Rate
order by
  RateId

If you want the last, you can change the order by to:

如果你想要最后一个，你可以将订单改为:

order by
    RateId Desc

Sql Fiddle Example

Sql小提琴的例子

#1