子查询执行多行，我的查询不同 - Subquery executes more than one row and my query is different

Subquery executes more than one row and my query is different

本文关键字：查询我的执行 | 更新日期: 2023-09-27

SELECT enquiry.*,
      (SELECT comm
       from comments
       where enquiry.id = comments.enquiryId
       order by time DESC
      ) as comm
FROM enquiry
where id='110' AND cmpId='3'

当我想通过删除限制来执行多行时，它说"子查询执行多行"请帮忙

您的子查询称为标量子查询，这意味着它必须有一列，最多一行。一种简单的方法是使用JOIN将结果放在多行中：

SELECT e.*, c.comments
FROM enquiry e JOIN
     comments c
     ON e.id = c.enquiryId
where e.id = 110 AND e.cmpId = 3;

或者

你可以将子查询放在 from 子句中（如下所示），这类似于连接条件。

SELECT enquiry.*, comm.comm
FROM enquiry,
      (SELECT comm
       from comments
       where enquiry.id = comments.enquiryId
       order by time DESC
      ) as comm
where id='110' AND cmpId='3'

由于查询和评论之间的关系是 1 比多（如图所示）您需要添加 GROUP_CONCAT（）函数来对所有注释进行分组，如下所示：

SELECT enquiry.*,
      (SELECT GROUP_CONCAT(comm)
       from comments
       where enquiry.id = comments.enquiryId
       order by time DESC
      ) as comm
FROM enquiry
where id='110' AND cmpId='3'