嵌套 SQL Select 语句在 SQL Server 2000 上失败，在 SQL Server 2005 上正常
查询如下：
INSERT INTO @TempTable
SELECT
UserID, Name,
Address1 =
(SELECT TOP 1 [Address] FROM
(SELECT TOP 1 [Address] FROM [UserAddress] ua
INNER JOIN UserAddressOrder uo ON ua.UserID = uo.UserID
WHERE ua.UserID = u.UserID
ORDER BY uo.AddressOrder ASC) q
ORDER BY AddressOrder DESC),
Address2 =
(SELECT TOP 1 [Address] FROM
(SELECT TOP 2 [Address] FROM [UserAddress] ua
INNER JOIN UserAddressOrder uo ON ua.UserID = uo.UserID
WHERE ua.UserID = u.UserID
ORDER BY uo.AddressOrder ASC) q
ORDER BY AddressOrder DESC)
FROM
User u
在这种情况下，用户有多个地址定义，并用一个整数字段指定首选顺序。 “Address2”（第二个首选地址）尝试获取前两个首选地址，对它们进行降序排序，然后从结果中获取最上面的一个。您可能会说，只需使用子查询对 Order 字段中包含“2”的记录执行 SELECT，但 Order 值并不连续。
如何重写它以符合 SQL 2000 的限制？
非常感谢。
[编辑]
当我用实际的用户 ID 替换 WHERE 子句中的 u.UserID 时，SQL Server 2000 不会抱怨。 SQL 2000 似乎无法处理将内部引用（u.UserID）链接到外部表（User u）。
再次，抛出的错误是：
Msg 8624, Level 16, State 16, Line 24
Internal SQL Server error.
Here is the query:
INSERT INTO @TempTable
SELECT
UserID, Name,
Address1 =
(SELECT TOP 1 [Address] FROM
(SELECT TOP 1 [Address] FROM [UserAddress] ua
INNER JOIN UserAddressOrder uo ON ua.UserID = uo.UserID
WHERE ua.UserID = u.UserID
ORDER BY uo.AddressOrder ASC) q
ORDER BY AddressOrder DESC),
Address2 =
(SELECT TOP 1 [Address] FROM
(SELECT TOP 2 [Address] FROM [UserAddress] ua
INNER JOIN UserAddressOrder uo ON ua.UserID = uo.UserID
WHERE ua.UserID = u.UserID
ORDER BY uo.AddressOrder ASC) q
ORDER BY AddressOrder DESC)
FROM
User u
In this scenario, users have multiple address definitions, with an integer field specifying the preferred order. "Address2" (the second preferred address) attempts to take the top two preferred addresses, order them descending, then take the top one from the result. You might say, just use a subquery which does a SELECT for the record with "2" in the Order field, but the Order values are not contiguous.
How can this be rewritten to conform to SQL 2000's limitations?
Very much appreciated.
[EDIT]
When I replace u.UserID in the WHERE clause with an actual User ID, SQL Server 2000 doesn't complain. It seems that SQL 2000 can't handle linking the inner reference (u.UserID) to the outer table (User u).
Again, the error thrown is:
Msg 8624, Level 16, State 16, Line 24
Internal SQL Server error.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
由于问题是由于无法解析行
INNER JOIN UserAddressOrder uo ON ua.UserID = uo.UserID中的内部引用uo.UserID造成的，因此我替换了行调用接受 uo.UserID 值作为参数的函数。这并不能优雅地解决问题，但它完成了工作（尽管性能略有下降）。
Since the problem was due to not being able to resolve the inner reference
uo.UserIDin the lineINNER JOIN UserAddressOrder uo ON ua.UserID = uo.UserID, I replaced the line with a call to a function which accepts theuo.UserIDvalue as a parameter.This doesn't solve the problem elegantly, but it got the job done (although with a slight performance hit).