从不同表获取时带有子查询的错误
从不同表获取时带有子查询的错误
提问于 2015-07-02 12:14:32
我运行了下面的查询，并出现了错误
子查询返回多行。
我已经将所有的子查询in替换为any，以及=。然而问题并没有得到解决。
SELECT *
from UserPost
where UserId = UserId_Param
or UserId = any (select UserId from UserPals
where PalUserId = UserId_Param and FriendStatus = 1)
or UserId = any (select PalUserId from UserPals
where UserId = UserId_Param and FriendStatus = 1)
and privacy = 1
and PostId not in (select PostId from UserHidePost where UserId = UserId_Param)
and Committed = 1
and Trashed = 0
union all
select *
from UserPost
where privacy = 2
and PostId = any(select PostId from PostCategory
where PalCategoryId = any (select UserPalCategoryId
from PalCategory
where userId = UserId_Param
or PalUserId = UserId_Param))
and PostId not in(select PostId from UserHidePost
where UserId = UserId_Param)
and Committed = 1
and (UserId = any (select UserId from UserPals
where PalUserId = UserId_Param and FriendStatus = 1)
or (select PalUserId from UserPals
where UserId = UserId_Param and FriendStatus = 1))
and Trashed = 0
LIMIT lim_val OFFSET lim_offset;回答 2
Stack Overflow用户
发布于 2015-07-02 12:20:34
唯一明显的子查询(对我来说)可能导致这种情况的是：
(select PalUserId from UserPals
where UserId = UserId_Param and FriendStatus = 1)也许你想要exists
exists (select PalUserId from UserPals
where UserId = UserId_Param and FriendStatus = 1
)顺便说一下，将= any ()和in混为一谈似乎有点奇怪。
Stack Overflow用户
发布于 2015-07-02 12:22:14
该子查询缺少一个any子句：
SELECT *
from UserPost
where UserId = UserId_Param
or UserId = any (select UserId from UserPals
where PalUserId = UserId_Param and FriendStatus = 1)
or UserId = any (select PalUserId from UserPals
where UserId = UserId_Param and FriendStatus = 1)
and privacy = 1
and PostId not in (select PostId from UserHidePost where UserId = UserId_Param)
and Committed = 1
and Trashed = 0
union all
select *
from UserPost
where privacy = 2
and PostId = any(select PostId from PostCategory
where PalCategoryId = any (select UserPalCategoryId
from PalCategory
where userId = UserId_Param
or PalUserId = UserId_Param))
and PostId not in(select PostId from UserHidePost
where UserId = UserId_Param)
and Committed = 1
and (UserId = any (select UserId from UserPals
where PalUserId = UserId_Param and FriendStatus = 1)
----THIS-> or (select PalUserId from UserPals
where UserId = UserId_Param and FriendStatus = 1))-- <---
and Trashed = 0
LIMIT lim_val OFFSET lim_offset;页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/31184416
复制相关文章
相似问题
腾讯云开发者
