查询此问题的最佳方式是什么？

Question

我有看起来像这样的表：

questions:
id description
1   Q1
2   Q2

answers:
id question_id x y description
1     1        1 2   A1
2     1        3 4   A2
3     2        5 6   A3
4     2        7 8   A4

我想要得到的是一个可以输出以下内容的查询：

Q1 A1 1,2 A2 3,4
Q2 A3 5,6 A4 7,8

我已经花了好几天的时间试图弄清楚这一点。我正在 PHP 和 MySQL 中完成此操作，因此如果有人可以提供一些线索，那就太好了。

编辑：我忘了提及我也使用 CodeIgniter 来实现此目的。所以，这可能有助于找到答案。

Answer 1

考虑到每个问题可能有随机数量的答案，您无法设计返回固定数量列的查询。您必须为每个问题返回一个结果，然后在代码中进行一些解析。

GROUP_CONCAT 函数对于解决此类问题很有帮助：

SELECT q.description, GROUP_CONCAT(
    CONCAT(a.description,' ',a.x,',',a.y) ORDER BY a.id
    SEPARATOR ' '
    ) AS answers
FROM questions q
  JOIN answers a ON a.question_id = q.id
GROUP BY q.description;

将返回

+-------------+---------------+
| description | answers       |
+-------------+---------------+
| Q1          | A1 1,2 A2 3,4 |
| Q2          | A3 5,6 A4 7,8 |
+-------------+---------------+
2 rows in set (0.00 sec)

您可以根据您想要在代码中解析结果的任何内容来更改 SEPARATOR 值。您可以使用 GROUP_CONCAT 函数的 ORDER BY 子句对每个答案的返回结果中的答案进行排序（这里我按答案 id 排序）。

编辑：如果您确定每个问题的答案永远不会超过 4 个，您可以发出以下查询，将每个答案放入其自己的列中：

SELECT description,
  REPLACE(SUBSTRING(SUBSTRING_INDEX(answers, '
将返回
+-------------+----------+----------+----------+----------+
| description | answer_1 | answer_2 | answer_3 | answer_4 |
+-------------+----------+----------+----------+----------+
| Q1          | A1 1,2   | A2 3,4   |          |          |
| Q2          | A3 5,6   | A4 7,8   | A5 9,10  |          |
+-------------+----------+----------+----------+----------+
2 rows in set (0.00 sec)

我添加了第二个问题的答案为了说明。
, 1), LENGTH(SUBSTRING_INDEX(answers, '
将返回

我添加了第二个问题的答案为了说明。
, 1 - 1)) + 1), '
将返回

我添加了第二个问题的答案为了说明。
, '') answer_1,
  REPLACE(SUBSTRING(SUBSTRING_INDEX(answers, '
将返回

我添加了第二个问题的答案为了说明。
, 2), LENGTH(SUBSTRING_INDEX(answers, '
将返回

我添加了第二个问题的答案为了说明。
, 2 - 1)) + 1), '
将返回

我添加了第二个问题的答案为了说明。
, '') answer_2,
  REPLACE(SUBSTRING(SUBSTRING_INDEX(answers, '
将返回

我添加了第二个问题的答案为了说明。
, 3), LENGTH(SUBSTRING_INDEX(answers, '
将返回

我添加了第二个问题的答案为了说明。
, 3 - 1)) + 1), '
将返回

我添加了第二个问题的答案为了说明。
, '') answer_3,
  REPLACE(SUBSTRING(SUBSTRING_INDEX(answers, '
将返回

我添加了第二个问题的答案为了说明。
, 4), LENGTH(SUBSTRING_INDEX(answers, '
将返回

我添加了第二个问题的答案为了说明。
, 4 - 1)) + 1), '
将返回

我添加了第二个问题的答案为了说明。
, '') answer_4
FROM (
    SELECT q.description, GROUP_CONCAT(
        CONCAT(a.description,' ',a.x,',',a.y) ORDER BY a.id
        SEPARATOR '
将返回

我添加了第二个问题的答案为了说明。

        ) AS answers
    FROM questions q
      JOIN answers a ON a.question_id = q.id
    GROUP BY q.description
  ) t;

将返回

我添加了第二个问题的答案为了说明。

Answer 2

从答案中选择 * ORDER BY Question_id

$question_id = 0;
$print_ln = null;
foreach ($result as $row) {
  if ($question_id != $row['question_id']) { 
     echo "<br>";
     $question_id = $row['question_id'];
     $print_ln = "Q" . $row['question_id'] . " " . $row['description'] . " " . $row['x'] . "," . $row['y'];
  } else { 
     $print_ln = $print_ln . " " . $row['description'] . " " . $row['x'] . "," . $row['y'];
  }
  echo $print_ln;
 }

请注意，此代码仍然需要一些工作......它可以让您了解如何做到这一点。

Answer 3

此查询：

SELECT 
      q.id
    , q.description
    , a.description
    , CONCAT(a.x, ',', a.y)
FROM questions AS q
    JOIN answers AS a
        ON a.question_id = q.id
ORDER BY q.id
       , a.id

将显示：

| 1 | Q1 | A1 | 1,2 |
| 1 | Q1 | A2 | 3,4 |
| 2 | Q2 | A3 | 5,6 |
| 2 | Q2 | A4 | 7,8 |

您所描述的最终结果称为透视，这在 MySQL 中并不容易，并且取决于您拥有的数据。例如，如果一个问题有超过 2 个答案，应该显示什么？

为什么结果会是这样的：

| Q1 | A1 | 1,2 | A2 | 3,4 | 
| Q2 | A3 | 5,6 | A4 | 7,8 |

而不是那样？：

| Q1 | A1 | 1,2 | A2 | 3,4 | 
| Q2 | A4 | 7,8 | A3 | 5,6 | 

---

无论如何，对于每个问题最多 4 个答案并按 answers.id 进行排序，这是可行的。最好使用前面的查询并在 PHP 中进行旋转，这样您就可以毫无问题地处理可变数量的列：

SELECT 
      q.id
    , q.description
    , ( SELECT a.description
        FROM answers AS a
        WHERE a.question_id = q.id
        ORDER BY a.id  LIMIT 0,1
      ) AS answer1
    , ( SELECT CONCAT(a.x, ',', a.y)
        FROM answers AS a
        WHERE a.question_id = q.id
        ORDER BY a.id  LIMIT 0,1
      ) AS xy1
    , ( SELECT a.description
        FROM answers AS a
        WHERE a.question_id = q.id
        ORDER BY a.id  LIMIT 1,1
      ) AS answer2
    , ( SELECT CONCAT(a.x, ',', a.y)
        FROM answers AS a
        WHERE a.question_id = q.id
        ORDER BY a.id  LIMIT 1,1
      ) AS xy2
    , ( SELECT a.description
        FROM answers AS a
        WHERE a.question_id = q.id
        ORDER BY a.id  LIMIT 2,1
      ) AS answer3
    , ( SELECT CONCAT(a.x, ',', a.y)
        FROM answers AS a
        WHERE a.question_id = q.id
        ORDER BY a.id  LIMIT 2,1
      ) AS xy3
    , ( SELECT a.description
        FROM answers AS a
        WHERE a.question_id = q.id
        ORDER BY a.id  LIMIT 3,1
      ) AS answer4
    , ( SELECT CONCAT(a.x, ',', a.y)
        FROM answers AS a
        WHERE a.question_id = q.id
        ORDER BY a.id  LIMIT 3,1
      ) AS xy4
FROM questions AS q
ORDER BY q.id