为select查询的结果设置一个变量

How do I set a variable to the result of select query without using a stored procedure?

我如何在不使用存储过程的情况下将一个变量设置为select查询的结果?

I want to do something like: OOdate DATETIME

我想做一些事情，比如:OOdate DATETIME

SET OOdate = Select OO.Date 
FROM OLAP.OutageHours as OO
WHERE OO.OutageID = 1

Then I want to use OOdate in this query:

然后我想在这个查询中使用OOdate:

SELECT COUNT(FF.HALID) from Outages.FaultsInOutages as OFIO
INNER join Faults.Faults as FF ON FF.HALID = OFIO.HALID
WHERE CONVERT(VARCHAR(10),OO.Date,126) = CONVERT(VARCHAR(10),FF.FaultDate,126)) 
AND
OFIO.OutageID = 1

6 个解决方案

#1

You can use something like

你可以用类似的东西

SET @cnt = (SELECT COUNT(*) FROM User)

或

SELECT @cnt = (COUNT(*) FROM User)

For this to work the SELECT must return a single column and a single result and the SELECT statement must be in parenthesis.

为此，选择必须返回单个列和单个结果，而SELECT语句必须在括号中。

Edit: Have you tried something like this?

编辑:你试过这样的东西吗?

DECLARE @OOdate DATETIME

SET @OOdate = Select OO.Date from OLAP.OutageHours as OO where OO.OutageID = 1

Select COUNT(FF.HALID) 
from Outages.FaultsInOutages as OFIO 
inner join Faults.Faults as FF 
    ON FF.HALID = OFIO.HALID 
WHERE @OODate = FF.FaultDate
    AND OFIO.OutageID = 1

#2

-- Sql Server 2005 Management studio

——Sql Server 2005 Management studio


use Master
go
DECLARE @MyVar bigint
SET @myvar = (SELECT count(*) FROM spt_values);
SELECT @myvar

Result: 2346 (in my db)

-- Note: @myvar = @Myvar

——注意:@myvar = @myvar

#3

You could use:

您可以使用:

declare @foo as nvarchar(25)

select @foo = 'bar'

select @foo

#4

You could also just put the first SELECT in a subquery. Since most optimizers will fold it into a constant anyway, there should not be a performance hit on this.

您还可以将第一个SELECT放在子查询中。由于大多数优化器都会将它折叠成一个常量，因此不应该对它造成性能损失。

Incidentally, since you are using a predicate like this:

顺便说一句，既然你用的谓词是这样的:

CONVERT(...) = CONVERT(...)

that predicate expression cannot be optimized properly or use indexes on the columns reference by the CONVERT() function.

不能正确地优化该谓词表达式，也不能使用CONVERT()函数对列引用的索引。

Here is one way to make the original query somewhat better:

有一种方法可以使原始查询变得更好:

DECLARE @ooDate datetime
SELECT @ooDate = OO.Date FROM OLAP.OutageHours AS OO where OO.OutageID = 1

SELECT 
  COUNT(FF.HALID)
FROM
  Outages.FaultsInOutages AS OFIO 
  INNER JOIN Faults.Faults as FF ON 
    FF.HALID = OFIO.HALID 
WHERE
  FF.FaultDate >= @ooDate AND
  FF.FaultDate < DATEADD(day, 1, @ooDate) AND
  OFIO.OutageID = 1

This version could leverage in index that involved FaultDate, and achieves the same goal.

该版本可以在涉及故障日期的索引中使用，并实现相同的目标。

Here it is, rewritten to use a subquery to avoid the variable declaration and subsequent SELECT.

这里，重写为使用子查询来避免变量声明和后续选择。

SELECT 
  COUNT(FF.HALID)
FROM
  Outages.FaultsInOutages AS OFIO 
  INNER JOIN Faults.Faults as FF ON 
    FF.HALID = OFIO.HALID 
WHERE
  CONVERT(varchar(10), FF.FaultDate, 126) = (SELECT CONVERT(varchar(10), OO.Date, 126) FROM OLAP.OutageHours AS OO where OO.OutageID = 1) AND
  OFIO.OutageID = 1

Note that this approach has the same index usage issue as the original, because of the use of CONVERT() on FF.FaultDate. This could be remedied by adding the subquery twice, but you would be better served with the variable approach in this case. This last version is only for demonstration.

注意，由于在FF.FaultDate上使用了CONVERT()，所以这种方法的索引使用问题与原始方法相同。可以通过两次添加子查询来解决这个问题，但是在这种情况下，使用变量方法会更好。最后一个版本仅供演示。

Regards.

的问候。

#5

This will work for original question asked:

这将适用于最初提出的问题:

DECLARE @Result INT;
SELECT @Result = COUNT(*)
FROM  TableName
WHERE Condition

#6

What do you mean exactly? Do you want to reuse the result of your query for an other query?

你确切的意思是什么?您是否希望将查询结果重用到另一个查询?

In that case, why don't you combine both queries, by making the second query search inside the results of the first one (SELECT xxx in (SELECT yyy...)

在这种情况下，为什么不将两个查询结合起来，在第一个查询的结果中进行第二个查询搜索(选择xxx In(选择yyyy…)

#1