mysql - mysql - 动态选择行作为列
问题描述
更新:我想使用动态 sql 选择问题作为列并将答案放在行中,如游标或循环,这可能吗?
我想要这样的选择结果
+--------+---------------+--------------------------------------------------------------------------+
| userid | Living Status | This is another question get from row and it's longer than 64 characters |
+--------+---------------+--------------------------------------------------------------------------+
| 19 | married | q2_opt3 |
+--------+---------------+--------------------------------------------------------------------------+
这是我的查询
select
userid,
min(if(question.ordering=1,o.name,NULL )) as 'Living Status',
min(if(question.ordering=2,o.name,NULL )) as 'This is another question get from row and it's longer than 64 characters'
from answer
inner join question on question.key_value = answer.key_value
inner join q_option o on question.id = o.question_id and o.value = answer.answer
where userid in (19)
GROUP BY id
问题表就像
+----+----------+---------------------------------------------------------------------------+--------------+
| id | ordering | question | key_value |
+----+----------+---------------------------------------------------------------------------+--------------+
| 1 | 1 | Living Status | livingStatus |
| 2 | 2 | This is another question get from row and it's longer than 64 characters | question_2 |
+----+----------+---------------------------------------------------------------------------+--------------+
答案表就像
+----+--------+--------------+--------+
| id | answer | key_value | userid |
+----+--------+--------------+--------+
| 1 | 2 | livingStatus | 19 |
| 2 | 3 | question_2 | 19 |
+----+--------+--------------+--------+
q_option 表就像
+----+----------+-------------+-------+
| id | name | question_id | value |
+----+----------+-------------+-------+
| 1 | single | 1 | 1 |
| 2 | married | 1 | 2 |
| 3 | divorced | 1 | 3 |
| 4 | q2_opt1 | 2 | 1 |
| 5 | q2_opt2 | 2 | 2 |
| 6 | q2_opt3 | 2 | 3 |
+----+----------+-------------+-------+
解决方案
推荐阅读
- python - 使用 Python(正则表达式)将数据仅放在数据框中的方括号中
- ansible - ansible-playbook 可以从标准输入而不是文件中读取吗?
- c# - 使用标签助手调用控制器,包括来自 asp.net core mvc 中搜索栏的数据
- c++ - 基于概念的模板成员函数重载
- java - 对这个 HashMap 算法面试题感到困惑
- laravel - 如何格式化以下 laravel 集合
- ios - 从碰撞收集的 SCNNode 调用 Methode
- php - codeigniter rest_controller 响应中的 text/html 格式
- ios - 使用除 caf 之外的其他标题录制音频
- vba - 如何在 Outlook VBA 中的自定义表单属性上设置事件?