mysql - 错误 : #1242 - Subquery returns more than 1 row

Question

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

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; 

Answer 1

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

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

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

不需要子选择，这可以通过使用以下方法遍历表格来完成:

  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