SQL Server 2005将变量设置为选择查询的结果

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

---

我想做类似的事情:OOdate DATETIME

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

然后我想在此查询中使用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

rslite.. 70

你可以使用类似的东西

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

要么

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

为此,SELECT必须返回单个列和单个结果,SELECT语句必须在括号中.

编辑:你尝试过这样的事吗？

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

对于您提供的第一个示例,您需要在括号中包装"select count(*)from user"或sql 2008将使用红色波浪线对齐.感谢您提供两个语法示例! (6认同)

---

Allisson Per.. 13

- Sql Server 2005管理工作室

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

Result: 2346 (in my db)

- 注意: @myvar = @Myvar

1> rslite..：

---

你可以使用类似的东西

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

要么

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

为此,SELECT必须返回单个列和单个结果,SELECT语句必须在括号中.

编辑:你尝试过这样的事吗？

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

---

对于您提供的第一个示例,您需要在括号中包装"select count(*)from user"或sql 2008将使用红色波浪线对齐.感谢您提供两个语法示例!

2> Allisson Per..：

---

- Sql Server 2005管理工作室

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

Result: 2346 (in my db)

- 注意: @myvar = @Myvar