MySQL:选择多个子查询的结果时为 "Subquery returns more than 1 row"

Question

我在运行旨在返回两个子查询结果的查询时收到“子查询返回超过 1 行”错误。为什么返回多行会成为问题，我该如何解决这个问题？

数据表和相关字段如下所示:

账户
id

session
账号
assigned_user_id
开始日期

用户
编号
姓氏

一个 session 被分配给一个帐户和一个用户。我正在尝试创建一个表格，该表格将显示每个帐户的每个分配用户的 session 数量，其中 session 开始日期在不同的日期范围内。日期范围应排列在同一行中，作为具有以下标题的表格:

帐号 |用户姓氏 | future 1-31 天的 session | future 31-60 天的 session

如图所示:

.

这是我的查询:

SELECT
(SELECT
    a.name
  FROM
    accounts AS a
  JOIN
    meetings AS m ON a.id = m.account_id
    AND date_start BETWEEN CURDATE() AND DATE_ADD(CURDATE(),INTERVAL 60 DAY)
  JOIN
    users AS u ON m.assigned_user_id = u.id
  WHERE
    m.status = 'Planned'
    AND m.deleted = 0
GROUP BY a.id, u.id) AS 'Account',
(SELECT
    u.last_name
  FROM
    accounts AS a
  JOIN
    meetings AS m ON a.id = m.account_id
    AND date_start BETWEEN CURDATE() AND DATE_ADD(CURDATE(),INTERVAL 60 DAY)
  JOIN
    users AS u ON m.assigned_user_id = u.id
WHERE
    m.status = 'Planned'
    AND m.deleted = 0
GROUP BY a.id, u.id) AS 'Name',
(SELECT
    COUNT(m.id)
  FROM
    accounts AS a
  JOIN
    meetings AS m ON a.id = m.account_id
    AND date_start BETWEEN CURDATE() AND DATE_ADD(CURDATE(),INTERVAL 30 DAY)
  JOIN
    users AS u ON m.assigned_user_id = u.id
  WHERE
    m.status = 'Planned'
    AND m.deleted = 0
GROUP BY a.id, u.id) AS 'Meetings 1-30 days',
(SELECT
    COUNT(m.id)
  FROM
    accounts AS a2
  JOIN
    meetings AS m ON a.id = m.account_id
    AND m.date_start BETWEEN DATE_ADD(CURDATE(),INTERVAL 31 DAY) AND DATE_ADD(CURDATE(),INTERVAL 60 DAY)
  JOIN
    users AS u ON m.assigned_user_id = u.id
  WHERE
    m.status = 'Planned'
    AND m.deleted = 0
GROUP BY a.id, u.id) AS 'Meetings 31-60 days'

必须将包含帐户名称和用户名称的列添加为子查询，以避免出现“Operand should contain 1 column(s)”错误。与 session 计数对应的列必须是子查询，因为连接表的任何一行都不能同时适合两个日期范围。每个子查询在单独运行时返回预期结果。但是当子查询如图所示放在一起时，我得到“Subquery returns more than 1 row”。我尝试为每个子查询分配不同的别名，但这没有帮助。

Answer 1

SQL 查询不返回嵌套结果集；因此 SELECT 子句中使用的表达式(例如子查询)不能有多个值，因为那样会“嵌套”它的值。您更有可能只需要使用条件聚合，如下所示:

SELECT a.id, u.id, a.name, u.last_name
    , COUNT(CASE WHEN m.date_start BETWEEN CURDATE() AND DATE_ADD(CURDATE(),INTERVAL 30 DAY) THEN 1 ELSE NULL END) AS `Meetings 1-30 days`
    , COUNT(CASE WHEN m.date_start BETWEEN DATE_ADD(CURDATE(),INTERVAL 31 DAY) AND DATE_ADD(CURDATE(),INTERVAL 60 DAY) THEN 1 ELSE NULL END) AS `Meetings 31-60 days`
    , COUNT(CASE WHEN  THEN 1 ELSE NULL END) AS 
FROM accounts AS a
JOIN meetings AS m ON a.id = m.account_id
JOIN users AS u ON m.assigned_user_id = u.id
WHERE m.status = 'Planned' AND m.deleted = 0
  AND m.date_start BETWEEN CURDATE() AND DATE_ADD(CURDATE(),INTERVAL 60 DAY)
GROUP BY a.id, u.id, a.name, u.last_name
;

注意:ELSE NULL 在技术上是自动的，可以省略；它只是为了清楚起见。聚合函数，例如 COUNT，忽略 NULL 值；空值影响此类函数的唯一时间是它们仅遇到空值(在这种情况下它们的结果为空)。

旁注:您可以以类似于您最初的形式继续您的查询；如果您在子查询的结果中包含分组字段，则子查询可能已连接在一起(但这会导致大量多余的帐户、 session 和用户连接)。