mysql - 将附加列和子查询添加到 MySQL VIEW 以创建现有表的更具可读性的版本

Question

这个问题确实让我头疼，我花了几个小时寻找不同的解决方案来解决我的问题，但到目前为止还没有运气。

在我们的在线调查软件(“Limesurvey”)中，所有数据都存储在一个平面表格中。每个响应数据集有一行，每个问题项在该表中都有一个相关列。实际响应存储在表格单元格中。问题是这些数据很难读取，因为:

• 对于数组问题，Limesurvey 仅存储答案代码 -> 我们还想为完整答案文本添加一列。
• 对于多个数字问题，Limesurvey 会存储输入的数字 -> 我们还想添加一个列出相关子问题文本的列。

我们试图通过根据调查响应表的表定义创建 MySQL VIEW 来实现上述目标。为此，我们扩展了 VIEW 功能，在创建 VIEW 时添加其他列，并且添加了代码来查询相关答案和子问题文本。这些查询工作正常，如果输入一个数据集， View 将显示以下内容(到目前为止看起来不错): One data set

问题是，一旦我们添加具有不同答案的第二个数据集，数据就会混淆。看起来 MySQL 只是聚合所有行的数据来填充附加列。现在两行都有附加的答案/子问题文本(标记为“？”)，但对于该数据集没有给出答案，请参阅: Two data sets

我的主要问题是:是否可以定义附加“..._TEXT”列的范围，以便根据当前行的详细信息而不是整个数据来填充它们？

如果您有任何疑问，请告诉我，描述这个问题有点复杂。

Answer 1

我就是这么做的。使用这样的连接，您可以发现主选择列和问题列之间缺少关系。请参阅下面我的代码中的注释。

CREATE VIEW lime_view_967824 AS 
  SELECT 
    ls.id 'id',
    ls.submitdate 'submitdate',
    ls.lastpage 'lastpage',
    ls.startlanguage 'startlanguage',
    ls.967824X1087X6950SQ001 'arraySD_SQ001',
    COALESCE(a1.answer,'') AS 'arraySD_SQ001[TEXT]',
    ls.967824X1087X6950SQ002 'arraySD_SQ002',
    COALESCE(a2.answer, '') AS 'arraySD_SQ002[TEXT]',
    ls.967824X1087X6950SQ003 'arraySD_SQ003',
    COALESCE(a3.answer, '') AS 'arraySD_SQ003[TEXT]',
    ls.967824X1087X6946SQ001 'multiOptions_SQ001',
    ls.967824X1087X6946SQ002 'multiOptions_SQ002',
    ls.967824X1087X6946SQ003 'multiOptions_SQ003',
    ls.967824X1087X6946other 'multiOptions_other',
    ls.967824X1087X6941SQ001 'multiNumInput_SQ001',
    COALESCE(q1.question,'') AS 'multiNumInput_SQ001[TEXT]',
    ls.967824X1087X6941SQ002 'multiNumInput_SQ002',
    COALESCE(q2.question,'') AS 'multiNumInput_SQ002[TEXT]',
    ls.967824X1087X6941SQ003 'multiNumInput_SQ003',
    COALESCE(q3.question,'') AS 'multiNumInput_SQ003[TEXT]',
    ls.967824X1087X6941SQ004 'multiNumInput_SQ004',
    COALESCE(q4.question,'') AS 'multiNumInput_SQ004[TEXT]'
  FROM lime_survey_967824 ls
  LEFT JOIN lime_answers a1 ON a1.qid=6950 AND a1.language='en' AND ls.967824X1087X6950SQ001=a1.code
  LEFT JOIN lime_answers a2 ON a2.qid=6950 AND a2.language='en' AND ls.967824X1087X6950SQ002=a2.code
  LEFT JOIN lime_answers a3 ON a3.qid=6950 AND a3.language='en' AND ls.967824X1087X6950SQ003=a3.code
  LEFT JOIN lime_questions q1 ON q1.qid=6941 AND q1.title='SQ001' AND q1.language='en' AND q1.type='K' -- no join to ls?
  LEFT JOIN lime_questions q2 ON q2.qid=6941 AND q2.title='SQ002' AND q2.language='en' AND q2.type='K' -- no join to ls?
  LEFT JOIN lime_questions q3 ON q3.qid=6941 AND q3.title='SQ003' AND q3.language='en' AND q3.type='K' -- no join to ls?
  LEFT JOIN lime_questions q4 ON q4.qid=6941 AND q4.title='SQ004' AND q4.language='en' AND q4.type='K' -- no join to ls?