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

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, '$', 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;

会回来

+-------------+----------+----------+----------+----------+
| 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)

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