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   Rxr 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币。复制、灌水、广告等回答会被删除,是时候展现真正的技术了!
其他相关推荐
错误代码: 1242 Subquery returns more than 1 row
1. 错误描述1 queries executed, 0 success, 1 errors, 0 warnings查询:SELECT t.id, DATE_FORMAT( t.statisTime, '%Y-%m-%d %H:%i:%s' ) statisTime, (SELECT `id` FROM t_truck_info WHERE id = t.plateId...错误代码: 1242 S
报错 MYSQL --Subquery returns more than 1 row
Subquery returns more than 1 row表示子查询返回了多行数据 例如: select * from table1 where table1.colums=(select columns from table2) 解决方法 select * from table1 where table1.colums=any(select columns from
MYSQL --Subquery returns more than 1 row查询结果多于一行
Subquery returns more than 1 row表示子查询返回了多行数据 例如: select * from table1 where table1.colums=(select columns from table2) 解决方法 select * from table1 where table1.colums=any(select columns from
【MySQL】Subquery returns more than 1 row 解决方案
问题提出:     有如下需求,在同一张表中,当某个字段的值符合一定条件时,修改另一个字段的值。     SQL:update topic set topi_closed = 1 where topi_id = (select topi_id from (select * from topic) as b where now() > topi_stilltime and topi_closed
Subquery returns more than 1 row
Subquery returns more than 1 row表示子查询返回了多行数据 例如: select * from table1 where table1.colums=(select columns from table2) 解决方法 select * from table1 where table1.colums=any(select columns from table2)
[Err] 1242 - Subquery returns more than 1 row
1、错误描述[SQL]SELECT queryStuName(2016010101); [Err] 1242 - Subquery returns more than 1 row2、错误原因BEGIN DECLARE stuName VARCHAR(100); SET stuName = (SELECT stu_name FROM t_student_info WHERE stu_no =
报错java.sql.SQLException: Subquery returns more than 1 row
原因,子查询返回结果大于1,而外层查询使用了等于号,改为in
关于mysql错误:Subquery returns more than 1 row
mysql报错:SQLSTATE[21000]: Cardinality violation: 1242 Subquery returns more than 1 row 错误的意思是指子查询结果多于一行。报错如下: 解决方法: 以select * from table1 where table1.colums=(select columns from table2);这个s
ORA-01427:single-row subquery returns more than one row
今天开发的同事反馈一个问题,该程序已经使用一段时间,在查询昨天的数据时突然报出了错误:                       图1   OR-01427:single-row subquery returns more than one row  异常为:单行子查询返回多行 我得到了该查询页面对应的sql语句:  SELECT  ID,(SELECT NAME FROM TABL
SQL Error: ORA-01427: single-row subquery returns more than one row
http://stackoverflow.com/questions/9970480/updateerror-receiving-error-ora-01427-single-row-subquery-returns-more-than-o