我试图使用sql从mysql数据库中的moodle服务器中提取调查问题和答案(moodle版本3.1.7（内部版本：20170710））
这个 mdl_feedback_item 表存储问题，而答案是通过与 mdl_feedback_value table。
这是我当前的sql脚本：
select fi.id, fi.name as fb_question, fi.presentation as answer_options, fv.value as answer from mdl_feedback fb left join mdl_feedback_item fi on fi.feedback=fb.id left join mdl_feedback_value fv on fv.item=fi.id 结果是： id / fb_question / answer_options / answer 68 / Do you ... / Agree|Neutral|Disagree / 3 69 / Are you... / Yes|No|Maybe / 1 70 / Which day... / Monday|Tuesday / 2 (...) 我的目标是检查“answer”值，并用相应的answer\u选项替换它：
id / fb_question / answer_options / answer 68 / Do you ... / Agree|Neutral|Disagree / Disagree 69 / Are you... / Yes|No|Maybe / Yes 70 / Which day... / Monday|Tuesday / Tuesday 每个问题没有固定的最小/最大备选方案数，因此我认为我需要检查答案“值”，然后将其与管道分隔的答案选项列中的相应位置匹配。
有没有人有做这种拔牙的经验？
谢谢！
1条答案
按热度按时间093gszye1#
我想我找到答案了。
所以，我基本上可以通过使用长度，找出一门特定课程的备选答案：
Max((LENGTH(fi.presentation) - LENGTH(REPLACE(fi.presentation, '|', '')))+1) as max_num_options,假设结果是5。因此，我在case语句中使用substring\ u index，它将给出5个备选方案中每一个的值。
CASE
WHEN fv.value = 1 THEN (IF(SUBSTRING_INDEX(SUBSTRING_INDEX(fi.presentation, '|', ((LENGTH(fi.presentation) - LENGTH(REPLACE(fi.presentation, '|', '')))+1) ), '|', -1 ) = SUBSTRING_INDEX(SUBSTRING_INDEX(fi.presentation, '|', (((LENGTH(fi.presentation) - LENGTH(REPLACE(fi.presentation, '|', '')))+1)-1) ), '|', -1 ),"",SUBSTRING_INDEX( SUBSTRING_INDEX(fi.presentation, '|', 1 ), '|', -1 )))WHEN fv.value = 2 THEN (IF(SUBSTRING_INDEX(SUBSTRING_INDEX(fi.presentation, '|', ((LENGTH(fi.presentation) - LENGTH(REPLACE(fi.presentation, '|', '')))+1) ), '|', -1 ) = SUBSTRING_INDEX(SUBSTRING_INDEX(fi.presentation, '|', (((LENGTH(fi.presentation) - LENGTH(REPLACE(fi.presentation, '|', '')))+1)-1) ), '|', -1 ),"",SUBSTRING_INDEX( SUBSTRING_INDEX(fi.presentation, '|', 2 ), '|', -1 )))WHEN fv.value = 3 THEN (IF(SUBSTRING_INDEX(SUBSTRING_INDEX(fi.presentation, '|', ((LENGTH(fi.presentation) - LENGTH(REPLACE(fi.presentation, '|', '')))+1) ), '|', -1 ) = SUBSTRING_INDEX(SUBSTRING_INDEX(fi.presentation, '|', (((LENGTH(fi.presentation) - LENGTH(REPLACE(fi.presentation, '|', '')))+1)-1) ), '|', -1 ),"",SUBSTRING_INDEX( SUBSTRING_INDEX(fi.presentation, '|', 3 ), '|', -1 )))WHEN fv.value = 4 THEN (IF(SUBSTRING_INDEX(SUBSTRING_INDEX(fi.presentation, '|', ((LENGTH(fi.presentation) - LENGTH(REPLACE(fi.presentation, '|', '')))+1) ), '|', -1 ) = SUBSTRING_INDEX(SUBSTRING_INDEX(fi.presentation, '|', (((LENGTH(fi.presentation) - LENGTH(REPLACE(fi.presentation, '|', '')))+1)-1) ), '|', -1 ),"",SUBSTRING_INDEX( SUBSTRING_INDEX(fi.presentation, '|', 4 ), '|', -1 )))WHEN fv.value = 5 THEN (IF(SUBSTRING_INDEX(SUBSTRING_INDEX(fi.presentation, '|', ((LENGTH(fi.presentation) - LENGTH(REPLACE(fi.presentation, '|', '')))+1) ), '|', -1 ) = SUBSTRING_INDEX(SUBSTRING_INDEX(fi.presentation, '|', (((LENGTH(fi.presentation) - LENGTH(REPLACE(fi.presentation, '|', '')))+1)-1) ), '|', -1 ),"",SUBSTRING_INDEX( SUBSTRING_INDEX(fi.presentation, '|', 5 ), '|', -1 )))ELSE 'No answer given / text field'END AS fv_response_text这有点混乱，我总是要事先检查有多少种选择，但它完成了工作。