在CTE中使用标量变量

Question

我正在审阅别人的代码，但遇到了麻烦。当我尝试执行提供的脚本时，我会得到以下错误：

必须声明标量变量"userId"；。

我试图删除第2行的分号只是为了尝试，但这给了我以下错误：

关键字"with"附近的语法不正确。如果此语句是通用表表达式、xmlnamespaces子句或更改跟踪上下文子句，则前一条语句必须以分号结束。

我对此感到非常困惑，因为这段代码在其他机器上有效，但在我的机器上无效。我知道还有其他方法可以编写这个查询，但我需要让它发挥作用是否有任何方法可以在不重写整个内容的情况下使变量按脚本中的预期工作

我在Ubuntu 18.04.4 LTS上使用Microsoft SQL Server 2019(RTM-CU8((KB45777194(-15.0.4073.23(X64(。

DECLARE @userId int
SET @userId =:user_id;

WITH bidsplacedtotal AS (
SELECT amount, user_id, auction_id
FROM bids WHERE user_id = @userId
)
,bidsreceivedtotal AS (
SELECT amount, user_id, auction_id
FROM bids WHERE auction_id IN
(SELECT id FROM auctions WHERE user_id = @userId)
)

SELECT COUNT(amount) FROM bidsplacedtotal) AS placed_total
UNION
SELECT COUNT(amount) FROM bidsreceivedtotal) AS received_total

Answer 1

试试这个代码看看它是否有效：

DECLARE @userId int
SELECT @userId = 2091

;WITH bidsplacedtotal AS (
SELECT amount, user_id, auction_id
FROM bids WHERE user_id = @userId
)
,bidsreceivedtotal AS (
SELECT amount, user_id, auction_id
FROM bids WHERE auction_id IN
(SELECT id FROM auctions WHERE user_id = @userId)
)

SELECT COUNT(amount) as CountAmount FROM bidsplacedtotal) AS placed_total
UNION
SELECT COUNT(amount) as CountAmount FROM bidsreceivedtotal) AS received_total