通过这个字面意思都能明白,就是子查询的结果不止1条。比如:
select userName,roleId,(case when orgID!='' then (SELECT name FROM organization WHERE orgID=orgID) END) as orgName,
(CASE WHEN bid!='' THEN(SELECT name FROM building WHERE bid=bid) END) AS buildName from user where userName='tj'
执行这句sql时总是报错:-- SELECT name FROM building WHERE bid=1
[Err] 1242 - Subquery returns more than 1 row
但是单独执行"SELECT name FROM building WHERE bid=1"时,确只返回一条记录。百思不得其解!!
后来抱着试试的态度,把building表的bid改成id,
select userName,roleId,(case when orgID!='' then (SELECT name FROM organization WHERE id=orgID) END) as orgName,
(CASE WHEN bid!='' THEN(SELECT name FROM building WHERE id=bid) END) AS buildName from user where userName='tj'
这样就没再报错。为了保险起见,也将organization的主键orgID 改成id。