mysql报错：子查询返回值大于一

azure10492

于 2022-07-28 11:43:24 发布

阅读量1.4k

点赞数

分类专栏：学习日记文章标签： java mysql

本文链接：https://blog.csdn.net/azure10492/article/details/126031336

版权

学习日记专栏收录该内容

54 篇文章 0 订阅

订阅专栏

运行某个查询，报错：Subquery returns more than 1 row 代表你的子查询的筛选条件有问题，查出来的行数大于一，直接看子查询，单独摘出来在运行，排查错误即可。

例子：

SELECT id,city,hose,emp_id,emp_name,`name`,(SELECT`name` FROM `today` c1 WHERE type = 0
        AND `name` = 'yyyy' AND c.emp_id = c1.emp_id ) as name1,
        (IF( (SELECT	finish 	FROM  `today` c1 	WHERE	type = 0 	AND `name` = 'yyyy' 	AND c.emp_id = c1.emp_id AND `status` = 0 and date(#{date}) = date(gmt_create)) = 0
        AND
        (SELECT	finish 	FROM	`cnpc_today` c1 WHERE	type = 0 AND `name` = 'xxxx' AND c.emp_id = c1.emp_id AND `status` = 0 and date(#{date}) = date(gmt_create)) =0,0,1)) AS finish
        FROM	`today` c WHERE	type = 0 	AND `name` = 'xxxx' 	AND `status` = 0
        <if test="date != null">
            and date(#{date}) = date(gmt_create)
        </if>
        ORDER BY city

这个查询里

(IF( (SELECT finish FROM `today` c1 WHERE type = 0 AND `name` = 'yyyy' AND c.emp_id = c1.emp_id AND `status` = 0 and date(#{date}) = date(gmt_create)) = 0
AND
(SELECT finish FROM `cnpc_today` c1 WHERE type = 0 AND `name` = 'xxxx' AND c.emp_id = c1.emp_id AND `status` = 0 and date(#{date}) = date(gmt_create)) =0,0,1)) AS finish

这个子查询如果没有时间这个筛选，就会报错：Subquery returns more than 1 row

根据自己的需求和环境加上确定的筛选条件可以解决这个错误