如何用单条SQL查询关联问题表与答案表并解决子查询多列报错？

问题根源 #

你遇到的「operand should contain 1 column(s)」错误，核心原因是：你在SELECT列表里写的子查询试图返回多列（A.answer_body, A.answer_id等），但标量子查询（作为SELECT字段的子查询）只能返回单个值，数据库自然会报错。

解决方案：两种实用实现思路 #

针对「获取单个问题的所有信息+关联的所有答案」这个需求，分享两种常用的实现方式：

方案1：用LEFT JOIN + JSON聚合打包答案（推荐）

如果你的MySQL版本是8.0+，可以用JSON_AGG直接把多条答案转换成JSON数组；如果是5.7+，可以用GROUP_CONCAT结合JSON_OBJECT拼接。同时优化投票数计算，用一次聚合查询代替多次子查询，提升效率。

示例SQL（MySQL 8.0+）：

SET @question_id = 56;

SELECT
  Q.*,
  CONCAT(m.firstname, ' ', m.lastname) AS author_name,
  m.username AS u_name,
  -- 将关联答案打包成JSON数组
  JSON_AGG(
    JSON_OBJECT(
      'answerid', A.answer_id,
      'body', A.answer_body,
      'askid', A.ask_id,
      'answered_by_user_id', A.user_id
    )
  ) AS answers,
  -- 统计反对票
  COUNT(CASE WHEN v.vote_type = 0 THEN v.vote_id END) AS votes_down,
  -- 统计赞成票
  COUNT(CASE WHEN v.vote_type = 1 THEN v.vote_id END) AS votes_up
FROM QUESTIONS_TB Q
LEFT JOIN ANSWERS_TB A ON Q.question_id = A.ask_id
LEFT JOIN MAIN_TABLE m ON Q.user_id = m.user_id
LEFT JOIN VOTES_TB v ON Q.question_id = v.ask_id
WHERE Q.question_id = @question_id
GROUP BY Q.question_id, m.firstname, m.lastname, m.username;

如果是MySQL 5.7，替换JSON_AGG为GROUP_CONCAT：

SET @question_id = 56;

SELECT
  Q.*,
  CONCAT(m.firstname, ' ', m.lastname) AS author_name,
  m.username AS u_name,
  GROUP_CONCAT(
    JSON_OBJECT(
      'answerid', A.answer_id,
      'body', A.answer_body,
      'askid', A.ask_id,
      'answered_by_user_id', A.user_id
    ) SEPARATOR ','
  ) AS answers,
  COUNT(CASE WHEN v.vote_type = 0 THEN v.vote_id END) AS votes_down,
  COUNT(CASE WHEN v.vote_type = 1 THEN v.vote_id END) AS votes_up
FROM QUESTIONS_TB Q
LEFT JOIN ANSWERS_TB A ON Q.question_id = A.ask_id
LEFT JOIN MAIN_TABLE m ON Q.user_id = m.user_id
LEFT JOIN VOTES_TB v ON Q.question_id = v.ask_id
WHERE Q.question_id = @question_id
GROUP BY Q.question_id, m.firstname, m.lastname, m.username;

拿到结果后，在应用层（比如PHP）用json_decode把answers字段转换成数组，就能轻松获取所有答案。

方案2：直接LEFT JOIN返回多行，应用层合并数据

这种方式更直观，不需要聚合函数，数据库会返回多行结果（每个答案对应一行，问题信息重复），你只需要在应用层把同一个问题的答案整理到数组里即可。

示例SQL：

SET @question_id = 56;

SELECT
  Q.*,
  CONCAT(m.firstname, ' ', m.lastname) AS author_name,
  m.username AS u_name,
  A.answer_id AS answerid,
  A.answer_body AS body,
  A.ask_id AS askid,
  A.user_id AS answered_by_user_id,
  (SELECT COUNT(v.vote_id) FROM VOTES_TB v WHERE v.ask_id = Q.question_id AND v.vote_type = 0) AS votes_down,
  (SELECT COUNT(v.vote_id) FROM VOTES_TB v WHERE v.ask_id = Q.question_id AND v.vote_type = 1) AS votes_up
FROM QUESTIONS_TB Q
LEFT JOIN ANSWERS_TB A ON Q.question_id = A.ask_id
LEFT JOIN MAIN_TABLE m ON Q.user_id = m.user_id
WHERE Q.question_id = @question_id;

比如在PHP里，你可以先提取第一条记录的问题信息，再遍历所有结果把答案收集到一个数组中，最终得到「问题+所有答案」的完整结构。

重要提醒：避免SQL注入 #

你原来的代码直接把$question_id拼入SQL，存在严重的SQL注入风险。建议改用预处理语句，比如PHP的PDO实现：

$question_id = 56;
$pdo = new PDO('mysql:host=localhost;dbname=your_db', 'username', 'password');
$stmt = $pdo->prepare("
  -- 填入上面的SQL语句，将WHERE条件改为 Q.question_id = ?
");
$stmt->execute([$question_id]);
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);

内容的提问来源于stack exchange，提问作者james Oduro