Mysql报错

Mysql报错：1242 - Subquery returns more than 1 row
错误的意思是指子查询结果多于一行。报错如下：

mysql> select name from tb_students_info
    -> where dept_id=
    -> (select dept_id
    -> from tb_departments
    -> where dept_name='Computer');
1242 - Subquery returns more than 1 row

基于两个表信息

mysql> select * from tb_departments;
+---------+-----------+-----------+
| dept_id | dept_name | dept_type |
+---------+-----------+-----------+
|       1 | Computer  | A         |
|       2 | Chinese   | B         |
|       3 | Math      | A         |
|       4 | Computer  | A         |
|       5 | Computer  | A         |
|       6 | Math      | B         |
|       7 | Economy   | C         |
|       8 | Chinese   | B         |
|       9 | Economy   | A         |
|      10 | History   | B         |
|      11 | Economy   | A         |
+---------+-----------+-----------+
11 rows in set


mysql> select * from tb_students_info;
+---------+--------+-----+-----+--------+---------------------+
| dept_id | name   | age | sex | height | login_data          |
+---------+--------+-----+-----+--------+---------------------+
|       1 | Dany   |   1 | F   |    160 | 2015-09-10 00:00:00 |
|       2 | Green  |   3 | F   |    150 | 2015-10-22 00:00:00 |
|       3 | Henry  |   4 | M   |    166 | 2015-05-31 00:00:00 |
|       4 | Jane   |   5 | F   |    124 | 2015-01-15 00:00:00 |
|       5 | Jim    |   2 | M   |    145 | 2015-03-20 00:00:00 |
|       6 | John   |  23 | M   |    156 | 2015-04-01 00:00:00 |
|       7 | Lily   |   2 | F   |    171 | 2015-05-07 00:00:00 |
|       8 | Susan  |   4 | F   |    182 | 2015-06-10 00:00:00 |
|       9 | Thomas |   3 | M   |    196 | 2015-07-05 00:00:00 |
|      10 | Tom    |   4 | M   |    201 | 2015-08-23 00:00:00 |
+---------+--------+-----+-----+--------+---------------------+

解决方案：

方案一：limit 1

mysql> select name from tb_students_info
    where dept_id =
   (select dept_id
    from tb_departments
    where dept_name='Computer' limit 1
);
+------+
| name |
+------+
| Dany |
+------+
1 row in set

但对于多行需求，仍不满足需求。

方案二：group_concat()

mysql> select name from tb_students_info
    where dept_id =
(select group_concat(dept_id)

    from tb_departments
    where dept_name='Computer'
);
+------+
| name |
+------+
| Dany |
+------+
1 row in set

mysql> select group_concat(dept_id)
    from tb_departments
    where dept_name='Computer';
+-----------------------+
| group_concat(dept_id) |
+-----------------------+
| 1,4,5                 |
+-----------------------+
1 row in set

显示结果：还只是一个。不满足需求。
报错注入遇到ERROR 1242 (21000): Subquery returns more than 1 row解决方案
http://www.bubuko.com/infodetail-2690543.html?

方案三： any和some

• SOME相当于ANY的别名

- //ANY
select name from tb_students_info
   where dept_id =
  any
(select dept_id
   from tb_departments
   where dept_name='Computer'
);
+------+
| name |
+------+
| Dany |
| Jane |
| Jim  |
+------+
3 rows in set

//SOME
select name from tb_students_info
   where dept_id =
   some
(select dept_id
   from tb_departments
   where dept_name='Computer'
);
+------+
| name |
+------+
| Dany |
| Jane |
| Jim  |
+------+
3 rows in set

经测试，方案三 any和some方式，与本次开发需要符合。

参考例子

MySQL出错信息: Subquery returns more than 1 row及其解决方法
https://www.cnblogs.com/dmcs95/p/10777013.html
关于mysql错误：Subquery returns more than 1 row
https://blog.csdn.net/ly_dengle/article/details/78028166