mysql - 如果行名称位于单独的表中，如何创建 mysql 数据透视表？

Question

我有 3 个表，我需要从中获取一个数据透视表。

1. 表格 - 问题

+------------+----------+  | idQuestion | question |  +------------+----------+  | 1          | Q1       |  +------------+----------+  | 2          | Q2       |  +------------+----------+

2. Table - question_answer

+------------+------------+----------+-------+  | idAnswer   | idQuestion | answer   | value |  +------------+------------+----------+-------+  | 1          | 1          | A1       | 100   |  +------------+------------+----------+-------+  | 2          | 1          | A2       | 101   |  +------------+------------+----------+-------+  | 3          | 2          | B1       | 200   |  +------------+------------+----------+-------+  

3. Table - results

+------------+------------+----------+-------+  | idResult   | idAnswer   | idUser   | ....  |  +------------+------------+----------+-------+  | 1          | 1          | u1       | ...   |  +------------+------------+----------+-------+  | 2          | 1          | u2       | ...   |  +------------+------------+----------+-------+  | 3          | 3          | u3       | ...   |  +------------+------------+----------+-------+  

and I need to get pivot table from results. I need it to like something like this:

+---------+--------------+--------------+--------------+
| idUser  | Q1           | Q2           | Qn           |
+---------+--------------+--------------+--------------+
| u1      | answer.value | answer.value | answer.value |
+---------+--------------+--------------+--------------+
| u2      | answer.value | answer.value | answer.value |
+---------+--------------+--------------+--------------+
| u2      | answer.value | answer.value | answer.value |
+---------+--------------+--------------+--------------+

我读过很多问题，看到过很多解决方案，但没有一个符合我的问题。

Answer 1

问题中所示的结果集无法通过单个 SELECT 语句获得。

获得此类结果的一种方法是考虑获得如下结果:

+---------+----+-------------+-------------+-------------+
| idUser  | q  | a.value     | a.value     | a.value     |
+---------+----+-------------+-------------+-------------+
| u1      | Q1 | a.value=101 | a.value=101 | a.value=101 |
| u1      | Q2 | a.value=200 | a.value=200 | a.value=200 |
| u1      | Qn | a.value=NNN | a.value=NNN | a.value=NNN |

也就是说，每个用户和问题及其答案占一行。考虑在每个 Ans.x 列中重复相同的答案。

“技巧”是用于返回每个 a.value 列的表达式，以使用条件测试来查看 q 是否与我们想要的特定问题匹配返回特定列中的答案...

SELECT r.idUser
     , q.q
     , IF(q.q='Q1',a.value,NULL) AS AnsQ1
     , IF(q.q='Q2',a.value,NULL) AS AnsQ2
     , IF(q.q='Qn',a.value,NULL) AS AnsQn
     , ...
  FROM ...

所以我们会得到如下结果:

+---------+----+-------------+-------------+-------------+
| idUser  | q  | AnsQ1       | AnsQ2       | AnsQn       |
+---------+----+-------------+-------------+-------------+
| u1      | Q1 | a.value=101 | NULL        | NULL        |
| u1      | Q2 | NULL        | a.value=200 | NULL        |
| u1      | Qn | NULL        | NULL        | a.value=NNN |

根据该结果，我们可以添加 GROUP BY idUser，并使用聚合函数(MIN() 或 MAX())选出非 NULL 的答案值。

SELECT r.idUser
     , MAX(IF(t.q='Q1',a.value,NULL)) AS AnsQ1
     , MAX(IF(t.q='Q2',a.value,NULL)) AS AnsQ2
     , MAX(IF(t.q='Qn',a.value,NULL)) AS AnsQn
     , ... 
  FROM ...
 GROUP BY r.idUser

要得到这样的结果:

+---------+-------------+-------------+-------------+
| idUser  | AnsQ1       | AnsQ2       | AnsQn       |
+---------+-------------+-------------+-------------+
| u1      | a.value=101 | a.value=200 | a.value=NNN |

<小时/>

但问题是:不可能动态更改返回的列数，或者动态更改 < 内的 SQL 语句返回的列的数据类型或名称。/em> SQL 语句。

列数、数据类型和列名称...这些都必须在 SELECT 语句提交到数据库时进行修复。

因此，底线是，结果集无法从单个 SELECT 语句中的表返回，无法使用表中的值作为列名，也无法改变列数从语句中返回。

但是，可以事先使用单独的 SQL 语句来从表中获取信息，这样我们就可以动态构建获取结果集所需的查询。

注意:上面所示的 MAX(IF(,val,NULL)) ... GROUP BY ... 模式只是一种方法。您可能也遇到了“SELECT 列表中的相关子查询”模式的答案。同样的方法适用:运行一个查询，获取构建查询所需的元数据，然后为第二个查询构建 SQL 文本(以返回您实际想要的结果)。