SQL查询连接表 - 子查询返回的值超过1
我试图从alldocs(其中siteid =东西和结帐不为空)的所有文件。 Alldocs有一个字段调用checkoutuserid,我需要从userinfo表中获取名称。 UserInfo可能有相同的ID重复,这就是为什么我添加站点ID。我收到以下查询的错误。 “子查询返回多个值)SQL查询连接表 - 子查询返回的值超过1
SELECT AllDocs.LeafName
, AllDocs.CheckoutDate
, UserInfo.tp_Title AS [User Name]
, UserInfo.tp_Login AS [User ID]
, UserInfo.tp_Email
FROM AllDocs
INNER JOIN UserInfo ON AllDocs.CheckoutUserId = (
SELECT UserInfo.tp_ID
FROM UserInfo
WHERE UserInfo.tp_SiteID = 'E2FF98A7-B719-428D-8C30-856F08989691'
)
WHERE (AllDocs.SiteId = 'E2FF98A7-B719-428D-8C30-856F08989691')
AND (AllDocs.CheckoutUserId IS NOT NULL)
ORDER BY AllDocs.LeafName
我加入,我已经加了第三个表来查询以下工作代码。在正常使用下。
use WSS_Content_MMRHome1
SELECT distinct AllDocs.LeafName AS [File Name], AllDocs.dirName AS [Path], AllDocs.CheckoutDate,
UserInfo.tp_Title AS [User Name], UserInfo.tp_Login AS [User ID],
UserInfo.tp_Email AS [User Email], AllLists.tp_Title AS [List Name], ('http://inside.nv.com/'+AllDocs.DirName+'/'+AllDocs.LeafName) AS URL
FROM AllDocs
INNER JOIN UserInfo ON AllDocs.CheckoutUserId = UserInfo.tp_ID AND UserInfo.tp_SiteID = AllDocs.SiteId
INNER JOIN AllLists ON AllDocs.ListId = AllLists.tp_ID
WHERE AllDocs.SiteId = 'D36CCEA0-5351-4AEC-8B83-ACA2439CF38B'
AND AllDocs.CheckoutUserId IS NOT NULL
ORDER BY AllDocs.LeafName
无需子查询,只要将附加标准到join
(或包括where
子句中):
SELECT distinct AllDocs.LeafName, AllDocs.CheckoutDate,
UserInfo.tp_Title AS [User Name], UserInfo.tp_Login AS [User ID],
UserInfo.tp_Email
FROM AllDocs INNER JOIN
UserInfo ON AllDocs.CheckoutUserId = UserInfo.tp_ID
AND UserInfo.tp_SiteID = 'E2FF98A7-B719-428D-8C30-856F08989691'
WHERE AllDocs.SiteId = 'E2FF98A7-B719-428D-8C30-856F08989691'
AND AllDocs.CheckoutUserId IS NOT NULL
ORDER BY AllDocs.LeafName
其实看起来像是同一个网站ID?如果是的话,只需加入该领域:
...
FROM AllDocs INNER JOIN
UserInfo ON AllDocs.CheckoutUserId = UserInfo.tp_ID
AND UserInfo.tp_SiteID = AllDocs.SiteId
WHERE AllDocs.SiteId = 'E2FF98A7-B719-428D-8C30-856F08989691'
AND AllDocs.CheckoutUserId IS NOT NULL
ORDER BY AllDocs.LeafName
可能是真的,但是这*会产生重复的行 –
@GordonLinoff - true,depends在这种情况下可能会有不同的结果...... – sgeddes
这给了我alldocs中的重复行,但是如果我添加不同的行,它可以解决问题 –
如果它应该多于一个值,请使用'in'而不是'=' - 或者修复您的sql以仅获取一个值。你也可以使用'和exists(选择1 ...'结构,它通常对性能更好,并且nulls不会导致问题。 –