问题描述

sql Subquery returns more than 1 row

SELECT
GUID,
DOCUMENTNAME,
ID,
INVITEDNAME,
USERGUID,
USERTYPE,
INVITEDGUID,
INVITETYPE,
RIGUID,
COLLEGE,
MAJORY,
CLASS,
ISFAVORITE,
ISRECOMMEND,
PUSHTYPE
FROM
(
SELECT
D.GUID,
D.DOCUMENTNAME,
RI.ID,
U.USERNAME INVITEDNAME,
u.guid userguid,
RI.INVITEDGUID,
RI.INVITETYPE,
RI.GUID RIGUID,
(
SELECT
customName
FROM
customcategorytab cc
INNER JOIN accountcategorymaptab acm ON cc.guid = acm.CUSTOMCATEGORYGUID
WHERE
type = 1
AND cc.ORGANIZATIONGUID = '821ACE1605DE402F806FD0FA26C2CFE7'
AND acm.USERACCOUNTGUID = u.guid
) college,
(
SELECT
customName
FROM
customcategorytab cc
INNER JOIN accountcategorymaptab acm ON cc.guid = acm.CUSTOMCATEGORYGUID
WHERE
type = 2
AND cc.ORGANIZATIONGUID = '821ACE1605DE402F806FD0FA26C2CFE7'
AND acm.USERACCOUNTGUID = u.guid
) majory,
(
SELECT
departname
FROM
departmenttab dt
INNER JOIN accountcategorymaptab acm ON dt.guid = acm.CUSTOMCATEGORYGUID
WHERE
type = 3
AND dt.ORGANIZATIONGUID = '821ACE1605DE402F806FD0FA26C2CFE7'
AND acm.USERACCOUNTGUID = u.guid
) class,
(
SELECT
paramtitle
FROM
systemparamtab sp
INNER JOIN useraccounttab ua ON sp.PARAMNAME = ua.USERTYPE
WHERE
sp.PARENTID = 321
AND ua.guid = '669A4A1BA01546559F27727B338EAD19'
AND ua.ORGANIZATIONGUID = '821ACE1605DE402F806FD0FA26C2CFE7'
) usertype,
CASE
WHEN f.guid IS NULL THEN
0
ELSE
1
END isFavorite,
CASE
WHEN r.guid IS NULL THEN
0
ELSE
1
END isRecommend,
o.pushType
FROM
documenttab D
RIGHT JOIN recommendinvitetab ri ON D.guid = ri.resourceguid
LEFT JOIN organizationresourcetab o ON o.resourceguid = D.GUID
LEFT JOIN useraccounttab U ON ri.userguid = U.guid
LEFT JOIN useraccounttab U1 ON ri.invitedguid = U1.guid
LEFT JOIN (
SELECT
*
FROM
favoritetab
WHERE
favoritetype = 1
AND ORGANIZATIONGUID = '821ACE1605DE402F806FD0FA26C2CFE7'
) f ON d.guid = f.resourceguid
LEFT JOIN recommendtab r ON d.guid = r.resourceguid
WHERE
1 = 1
AND ri.invitetype <> 3
AND ri.invitedguid = '669A4A1BA01546559F27727B338EAD19'
GROUP BY
invitedname,
d.DOCUMENTNAME
ORDER BY
ri.INVITETIME DESC
) invite

解决方案

 RI.GUID RIGUID,
(
SELECT
customName
FROM
customcategorytab cc
INNER JOIN accountcategorymaptab acm ON cc.guid = acm.CUSTOMCATEGORYGUID
WHERE
type = 1
AND cc.ORGANIZATIONGUID = '821ACE1605DE402F806FD0FA26C2CFE7'
AND acm.USERACCOUNTGUID = u.guid
) college

类似这种select子句当字段的，查询结果有多条，不是一条。
看业务需要，是数据问题还是重复，如果要容错，可以只取第一条记录的方式来解决。

解决方案二：

子查询，必须返回唯一数据，不能返回多个数据行

解决方案三：

加上一个top 1或者max之类的，在你的子查询上。

解决方案四：

This error occurs for statements where the subquery must return at most one row but returns multiple rows.