2 qq 34006136 qq_34006136 于 2016.03.17 16:46 提问

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

4个回答

danielinbiti
danielinbiti   Ds   Rxr 2016.03.17 17:06
 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子句当字段的,查询结果有多条,不是一条。
看业务需要,是数据问题还是重复,如果要容错,可以只取第一条记录的方式来解决。
caozhy
caozhy   Ds   Rxr 2016.03.17 17:26

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

bdmh
bdmh   Ds   Rxr 2016.03.17 17:16

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

enpterexpress
enpterexpress   2016.03.17 16:51

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

Csdn user default icon
上传中...
上传图片
插入图片
准确详细的回答,更有利于被提问者采纳,从而获得C币。复制、灌水、广告等回答会被删除,是时候展现真正的技术了!