SELECT TOP N WITH TIES @scalar_value = count(*)

Question

我正在尝试设置标量值。需要的结果来自SELECT TOP 1 WITH TIES块。我怎样才能做得好呢?

我的做法是:

USE AdventureWorks2014
GO
CREATE FUNCTION funcGetLastFridayOrders
RETURNS SMALLINT
AS
BEGIN
    DECLARE @orders_quantity SMALLINT
    SELECT TOP 1 WITH TIES @orders_quantity = COUNT(*) 
    FROM Purchasing.PurchaseOrderHeader
    WHERE DATEPART(DAY,OrderDate) = 5
    GROUP BY OrderDate 
    ORDER BY OrderDate DESC
    RETURN @order_quantity
END

错误信息:

必须声明标量变量"@order_quantity"

与

DECLARE @var INT
SELECT @var = intValue FROM table WHERE id = 1

没问题。

Answer 1

语法错误

DECLARE @orders_quantity SMALLINT

但是在return中使用

RETURN @order_quantity

Answer 2

这只是返回行中的一个打字错误。您把"orders_quantity"写成了"order_quantity"。

这是更正后的语句:

USE AdventureWorks2014
GO
CREATE FUNCTION funcGetLastFridayOrders
RETURNS SMALLINT
AS
BEGIN
    DECLARE @orders_quantity SMALLINT
    SELECT TOP 1 WITH TIES @orders_quantity = COUNT(*) 
    FROM Purchasing.PurchaseOrderHeader
    WHERE DATEPART(DAY,OrderDate) = 5
    GROUP BY OrderDate 
    ORDER BY OrderDate DESC
    RETURN @orders_quantity
END