我对SQL有点陌生,我想从这样的查询中设置一个变量:
DECLARE @value int;
SET @value = (SELECT *
FROM
(SELECT
ROW_NUMBER() OVER (ORDER BY ID ASC) AS rownumber
FROM
(SELECT ID FROM Users
WHERE ID NOT IN (SELECT UserID FROM UserFavoritePlaces))
) AS foo
WHERE rownumber = 1)
但是我得到了一个语法错误
消息102,级别15,状态1,第31行
'('附近的语法错误
我不知道自己做错了什么。
您的查询过于复杂。您可以将此方法用作:
SELECT @value = id
FROM (SELECT u.*, ROW_NUMBER() OVER (ORDER BY ID ASC) AS rownumber
FROM Users
WHERE u.ID NOT IN (SELECT ufp.UserID FROM UserFavoritePlaces ufp)
)
WHERE rownumber = 1;
但它可能更简单:
SELECT @value = id
FROM Users
WHERE u.ID NOT IN (SELECT ufp.UserID FROM UserFavoritePlaces ufp)
ORDER BY id
OFFSET 0 FETCH FIRST 1 ROW ONLY;
实际上,对于您尝试的逻辑,最简单的形式是:
SELECT @value = 1;
您正在将值设置为行号。如果这是你想要的,那么EXIST
通常是一个更好的方法。
使用稍微不同的布局,语法错误的原因变得很清楚。。。
Declare @value int;
SET @value=
(
SELECT
*
FROM
(
SELECT
ROW_NUMBER() OVER (ORDER BY ID ASC) AS rownumber
FROM
(
SELECT
ID
FROM
Users
WHERE
ID NOT IN (SELECT UserID FROM UserFavoritePlaces)
)
-- NO TABLE ALIAS HERE
)
AS foo
WHERE
rownumber =1
)
此外,您的foo
表仅具有rownumber
列。因此,即使它执行了,它也总是返回1
。
我会做…
SELECT
@value = MIN(ID)
FROM
Users
WHERE
ID NOT IN (SELECT UserID FROM UserFavoritePlaces)