2.调查问卷-建表

男娘i 2022-03-09 04:56 258阅读 0赞

1.QUESTIONNAIRE\_MANAGE\_T--问卷活动管理表

2.QUESTIONNAIRE\_MANAGE\_EXTEND\_T--问卷活动管理扩展表

3.QUE\_ANSWER\_FREQUENCY\_T--答题次数查询表

4.QUESTIONNAIRE\_INFO\_T--题干表

5.QUESTIONNAIRE\_EXTEND\_INFO\_T--选项表

6.QUESTIONNAIRE\_ANSWER\_T--调查问卷答题表

7.STANDARD\_ANSWER\_SCORE--旅客评分表--无业务需要暂无评分

--1.QUESTIONNAIRE_MANAGE_T
    --SELECT * FROM QUESTIONNAIRE_MANAGE_T T;--问卷活动管理表
    -- Create table
    create table QUESTIONNAIRE_MANAGE_T
    (
      ID                   VARCHAR2(32),
      ACTIVITY_ID          NUMBER(6),
      QUESTION_VERSION     NUMBER(4),
      START_DATE           DATE default SYSDATE,
      END_DATE             DATE default SYSDATE,
      ANSWER_LEVEL         VARCHAR2(100),
      ANSWER_NUMLIMIT      NUMBER(6),
      INDIVIDUALIZATION    VARCHAR2(100),
      CREATE_DATE          DATE default SYSDATE,
      CTEATOR              VARCHAR2(300),
      UPD_TIME             DATE,
      UPD_USER             VARCHAR2(300),
      IF_VALID             NUMBER(2) default 1,
      QUESTIONTYPE         VARCHAR2(300),
      CALL_LIMIT           VARCHAR2(100),
      ANSWER_TABLE_NAME    VARCHAR2(100),
      FREQUENCY_TABLE_NAME VARCHAR2(100),
      REMARK               VARCHAR2(150)
    );
    -- Add comments to the table 
    comment on table QUESTIONNAIRE_MANAGE_T is '调查问卷题库管理表';
    -- Add comments to the columns 
    comment on column QUESTIONNAIRE_MANAGE_T.ID is '问卷id';
    comment on column QUESTIONNAIRE_MANAGE_T.ACTIVITY_ID is '问卷活动id,从1开始，逐个活动递增';
    comment on column QUESTIONNAIRE_MANAGE_T.QUESTION_VERSION is '题版本，从1开始，需要根据实际情况递增';
    comment on column QUESTIONNAIRE_MANAGE_T.START_DATE is '问卷开始时间';
    comment on column QUESTIONNAIRE_MANAGE_T.END_DATE is '问卷结束时间';
    comment on column QUESTIONNAIRE_MANAGE_T.ANSWER_LEVEL is '问卷限制会员级别';
    comment on column QUESTIONNAIRE_MANAGE_T.ANSWER_NUMLIMIT is '答题次数限制:1是有1次答题机会';
    comment on column QUESTIONNAIRE_MANAGE_T.INDIVIDUALIZATION is '问卷个性化展示标识';
    comment on column QUESTIONNAIRE_MANAGE_T.CREATE_DATE is '创建时间';
    comment on column QUESTIONNAIRE_MANAGE_T.CTEATOR is '创建人';
    comment on column QUESTIONNAIRE_MANAGE_T.UPD_TIME is '修改时间';
    comment on column QUESTIONNAIRE_MANAGE_T.UPD_USER is '修改人';
    comment on column QUESTIONNAIRE_MANAGE_T.IF_VALID is '是否有效1-有效 0-无效，无效时相当与关闭活动';
    comment on column QUESTIONNAIRE_MANAGE_T.QUESTIONTYPE is '本次问卷的所有题型';
    comment on column QUESTIONNAIRE_MANAGE_T.CALL_LIMIT is '可以调用查询旅客填写次数的接口名';
    comment on column QUESTIONNAIRE_MANAGE_T.ANSWER_TABLE_NAME is '问卷答题表名';
    comment on column QUESTIONNAIRE_MANAGE_T.FREQUENCY_TABLE_NAME is '问卷答题次数表名';
    comment on column QUESTIONNAIRE_MANAGE_T.REMARK is '描述';
    
    insert into QUESTIONNAIRE_MANAGE_T (ID, ACTIVITY_ID, QUESTION_VERSION, START_DATE, END_DATE, ANSWER_LEVEL, ANSWER_NUMLIMIT, INDIVIDUALIZATION, CREATE_DATE, CTEATOR, UPD_TIME, UPD_USER, IF_VALID, QUESTIONTYPE, CALL_LIMIT, ANSWER_TABLE_NAME, FREQUENCY_TABLE_NAME, REMARK)
    values ('asdfghjkl', 1, 1, to_date('18-02-2019', 'dd-mm-yyyy'), to_date('28-03-2019', 'dd-mm-yyyy'), '普卡，金卡', 10, '''公共'',''有午餐可填'',''有餐可填''', to_date('19-02-2019', 'dd-mm-yyyy'), 'cheng', null, '', 1, 'single_choice,multiple_choice,essay,yes_no,true_false,score,fill_blanks', 'qryQuestionnaireInfo,saveQuestionnaireInfo', 'QUESTIONNAIRE_ANSWER_T', 'QUE_ANSWER_FREQUENCY_T', '');
    
    
    --2.QUESTIONNAIRE_MANAGE_EXTEND_T
    --SELECT * FROM QUESTIONNAIRE_MANAGE_EXTEND_T T;--问卷活动管理扩展表
    -- Create table
    create table QUESTIONNAIRE_MANAGE_EXTEND_T
    (
      ACTIVITY_ID      NUMBER(6),
      QUESTION_VERSION NUMBER(4),
      QUESTIONID_NAME  VARCHAR2(300),
      QUESTIONCONTENT  VARCHAR2(2000),
      URL_PARAM        VARCHAR2(1000),
      CREATE_DATE      DATE default SYSDATE,
      CTEATOR          VARCHAR2(300),
      UPD_TIME         DATE,
      UPD_USER         VARCHAR2(300),
      IF_VALID         NUMBER(2) default 1,
      LANG             VARCHAR2(8),
      REMARK           VARCHAR2(150)
    );
    -- Add comments to the table 
    comment on table QUESTIONNAIRE_MANAGE_EXTEND_T is '调查问卷题库管理表扩展';
    -- Add comments to the columns 
    comment on column QUESTIONNAIRE_MANAGE_EXTEND_T.ACTIVITY_ID is '活动id';
    comment on column QUESTIONNAIRE_MANAGE_EXTEND_T.QUESTION_VERSION is '题版本号';
    comment on column QUESTIONNAIRE_MANAGE_EXTEND_T.QUESTIONID_NAME is '活动题目';
    comment on column QUESTIONNAIRE_MANAGE_EXTEND_T.QUESTIONCONTENT is '活动描述';
    comment on column QUESTIONNAIRE_MANAGE_EXTEND_T.URL_PARAM is '图片等url地址';
    comment on column QUESTIONNAIRE_MANAGE_EXTEND_T.CREATE_DATE is '创建时间';
    comment on column QUESTIONNAIRE_MANAGE_EXTEND_T.CTEATOR is '创建人';
    comment on column QUESTIONNAIRE_MANAGE_EXTEND_T.UPD_TIME is '修改时间';
    comment on column QUESTIONNAIRE_MANAGE_EXTEND_T.UPD_USER is '修改人';
    comment on column QUESTIONNAIRE_MANAGE_EXTEND_T.IF_VALID is '是否有效 1-有效 0-无效，无效时相当与删除';
    comment on column QUESTIONNAIRE_MANAGE_EXTEND_T.LANG is '语言';
    comment on column QUESTIONNAIRE_MANAGE_EXTEND_T.REMARK is '描述';
    
    insert into QUESTIONNAIRE_MANAGE_EXTEND_T (ACTIVITY_ID, QUESTION_VERSION, QUESTIONID_NAME, QUESTIONCONTENT, URL_PARAM, CREATE_DATE, CTEATOR, UPD_TIME, UPD_USER, IF_VALID, LANG, REMARK)
    values (1, 1, '第一套题', '第一套题简介', '第一套题封面url', to_date('19-02-2019', 'dd-mm-yyyy'), 'cheng', null, '', 1, '中文', '');
    
    insert into QUESTIONNAIRE_MANAGE_EXTEND_T (ACTIVITY_ID, QUESTION_VERSION, QUESTIONID_NAME, QUESTIONCONTENT, URL_PARAM, CREATE_DATE, CTEATOR, UPD_TIME, UPD_USER, IF_VALID, LANG, REMARK)
    values (1, 1, '第一套题（英文）', '第一套题简介（英文）', '第一套题封面英文url', to_date('19-02-2019', 'dd-mm-yyyy'), 'cheng', null, '', 1, '英文', '');
    
    insert into QUESTIONNAIRE_MANAGE_EXTEND_T (ACTIVITY_ID, QUESTION_VERSION, QUESTIONID_NAME, QUESTIONCONTENT, URL_PARAM, CREATE_DATE, CTEATOR, UPD_TIME, UPD_USER, IF_VALID, LANG, REMARK)
    values (1, 1, '第一套题（日文）', '第一套题简介（日文）', '第一套题封面日文url', to_date('19-02-2019', 'dd-mm-yyyy'), 'cheng', null, '', 1, '日文', '');
    
    
    
    --3.QUE_ANSWER_FREQUENCY_T
    --SELECT * FROM QUE_ANSWER_FREQUENCY_T T;--答题次数查询表
    -- Create table
    create table QUE_ANSWER_FREQUENCY_T
    (
      USER_ID          VARCHAR2(32),
      ACTIVITY_ID      NUMBER(6),
      QUESTION_VERSION NUMBER(4),
      CREATE_DATE      DATE default SYSDATE,
      IF_VALID         NUMBER(2) default 1,
      REMARK           VARCHAR2(150)
    );
    -- Add comments to the table 
    comment on table QUE_ANSWER_FREQUENCY_T is '调查问卷题库答题次数记录表';
    -- Add comments to the columns 
    comment on column QUE_ANSWER_FREQUENCY_T.USER_ID is '用户唯一id';
    comment on column QUE_ANSWER_FREQUENCY_T.ACTIVITY_ID is '问卷活动id';
    comment on column QUE_ANSWER_FREQUENCY_T.QUESTION_VERSION is '问卷版本';
    comment on column QUE_ANSWER_FREQUENCY_T.CREATE_DATE is '创建时间';
    comment on column QUE_ANSWER_FREQUENCY_T.IF_VALID is '是否有效：1有效 0无效';
    comment on column QUE_ANSWER_FREQUENCY_T.REMARK is '描述';
    
    insert into QUE_ANSWER_FREQUENCY_T (USER_ID, ACTIVITY_ID, QUESTION_VERSION, CREATE_DATE, IF_VALID, REMARK)
    values ('18730733961', 1, 1, to_date('04-03-2019', 'dd-mm-yyyy'), 1, '');
    
    insert into QUE_ANSWER_FREQUENCY_T (USER_ID, ACTIVITY_ID, QUESTION_VERSION, CREATE_DATE, IF_VALID, REMARK)
    values ('18730733961', 1, 1, to_date('11-03-2019 11:12:29', 'dd-mm-yyyy hh24:mi:ss'), 1, '');
    
    insert into QUE_ANSWER_FREQUENCY_T (USER_ID, ACTIVITY_ID, QUESTION_VERSION, CREATE_DATE, IF_VALID, REMARK)
    values ('18730733961', 1, 1, to_date('11-03-2019 11:16:42', 'dd-mm-yyyy hh24:mi:ss'), 1, '');
    
    insert into QUE_ANSWER_FREQUENCY_T (USER_ID, ACTIVITY_ID, QUESTION_VERSION, CREATE_DATE, IF_VALID, REMARK)
    values ('18730733961', 1, 1, to_date('11-03-2019 11:23:03', 'dd-mm-yyyy hh24:mi:ss'), 1, '');
    
    insert into QUE_ANSWER_FREQUENCY_T (USER_ID, ACTIVITY_ID, QUESTION_VERSION, CREATE_DATE, IF_VALID, REMARK)
    values ('18730733961', 1, 1, to_date('11-03-2019 15:56:41', 'dd-mm-yyyy hh24:mi:ss'), 1, '');
    
    insert into QUE_ANSWER_FREQUENCY_T (USER_ID, ACTIVITY_ID, QUESTION_VERSION, CREATE_DATE, IF_VALID, REMARK)
    values ('18730733961', 1, 1, to_date('11-03-2019 22:46:33', 'dd-mm-yyyy hh24:mi:ss'), 1, '');
    
    insert into QUE_ANSWER_FREQUENCY_T (USER_ID, ACTIVITY_ID, QUESTION_VERSION, CREATE_DATE, IF_VALID, REMARK)
    values ('18730733961', 1, 1, to_date('12-03-2019 11:26:36', 'dd-mm-yyyy hh24:mi:ss'), 1, '');
    
    insert into QUE_ANSWER_FREQUENCY_T (USER_ID, ACTIVITY_ID, QUESTION_VERSION, CREATE_DATE, IF_VALID, REMARK)
    values ('18730733961', 1, 1, to_date('12-03-2019 14:53:54', 'dd-mm-yyyy hh24:mi:ss'), 1, '');
    
    
    --4.QUESTIONNAIRE_INFO_T
    --SELECT * FROM QUESTIONNAIRE_INFO_T T;--题干表
    -- Create table
    create table QUESTIONNAIRE_INFO_T
    (
      QUESTIONID           NUMBER(8),
      QUESTION_VERSION     NUMBER(4),
      QUESTIONTYPE         VARCHAR2(32),
      QUESTIONCONTENT      VARCHAR2(1000),
      ANSWER_LEVEL         NUMBER(2),
      REQUIRED             NUMBER(2),
      QUESTION_SEQUENCE    NUMBER(6),
      WATERMARK            VARCHAR2(150),
      INDIVIDUALIZATION    VARCHAR2(32),
      CHOICE_BLANKS_NUMBER NUMBER(2) default 1,
      SHOW_BLANKS          VARCHAR2(64),
      MINSCORE             NUMBER(3) default 0,
      MAXSCORE             NUMBER(3) default 0,
      OPTIONSCORE          NUMBER(3) default 10,
      IF_VALID             NUMBER(2) default 1,
      CREATE_DATE          DATE default SYSDATE,
      LANG                 VARCHAR2(8),
      URL_PARAM            VARCHAR2(300),
      REMARK               VARCHAR2(150)
    );
    -- Add comments to the table 
    comment on table QUESTIONNAIRE_INFO_T is '调查问卷题库表';
    -- Add comments to the columns 
    comment on column QUESTIONNAIRE_INFO_T.QUESTIONID is '题目ID';
    comment on column QUESTIONNAIRE_INFO_T.QUESTION_VERSION is '题的版本号';
    comment on column QUESTIONNAIRE_INFO_T.QUESTIONTYPE is '题目类型 single_choice单选/multiple_choice多选/score评分/essay简答/fill_blanks填空/yes_no是否题/true_false判断题';
    comment on column QUESTIONNAIRE_INFO_T.QUESTIONCONTENT is '题目内容，如：今天天气如何？';
    comment on column QUESTIONNAIRE_INFO_T.ANSWER_LEVEL is '问题等级，可以按等级给旅客展示';
    comment on column QUESTIONNAIRE_INFO_T.REQUIRED is '是否必答：1是必填 0非必填';
    comment on column QUESTIONNAIRE_INFO_T.QUESTION_SEQUENCE is '题展示的题号的排序，用来对前台排序展示';
    comment on column QUESTIONNAIRE_INFO_T.WATERMARK is '题干中的水印';
    comment on column QUESTIONNAIRE_INFO_T.INDIVIDUALIZATION is '个性化展示标志';
    comment on column QUESTIONNAIRE_INFO_T.CHOICE_BLANKS_NUMBER is '选则最大选则数--填空题填空数--其他题型默认为1';
    comment on column QUESTIONNAIRE_INFO_T.SHOW_BLANKS is '展示具体说明框的扩展信息表选项id字段';
    comment on column QUESTIONNAIRE_INFO_T.MINSCORE is '评分题最小评分数，默认为0';
    comment on column QUESTIONNAIRE_INFO_T.MAXSCORE is '评分题最大评分数，默认为10';
    comment on column QUESTIONNAIRE_INFO_T.OPTIONSCORE is '评分题默认评分数，默认为0';
    comment on column QUESTIONNAIRE_INFO_T.IF_VALID is '是否有效 0无效 1有效 ';
    comment on column QUESTIONNAIRE_INFO_T.CREATE_DATE is '创建时间';
    comment on column QUESTIONNAIRE_INFO_T.LANG is '语言';
    comment on column QUESTIONNAIRE_INFO_T.URL_PARAM is '题干包含图片url';
    comment on column QUESTIONNAIRE_INFO_T.REMARK is '描述';
    
    
    insert into QUESTIONNAIRE_INFO_T (QUESTIONID, QUESTION_VERSION, QUESTIONTYPE, QUESTIONCONTENT, ANSWER_LEVEL, REQUIRED, QUESTION_SEQUENCE, WATERMARK, INDIVIDUALIZATION, CHOICE_BLANKS_NUMBER, SHOW_BLANKS, MINSCORE, MAXSCORE, OPTIONSCORE, IF_VALID, CREATE_DATE, LANG, URL_PARAM, REMARK)
    values (7, 1, 'true_false', '对错题：中文', 1, 1, 700, '题干水印', '公共', 1, '', 0, 10, 0, 1, to_date('27-11-2018', 'dd-mm-yyyy'), '中文', '', '');
    
    insert into QUESTIONNAIRE_INFO_T (QUESTIONID, QUESTION_VERSION, QUESTIONTYPE, QUESTIONCONTENT, ANSWER_LEVEL, REQUIRED, QUESTION_SEQUENCE, WATERMARK, INDIVIDUALIZATION, CHOICE_BLANKS_NUMBER, SHOW_BLANKS, MINSCORE, MAXSCORE, OPTIONSCORE, IF_VALID, CREATE_DATE, LANG, URL_PARAM, REMARK)
    values (2, 1, 'multiple_choice', '多选题：中文：多选', 0, 1, 200, '题干水印', '有午餐可填', 1, '0001,0002', 0, 10, 0, 1, to_date('27-11-2018', 'dd-mm-yyyy'), '中文', '', '');
    
    insert into QUESTIONNAIRE_INFO_T (QUESTIONID, QUESTION_VERSION, QUESTIONTYPE, QUESTIONCONTENT, ANSWER_LEVEL, REQUIRED, QUESTION_SEQUENCE, WATERMARK, INDIVIDUALIZATION, CHOICE_BLANKS_NUMBER, SHOW_BLANKS, MINSCORE, MAXSCORE, OPTIONSCORE, IF_VALID, CREATE_DATE, LANG, URL_PARAM, REMARK)
    values (1, 1, 'single_choice', '单选题：日文：天气如何', 0, 1, 100, '题干水印', '有午餐可填', 1, '', 0, 10, 0, 1, to_date('27-11-2018', 'dd-mm-yyyy'), 'jn', '', '');
    
    insert into QUESTIONNAIRE_INFO_T (QUESTIONID, QUESTION_VERSION, QUESTIONTYPE, QUESTIONCONTENT, ANSWER_LEVEL, REQUIRED, QUESTION_SEQUENCE, WATERMARK, INDIVIDUALIZATION, CHOICE_BLANKS_NUMBER, SHOW_BLANKS, MINSCORE, MAXSCORE, OPTIONSCORE, IF_VALID, CREATE_DATE, LANG, URL_PARAM, REMARK)
    values (1, 1, 'single_choice', '单选题：英文：天气如何', 1, 1, 100, '题干水印', '有午餐可填', 1, '', 0, 10, 0, 1, to_date('27-11-2018 12:00:00', 'dd-mm-yyyy hh24:mi:ss'), 'en', '', '');
    
    insert into QUESTIONNAIRE_INFO_T (QUESTIONID, QUESTION_VERSION, QUESTIONTYPE, QUESTIONCONTENT, ANSWER_LEVEL, REQUIRED, QUESTION_SEQUENCE, WATERMARK, INDIVIDUALIZATION, CHOICE_BLANKS_NUMBER, SHOW_BLANKS, MINSCORE, MAXSCORE, OPTIONSCORE, IF_VALID, CREATE_DATE, LANG, URL_PARAM, REMARK)
    values (1, 1, 'single_choice', '单选题：中文：天气如何', 0, 1, 100, '题干水印', '有午餐可填', 1, '', 0, 10, 0, 1, to_date('27-11-2018', 'dd-mm-yyyy'), '中文', '', '');
    
    insert into QUESTIONNAIRE_INFO_T (QUESTIONID, QUESTION_VERSION, QUESTIONTYPE, QUESTIONCONTENT, ANSWER_LEVEL, REQUIRED, QUESTION_SEQUENCE, WATERMARK, INDIVIDUALIZATION, CHOICE_BLANKS_NUMBER, SHOW_BLANKS, MINSCORE, MAXSCORE, OPTIONSCORE, IF_VALID, CREATE_DATE, LANG, URL_PARAM, REMARK)
    values (3, 1, 'score', '评分题：中文', 0, 1, 900, '题干水印', '有午餐可填', 1, '', 0, 10, 0, 1, to_date('27-11-2018', 'dd-mm-yyyy'), '中文', '', '');
    
    insert into QUESTIONNAIRE_INFO_T (QUESTIONID, QUESTION_VERSION, QUESTIONTYPE, QUESTIONCONTENT, ANSWER_LEVEL, REQUIRED, QUESTION_SEQUENCE, WATERMARK, INDIVIDUALIZATION, CHOICE_BLANKS_NUMBER, SHOW_BLANKS, MINSCORE, MAXSCORE, OPTIONSCORE, IF_VALID, CREATE_DATE, LANG, URL_PARAM, REMARK)
    values (4, 1, 'essay', '简单题：中文', 0, 1, 400, '题干水印', '有午餐可填', 1, '', 0, 10, 0, 1, to_date('27-11-2018', 'dd-mm-yyyy'), '中文', '', '');
    
    insert into QUESTIONNAIRE_INFO_T (QUESTIONID, QUESTION_VERSION, QUESTIONTYPE, QUESTIONCONTENT, ANSWER_LEVEL, REQUIRED, QUESTION_SEQUENCE, WATERMARK, INDIVIDUALIZATION, CHOICE_BLANKS_NUMBER, SHOW_BLANKS, MINSCORE, MAXSCORE, OPTIONSCORE, IF_VALID, CREATE_DATE, LANG, URL_PARAM, REMARK)
    values (5, 1, 'fill_blanks', '填空题：中文', 0, 1, 5000, '题干水印', '有早餐可填', 2, '', 0, 10, 0, 1, to_date('27-11-2018', 'dd-mm-yyyy'), '中文', '', '');
    
    insert into QUESTIONNAIRE_INFO_T (QUESTIONID, QUESTION_VERSION, QUESTIONTYPE, QUESTIONCONTENT, ANSWER_LEVEL, REQUIRED, QUESTION_SEQUENCE, WATERMARK, INDIVIDUALIZATION, CHOICE_BLANKS_NUMBER, SHOW_BLANKS, MINSCORE, MAXSCORE, OPTIONSCORE, IF_VALID, CREATE_DATE, LANG, URL_PARAM, REMARK)
    values (6, 1, 'yes_no', '是否题：中文', 0, 1, 600, '题干水印', '公共', 1, '', 0, 10, 0, 1, to_date('27-11-2018', 'dd-mm-yyyy'), '中文', '', '');
    
    
    
    
    --5.QUESTIONNAIRE_EXTEND_INFO_T
    --SELECT * FROM QUESTIONNAIRE_EXTEND_INFO_T T;--选项表
    -- Create table
    create table QUESTIONNAIRE_EXTEND_INFO_T
    (
      QUESTIONID        VARCHAR2(8),
      OPTIONID          VARCHAR2(8),
      OPTIONCONTENT     VARCHAR2(1000),
      WATERMARK         VARCHAR2(150),
      IF_VALID          NUMBER(2),
      QUESTION_SEQUENCE NUMBER(3),
      CREATE_DATE       DATE default SYSDATE,
      LANG              VARCHAR2(8),
      URL_PARAM         VARCHAR2(300),
      REMARK            VARCHAR2(150)
    );
    -- Add comments to the table 
    comment on table QUESTIONNAIRE_EXTEND_INFO_T is '题库扩展表';
    -- Add comments to the columns 
    comment on column QUESTIONNAIRE_EXTEND_INFO_T.QUESTIONID is '题目ID';
    comment on column QUESTIONNAIRE_EXTEND_INFO_T.OPTIONID is '选项ID，如：0001';
    comment on column QUESTIONNAIRE_EXTEND_INFO_T.OPTIONCONTENT is '选项内容，如：米饭';
    comment on column QUESTIONNAIRE_EXTEND_INFO_T.WATERMARK is '选项中的水印，适用整个题中只有一个水印';
    comment on column QUESTIONNAIRE_EXTEND_INFO_T.IF_VALID is '是否有效 0无效 1有效';
    comment on column QUESTIONNAIRE_EXTEND_INFO_T.QUESTION_SEQUENCE is '选项等内容展示的题号的排序';
    comment on column QUESTIONNAIRE_EXTEND_INFO_T.CREATE_DATE is '创建时间';
    comment on column QUESTIONNAIRE_EXTEND_INFO_T.URL_PARAM is '该选项的url';
    comment on column QUESTIONNAIRE_EXTEND_INFO_T.REMARK is '描述';
    
    insert into QUESTIONNAIRE_EXTEND_INFO_T (QUESTIONID, OPTIONID, OPTIONCONTENT, WATERMARK, IF_VALID, QUESTION_SEQUENCE, CREATE_DATE, LANG, URL_PARAM, REMARK)
    values ('1', '0001', '选项A', '选择A水印', 1, 100, to_date('28-11-2018 12:00:00', 'dd-mm-yyyy hh24:mi:ss'), '中文', '', '');
    
    insert into QUESTIONNAIRE_EXTEND_INFO_T (QUESTIONID, OPTIONID, OPTIONCONTENT, WATERMARK, IF_VALID, QUESTION_SEQUENCE, CREATE_DATE, LANG, URL_PARAM, REMARK)
    values ('1', '0001', '选项A英文', '选择A英文水印', 1, 100, to_date('28-11-2018', 'dd-mm-yyyy'), 'en', '', '');
    
    insert into QUESTIONNAIRE_EXTEND_INFO_T (QUESTIONID, OPTIONID, OPTIONCONTENT, WATERMARK, IF_VALID, QUESTION_SEQUENCE, CREATE_DATE, LANG, URL_PARAM, REMARK)
    values ('1', '0002', '选项B英文', '选择B英文水印', 1, 200, to_date('28-11-2018', 'dd-mm-yyyy'), 'en', '', '');
    
    insert into QUESTIONNAIRE_EXTEND_INFO_T (QUESTIONID, OPTIONID, OPTIONCONTENT, WATERMARK, IF_VALID, QUESTION_SEQUENCE, CREATE_DATE, LANG, URL_PARAM, REMARK)
    values ('1', '0001', '选项A日文', '选择A日文水印', 1, 100, to_date('28-11-2018', 'dd-mm-yyyy'), 'jn', '', '');
    
    insert into QUESTIONNAIRE_EXTEND_INFO_T (QUESTIONID, OPTIONID, OPTIONCONTENT, WATERMARK, IF_VALID, QUESTION_SEQUENCE, CREATE_DATE, LANG, URL_PARAM, REMARK)
    values ('1', '0002', '选项B', '选择B水印', 1, 200, to_date('28-11-2018', 'dd-mm-yyyy'), '中文', '', '');
    
    insert into QUESTIONNAIRE_EXTEND_INFO_T (QUESTIONID, OPTIONID, OPTIONCONTENT, WATERMARK, IF_VALID, QUESTION_SEQUENCE, CREATE_DATE, LANG, URL_PARAM, REMARK)
    values ('2', '0001', '选项A', '水印', 1, 100, to_date('28-11-2018', 'dd-mm-yyyy'), '中文', '', '');
    
    insert into QUESTIONNAIRE_EXTEND_INFO_T (QUESTIONID, OPTIONID, OPTIONCONTENT, WATERMARK, IF_VALID, QUESTION_SEQUENCE, CREATE_DATE, LANG, URL_PARAM, REMARK)
    values ('2', '0002', '选项B', '水印', 1, 200, to_date('28-11-2018', 'dd-mm-yyyy'), '中文', '', '');
    
    insert into QUESTIONNAIRE_EXTEND_INFO_T (QUESTIONID, OPTIONID, OPTIONCONTENT, WATERMARK, IF_VALID, QUESTION_SEQUENCE, CREATE_DATE, LANG, URL_PARAM, REMARK)
    values ('2', '0003', '选项C', '水印', 1, 300, to_date('28-11-2018', 'dd-mm-yyyy'), '中文', '', '');
    
    --6.QUESTIONNAIRE_ANSWER_T
    SELECT * FROM QUESTIONNAIRE_ANSWER_T T;--调查问卷答题表
    -- Create table
    create table QUESTIONNAIRE_ANSWER_T
    (
      ACTIVITY_ID   NUMBER(6),
      USER_ID       VARCHAR2(32),
      QUESTIONID    NUMBER(8),
      ANSWEROPTION  VARCHAR2(256),
      CREATE_DATE   DATE default SYSDATE,
      IF_VALID      NUMBER(2),
      ANSWERCONTENT VARCHAR2(512)
    );
    -- Add comments to the table 
    comment on table QUESTIONNAIRE_ANSWER_T is '用户答题表';
    -- Add comments to the columns 
    comment on column QUESTIONNAIRE_ANSWER_T.ACTIVITY_ID is '问卷活动id';
    comment on column QUESTIONNAIRE_ANSWER_T.USER_ID is '用户唯一标识,若以后系统会员卡号扩位数等，该值不变/若没有可以为会员卡号';
    comment on column QUESTIONNAIRE_ANSWER_T.QUESTIONID is '题目id';
    comment on column QUESTIONNAIRE_ANSWER_T.ANSWEROPTION is '答题选项：选择/是否（1是 0否）/对错（1对 0错）/评分题选则';
    comment on column QUESTIONNAIRE_ANSWER_T.CREATE_DATE is '创建时间';
    comment on column QUESTIONNAIRE_ANSWER_T.IF_VALID is '是否有效:1有效 0无效';
    comment on column QUESTIONNAIRE_ANSWER_T.ANSWERCONTENT is '答题内容：选择/是否/对错/评分的选择说明 或 填空题 简答题';
    
    insert into QUESTIONNAIRE_ANSWER_T (ACTIVITY_ID, USER_ID, QUESTIONID, ANSWEROPTION, CREATE_DATE, IF_VALID, ANSWERCONTENT)
    values (1, '18730733961', 1, '0001', to_date('11-03-2019 11:16:42', 'dd-mm-yyyy hh24:mi:ss'), 1, '');
    
    insert into QUESTIONNAIRE_ANSWER_T (ACTIVITY_ID, USER_ID, QUESTIONID, ANSWEROPTION, CREATE_DATE, IF_VALID, ANSWERCONTENT)
    values (1, '18730733961', 2, '0001,0002', to_date('11-03-2019 11:16:42', 'dd-mm-yyyy hh24:mi:ss'), 1, '多选题说明，因为都会用到所有选择了两个选项');
    
    insert into QUESTIONNAIRE_ANSWER_T (ACTIVITY_ID, USER_ID, QUESTIONID, ANSWEROPTION, CREATE_DATE, IF_VALID, ANSWERCONTENT)
    values (1, '18730733961', 4, '', to_date('11-03-2019 11:16:42', 'dd-mm-yyyy hh24:mi:ss'), 1, '简答题的答案');
    
    insert into QUESTIONNAIRE_ANSWER_T (ACTIVITY_ID, USER_ID, QUESTIONID, ANSWEROPTION, CREATE_DATE, IF_VALID, ANSWERCONTENT)
    values (1, '18730733961', 6, '1', to_date('11-03-2019 11:16:42', 'dd-mm-yyyy hh24:mi:ss'), 1, '是否题，1选“是”，0选“否”');
    
    insert into QUESTIONNAIRE_ANSWER_T (ACTIVITY_ID, USER_ID, QUESTIONID, ANSWEROPTION, CREATE_DATE, IF_VALID, ANSWERCONTENT)
    values (1, '18730733961', 7, '1', to_date('11-03-2019 11:16:42', 'dd-mm-yyyy hh24:mi:ss'), 1, '对错题，1选“对”，0选“错”');
    
    insert into QUESTIONNAIRE_ANSWER_T (ACTIVITY_ID, USER_ID, QUESTIONID, ANSWEROPTION, CREATE_DATE, IF_VALID, ANSWERCONTENT)
    values (1, '18730733961', 3, '9', to_date('11-03-2019 11:16:42', 'dd-mm-yyyy hh24:mi:ss'), 1, '评分题：描述评分原因');
    
    insert into QUESTIONNAIRE_ANSWER_T (ACTIVITY_ID, USER_ID, QUESTIONID, ANSWEROPTION, CREATE_DATE, IF_VALID, ANSWERCONTENT)
    values (1, '18730733961', 5, '', to_date('11-03-2019 11:16:42', 'dd-mm-yyyy hh24:mi:ss'), 1, '填空1#填空2（旅客不能输入“#”）');
    
    insert into QUESTIONNAIRE_ANSWER_T (ACTIVITY_ID, USER_ID, QUESTIONID, ANSWEROPTION, CREATE_DATE, IF_VALID, ANSWERCONTENT)
    values (1, '18730733961', 1, '0001', to_date('11-03-2019 11:23:03', 'dd-mm-yyyy hh24:mi:ss'), 1, '');
    
    insert into QUESTIONNAIRE_ANSWER_T (ACTIVITY_ID, USER_ID, QUESTIONID, ANSWEROPTION, CREATE_DATE, IF_VALID, ANSWERCONTENT)
    values (1, '18730733961', 2, '0001,0002', to_date('11-03-2019 11:23:03', 'dd-mm-yyyy hh24:mi:ss'), 1, '多选题说明，因为都会用到所有选择了两个选项');
    
    insert into QUESTIONNAIRE_ANSWER_T (ACTIVITY_ID, USER_ID, QUESTIONID, ANSWEROPTION, CREATE_DATE, IF_VALID, ANSWERCONTENT)
    values (1, '18730733961', 4, '', to_date('11-03-2019 11:23:03', 'dd-mm-yyyy hh24:mi:ss'), 1, '简答题的答案');
    
    insert into QUESTIONNAIRE_ANSWER_T (ACTIVITY_ID, USER_ID, QUESTIONID, ANSWEROPTION, CREATE_DATE, IF_VALID, ANSWERCONTENT)
    values (1, '18730733961', 6, '1', to_date('11-03-2019 11:23:03', 'dd-mm-yyyy hh24:mi:ss'), 1, '是否题，1选“是”，0选“否”');
    
    insert into QUESTIONNAIRE_ANSWER_T (ACTIVITY_ID, USER_ID, QUESTIONID, ANSWEROPTION, CREATE_DATE, IF_VALID, ANSWERCONTENT)
    values (1, '18730733961', 7, '1', to_date('11-03-2019 11:23:03', 'dd-mm-yyyy hh24:mi:ss'), 1, '对错题，1选“对”，0选“错”');
    
    insert into QUESTIONNAIRE_ANSWER_T (ACTIVITY_ID, USER_ID, QUESTIONID, ANSWEROPTION, CREATE_DATE, IF_VALID, ANSWERCONTENT)
    values (1, '18730733961', 3, '9', to_date('11-03-2019 11:23:03', 'dd-mm-yyyy hh24:mi:ss'), 1, '评分题：描述评分原因');
    
    insert into QUESTIONNAIRE_ANSWER_T (ACTIVITY_ID, USER_ID, QUESTIONID, ANSWEROPTION, CREATE_DATE, IF_VALID, ANSWERCONTENT)
    values (1, '18730733961', 5, '', to_date('11-03-2019 11:23:03', 'dd-mm-yyyy hh24:mi:ss'), 1, '填空1#填空2（旅客不能输入“#”）');
    
    insert into QUESTIONNAIRE_ANSWER_T (ACTIVITY_ID, USER_ID, QUESTIONID, ANSWEROPTION, CREATE_DATE, IF_VALID, ANSWERCONTENT)
    values (1, '18730733961', 2, '0001,0002', to_date('11-03-2019 15:56:41', 'dd-mm-yyyy hh24:mi:ss'), 1, '多选题说明，因为都会用到所有选择了两个选项');
    
    insert into QUESTIONNAIRE_ANSWER_T (ACTIVITY_ID, USER_ID, QUESTIONID, ANSWEROPTION, CREATE_DATE, IF_VALID, ANSWERCONTENT)
    values (1, '18730733961', 4, '', to_date('11-03-2019 15:56:41', 'dd-mm-yyyy hh24:mi:ss'), 1, '简答题的答案');
    
    insert into QUESTIONNAIRE_ANSWER_T (ACTIVITY_ID, USER_ID, QUESTIONID, ANSWEROPTION, CREATE_DATE, IF_VALID, ANSWERCONTENT)
    values (1, '18730733961', 6, '1', to_date('11-03-2019 15:56:41', 'dd-mm-yyyy hh24:mi:ss'), 1, '是否题，1选“是”，0选“否”');
    
    insert into QUESTIONNAIRE_ANSWER_T (ACTIVITY_ID, USER_ID, QUESTIONID, ANSWEROPTION, CREATE_DATE, IF_VALID, ANSWERCONTENT)
    values (1, '18730733961', 7, '1', to_date('11-03-2019 15:56:41', 'dd-mm-yyyy hh24:mi:ss'), 1, '对错题，1选“对”，0选“错”');
    
    insert into QUESTIONNAIRE_ANSWER_T (ACTIVITY_ID, USER_ID, QUESTIONID, ANSWEROPTION, CREATE_DATE, IF_VALID, ANSWERCONTENT)
    values (1, '18730733961', 3, '9', to_date('11-03-2019 15:56:41', 'dd-mm-yyyy hh24:mi:ss'), 1, '评分题：描述评分原因');
    
    insert into QUESTIONNAIRE_ANSWER_T (ACTIVITY_ID, USER_ID, QUESTIONID, ANSWEROPTION, CREATE_DATE, IF_VALID, ANSWERCONTENT)
    values (1, '18730733961', 5, '', to_date('11-03-2019 15:56:41', 'dd-mm-yyyy hh24:mi:ss'), 1, '填空1#填空2（旅客不能输入“#”）');
    
    insert into QUESTIONNAIRE_ANSWER_T (ACTIVITY_ID, USER_ID, QUESTIONID, ANSWEROPTION, CREATE_DATE, IF_VALID, ANSWERCONTENT)
    values (1, '18730733961', 1, '0001', to_date('12-03-2019 14:53:54', 'dd-mm-yyyy hh24:mi:ss'), 1, '');
    
    insert into QUESTIONNAIRE_ANSWER_T (ACTIVITY_ID, USER_ID, QUESTIONID, ANSWEROPTION, CREATE_DATE, IF_VALID, ANSWERCONTENT)
    values (1, '18730733961', 2, '0001,0002', to_date('12-03-2019 14:53:54', 'dd-mm-yyyy hh24:mi:ss'), 1, '多选题说明，因为都会用到所有选择了两个选项');
    
    insert into QUESTIONNAIRE_ANSWER_T (ACTIVITY_ID, USER_ID, QUESTIONID, ANSWEROPTION, CREATE_DATE, IF_VALID, ANSWERCONTENT)
    values (1, '18730733961', 4, '', to_date('12-03-2019 14:53:54', 'dd-mm-yyyy hh24:mi:ss'), 1, '简答题的答案');
    
    insert into QUESTIONNAIRE_ANSWER_T (ACTIVITY_ID, USER_ID, QUESTIONID, ANSWEROPTION, CREATE_DATE, IF_VALID, ANSWERCONTENT)
    values (1, '18730733961', 6, '1', to_date('12-03-2019 14:53:54', 'dd-mm-yyyy hh24:mi:ss'), 1, '是否题，1选“是”，0选“否”');
    
    insert into QUESTIONNAIRE_ANSWER_T (ACTIVITY_ID, USER_ID, QUESTIONID, ANSWEROPTION, CREATE_DATE, IF_VALID, ANSWERCONTENT)
    values (1, '18730733961', 7, '1', to_date('12-03-2019 14:53:54', 'dd-mm-yyyy hh24:mi:ss'), 1, '对错题，1选“对”，0选“错”');
    
    insert into QUESTIONNAIRE_ANSWER_T (ACTIVITY_ID, USER_ID, QUESTIONID, ANSWEROPTION, CREATE_DATE, IF_VALID, ANSWERCONTENT)
    values (1, '18730733961', 3, '9', to_date('12-03-2019 14:53:54', 'dd-mm-yyyy hh24:mi:ss'), 1, '评分题：描述评分原因');
    
    insert into QUESTIONNAIRE_ANSWER_T (ACTIVITY_ID, USER_ID, QUESTIONID, ANSWEROPTION, CREATE_DATE, IF_VALID, ANSWERCONTENT)
    values (1, '18730733961', 5, '', to_date('12-03-2019 14:53:54', 'dd-mm-yyyy hh24:mi:ss'), 1, '填空1#填空2（旅客不能输入“#”）');
    
    insert into QUESTIONNAIRE_ANSWER_T (ACTIVITY_ID, USER_ID, QUESTIONID, ANSWEROPTION, CREATE_DATE, IF_VALID, ANSWERCONTENT)
    values (1, '18730733961', 2, '0001,0002', to_date('11-03-2019 22:46:33', 'dd-mm-yyyy hh24:mi:ss'), 1, '多选题说明，因为都会用到所有选择了两个选项');
    
    insert into QUESTIONNAIRE_ANSWER_T (ACTIVITY_ID, USER_ID, QUESTIONID, ANSWEROPTION, CREATE_DATE, IF_VALID, ANSWERCONTENT)
    values (1, '18730733961', 4, '', to_date('11-03-2019 22:46:33', 'dd-mm-yyyy hh24:mi:ss'), 1, '简答题的答案');
    
    insert into QUESTIONNAIRE_ANSWER_T (ACTIVITY_ID, USER_ID, QUESTIONID, ANSWEROPTION, CREATE_DATE, IF_VALID, ANSWERCONTENT)
    values (1, '18730733961', 6, '1', to_date('11-03-2019 22:46:33', 'dd-mm-yyyy hh24:mi:ss'), 1, '是否题，1选“是”，0选“否”');
    
    insert into QUESTIONNAIRE_ANSWER_T (ACTIVITY_ID, USER_ID, QUESTIONID, ANSWEROPTION, CREATE_DATE, IF_VALID, ANSWERCONTENT)
    values (1, '18730733961', 7, '1', to_date('11-03-2019 22:46:33', 'dd-mm-yyyy hh24:mi:ss'), 1, '对错题，1选“对”，0选“错”');
    
    insert into QUESTIONNAIRE_ANSWER_T (ACTIVITY_ID, USER_ID, QUESTIONID, ANSWEROPTION, CREATE_DATE, IF_VALID, ANSWERCONTENT)
    values (1, '18730733961', 3, '9', to_date('11-03-2019 22:46:33', 'dd-mm-yyyy hh24:mi:ss'), 1, '评分题：描述评分原因');
    
    insert into QUESTIONNAIRE_ANSWER_T (ACTIVITY_ID, USER_ID, QUESTIONID, ANSWEROPTION, CREATE_DATE, IF_VALID, ANSWERCONTENT)
    values (1, '18730733961', 5, '', to_date('11-03-2019 22:46:33', 'dd-mm-yyyy hh24:mi:ss'), 1, '填空1#填空2（旅客不能输入“#”）');
    
    insert into QUESTIONNAIRE_ANSWER_T (ACTIVITY_ID, USER_ID, QUESTIONID, ANSWEROPTION, CREATE_DATE, IF_VALID, ANSWERCONTENT)
    values (1, '18730733961', 1, '0001', to_date('12-03-2019 11:26:36', 'dd-mm-yyyy hh24:mi:ss'), 1, '');
    
    insert into QUESTIONNAIRE_ANSWER_T (ACTIVITY_ID, USER_ID, QUESTIONID, ANSWEROPTION, CREATE_DATE, IF_VALID, ANSWERCONTENT)
    values (1, '18730733961', 2, '0001,0002', to_date('12-03-2019 11:26:36', 'dd-mm-yyyy hh24:mi:ss'), 1, '多选题说明，因为都会用到所有选择了两个选项');
    
    insert into QUESTIONNAIRE_ANSWER_T (ACTIVITY_ID, USER_ID, QUESTIONID, ANSWEROPTION, CREATE_DATE, IF_VALID, ANSWERCONTENT)
    values (1, '18730733961', 4, '', to_date('12-03-2019 11:26:36', 'dd-mm-yyyy hh24:mi:ss'), 1, '简答题的答案');
    
    insert into QUESTIONNAIRE_ANSWER_T (ACTIVITY_ID, USER_ID, QUESTIONID, ANSWEROPTION, CREATE_DATE, IF_VALID, ANSWERCONTENT)
    values (1, '18730733961', 6, '1', to_date('12-03-2019 11:26:36', 'dd-mm-yyyy hh24:mi:ss'), 1, '是否题，1选“是”，0选“否”');
    
    insert into QUESTIONNAIRE_ANSWER_T (ACTIVITY_ID, USER_ID, QUESTIONID, ANSWEROPTION, CREATE_DATE, IF_VALID, ANSWERCONTENT)
    values (1, '18730733961', 7, '1', to_date('12-03-2019 11:26:36', 'dd-mm-yyyy hh24:mi:ss'), 1, '对错题，1选“对”，0选“错”');
    
    insert into QUESTIONNAIRE_ANSWER_T (ACTIVITY_ID, USER_ID, QUESTIONID, ANSWEROPTION, CREATE_DATE, IF_VALID, ANSWERCONTENT)
    values (1, '18730733961', 3, '9', to_date('12-03-2019 11:26:36', 'dd-mm-yyyy hh24:mi:ss'), 1, '评分题：描述评分原因');
    
    insert into QUESTIONNAIRE_ANSWER_T (ACTIVITY_ID, USER_ID, QUESTIONID, ANSWEROPTION, CREATE_DATE, IF_VALID, ANSWERCONTENT)
    values (1, '18730733961', 5, '', to_date('12-03-2019 11:26:36', 'dd-mm-yyyy hh24:mi:ss'), 1, '填空1#填空2（旅客不能输入“#”）');
    
    
    
    --7.STANDARD_ANSWER_SCORE
    --SELECT * FROM STANDARD_ANSWER_SCORE T;--旅客评分表--无业务需要暂无评分
    -- Create table
    create table STANDARD_ANSWER_SCORE
    (
      ACTIVITY_ID     NUMBER(4),
      QUESTIONID      NUMBER(8),
      STANDARD_ANSWER VARCHAR2(516) default '',
      SCORE           NUMBER(4,2),
      IF_VALID        NUMBER(2),
      REMARK          VARCHAR2(150)
    );
    -- Add comments to the table 
    comment on table STANDARD_ANSWER_SCORE is '答题评分表';
    -- Add comments to the columns 
    comment on column STANDARD_ANSWER_SCORE.ACTIVITY_ID is '活动id，当该参数为0时是默认答案，当含有活动id的答案时以该答案为准';
    comment on column STANDARD_ANSWER_SCORE.QUESTIONID is '题目id';
    comment on column STANDARD_ANSWER_SCORE.STANDARD_ANSWER is '标准答案';
    comment on column STANDARD_ANSWER_SCORE.SCORE is '得分';
    comment on column STANDARD_ANSWER_SCORE.IF_VALID is '是否有效:1有效 0无效';
    comment on column STANDARD_ANSWER_SCORE.REMARK is '描述';

查看用户答题记录
    
    --查询这个版本的所有题，把所有题号都放入下面语句
    select * from questionnaire_info_t t where t.question_version='1' and lang='中文' and if_valid='1';
    select * from QUE_ANSWER_FREQUENCY_T t;
    --查看用户答题记录
    select aa.create_date,aa.user_id,aa.activity_id
    ,a.questionid as "a",a.answeroption,a.answercontent
    ,b.questionid as "b",b.answeroption,b.answercontent
    ,c.questionid as "c",c.answeroption,c.answercontent
    ,d.questionid as "d",d.answeroption,d.answercontent
    ,e.questionid as "e",e.answeroption,e.answercontent
    ,f.questionid as "f",f.answeroption,f.answercontent
    ,g.questionid as "g",g.answeroption,g.answercontent
     from
    (select user_id,activity_id,max(create_date) as create_date from QUE_ANSWER_FREQUENCY_T where activity_id='1' and if_valid='1' group by user_id,activity_id,question_version) aa
    left join
    (select user_id,activity_id,questionid,answeroption,answercontent,create_date from questionnaire_answer_t t where if_valid='1' and questionid='1' ) a
    on a.user_id=aa.user_id and a.create_date = aa.create_date and a.activity_id = aa.activity_id
    left join
    (select user_id,activity_id,questionid,answeroption,answercontent,create_date from questionnaire_answer_t t where if_valid='1' and questionid='2' ) b
    on aa.user_id = b.user_id and aa.create_date=b.create_date and aa.activity_id=b.activity_id
    left join
    (select user_id,activity_id,questionid,answeroption,answercontent,create_date from questionnaire_answer_t t where if_valid='1' and questionid='3' ) c
    on aa.user_id = c.user_id and aa.create_date=c.create_date and aa.activity_id=c.activity_id
    left join
    (select user_id,activity_id,questionid,answeroption,answercontent,create_date from questionnaire_answer_t t where if_valid='1' and questionid='4' ) d
    on d.user_id = aa.user_id and d.create_date=aa.create_date and d.activity_id=aa.activity_id
    left join
    (select user_id,activity_id,questionid,answeroption,answercontent,create_date from questionnaire_answer_t t where if_valid='1' and questionid='5' ) e
    on aa.user_id = e.user_id and aa.create_date=e.create_date and aa.activity_id=e.activity_id
    left join
    (select user_id,activity_id,questionid,answeroption,answercontent,create_date from questionnaire_answer_t t where if_valid='1' and questionid='6' ) f
    on f.user_id = aa.user_id and f.create_date=aa.create_date and f.activity_id=aa.activity_id
    left join
    (select user_id,activity_id,questionid,answeroption,answercontent,create_date from questionnaire_answer_t t where if_valid='1' and questionid='7' ) g
    on aa.user_id = g.user_id and aa.create_date=g.create_date and aa.activity_id=g.activity_id;

**希望对你有帮助，祝你有一个好心情，加油！**

**若有错误、不全、可优化的点，欢迎纠正与补充！**