sql - 从 SQL 中的 Select 语句分配 INT 变量

Question

为什么在以下 SQL 语句中会出现语法错误:

DECLARE @Count90Day int;

SET @Count90Day =  SELECT COUNT(*) FROM Employee WHERE DateAdd(day,30,StartDate) BETWEEN
DATEADD(day,-10,GETDATE()) AND DATEADD(day,10,GETDATE())  AND Active ='Y' 

我正在尝试将从我的 Select 语句返回的行数分配给变量 @Count90Day。

Answer 1

您需要在子查询周围加上括号:

DECLARE @Count90Day int;

SET @Count90Day =  (SELECT COUNT(*)
                    FROM Employee
                    WHERE DateAdd(day,30,StartDate) BETWEEN DATEADD(day,-10,GETDATE()) AND 
                                                            DATEADD(day,10,GETDATE())  AND
                          Active ='Y'
                   );

你也可以在没有 set 的情况下这样写:

DECLARE @Count90Day int;

SELECT @Count90Day =  COUNT(*)
FROM Employee
WHERE DateAdd(day,30,StartDate) BETWEEN DATEADD(day,-10,GETDATE()) AND DATEADD(day,10,GETDATE()) AND
      Active ='Y';