错误:#1242-子查询返回多于1行 | 子查询返回多于1行

本文介绍了错误:#1242-子查询返回多于1行的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我收到一个错误:#1242-当我运行此sql时，子查询返回了多于1行.

I got an error: #1242 - Subquery returns more than 1 row when i run this sql.

CREATE VIEW test
AS
  SELECT cc_name,
         COUNT() AS total,
         (SELECT COUNT(*)
            FROM bed
           WHERE respatient_id > 0
        GROUP BY cc_name) AS occupied_beds,
         (SELECT COUNT(*)
            FROM bed
           WHERE respatient_id IS NULL
        GROUP BY cc_name) AS free_beds
    FROM bed
GROUP BY cc_name;

推荐答案

问题是您的子选择返回多个值-IE:

The problem is that your subselects are returning more than one value - IE:

SELECT ...
       (SELECT COUNT(*)
          FROM bed
         WHERE respatient_id IS NULL
      GROUP BY cc_name) AS free_beds,
       ...

...将为每个cc_name返回一行，但是SQL不支持压缩子选择的结果集-因此会出现错误.

...will return a row for each cc_name, but SQL doesn't support compacting the resultset for the subselect - hence the error.

不需要子选择，可以使用以下方法对表进行一次遍历:

Don't need the subselects, this can be done using a single pass over the table using:

  SELECT b.cc_name,
         COUNT(*) AS total,
         SUM(CASE
               WHEN b.respatient_id > 0 THEN 1
               ELSE 0
             END) AS occupied_beds,
         SUM(CASE
               WHEN b.respatient_id IS NULL THEN 1
               ELSE 0
             END) AS free_beds
    FROM bed b
GROUP BY b.cc_name

这篇关于错误:#1242-子查询返回多于1行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！