2.调查问卷-建表

男娘i 2022-03-09 04:56 315阅读 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. --1.QUESTIONNAIRE_MANAGE_T
  2. --SELECT * FROM QUESTIONNAIRE_MANAGE_T T;--问卷活动管理表
  3. -- Create table
  4. create table QUESTIONNAIRE_MANAGE_T
  5. (
  6.   ID                   VARCHAR2(32),
  7.   ACTIVITY_ID          NUMBER(6),
  8.   QUESTION_VERSION     NUMBER(4),
  9.   START_DATE           DATE default SYSDATE,
  10.   END_DATE             DATE default SYSDATE,
  11.   ANSWER_LEVEL         VARCHAR2(100),
  12.   ANSWER_NUMLIMIT      NUMBER(6),
  13.   INDIVIDUALIZATION    VARCHAR2(100),
  14.   CREATE_DATE          DATE default SYSDATE,
  15.   CTEATOR              VARCHAR2(300),
  16.   UPD_TIME             DATE,
  17.   UPD_USER             VARCHAR2(300),
  18.   IF_VALID             NUMBER(2) default 1,
  19.   QUESTIONTYPE         VARCHAR2(300),
  20.   CALL_LIMIT           VARCHAR2(100),
  21.   ANSWER_TABLE_NAME    VARCHAR2(100),
  22.   FREQUENCY_TABLE_NAME VARCHAR2(100),
  23.   REMARK               VARCHAR2(150)
  24. );
  25. -- Add comments to the table 
  26. comment on table QUESTIONNAIRE_MANAGE_T is '调查问卷题库管理表';
  27. -- Add comments to the columns 
  28. comment on column QUESTIONNAIRE_MANAGE_T.ID is '问卷id';
  29. comment on column QUESTIONNAIRE_MANAGE_T.ACTIVITY_ID is '问卷活动id,从1开始，逐个活动递增';
  30. comment on column QUESTIONNAIRE_MANAGE_T.QUESTION_VERSION is '题版本，从1开始，需要根据实际情况递增';
  31. comment on column QUESTIONNAIRE_MANAGE_T.START_DATE is '问卷开始时间';
  32. comment on column QUESTIONNAIRE_MANAGE_T.END_DATE is '问卷结束时间';
  33. comment on column QUESTIONNAIRE_MANAGE_T.ANSWER_LEVEL is '问卷限制会员级别';
  34. comment on column QUESTIONNAIRE_MANAGE_T.ANSWER_NUMLIMIT is '答题次数限制:1是有1次答题机会';
  35. comment on column QUESTIONNAIRE_MANAGE_T.INDIVIDUALIZATION is '问卷个性化展示标识';
  36. comment on column QUESTIONNAIRE_MANAGE_T.CREATE_DATE is '创建时间';
  37. comment on column QUESTIONNAIRE_MANAGE_T.CTEATOR is '创建人';
  38. comment on column QUESTIONNAIRE_MANAGE_T.UPD_TIME is '修改时间';
  39. comment on column QUESTIONNAIRE_MANAGE_T.UPD_USER is '修改人';
  40. comment on column QUESTIONNAIRE_MANAGE_T.IF_VALID is '是否有效1-有效 0-无效，无效时相当与关闭活动';
  41. comment on column QUESTIONNAIRE_MANAGE_T.QUESTIONTYPE is '本次问卷的所有题型';
  42. comment on column QUESTIONNAIRE_MANAGE_T.CALL_LIMIT is '可以调用查询旅客填写次数的接口名';
  43. comment on column QUESTIONNAIRE_MANAGE_T.ANSWER_TABLE_NAME is '问卷答题表名';
  44. comment on column QUESTIONNAIRE_MANAGE_T.FREQUENCY_TABLE_NAME is '问卷答题次数表名';
  45. comment on column QUESTIONNAIRE_MANAGE_T.REMARK is '描述';
  47. 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)
  48. 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', '');
  51. --2.QUESTIONNAIRE_MANAGE_EXTEND_T
  52. --SELECT * FROM QUESTIONNAIRE_MANAGE_EXTEND_T T;--问卷活动管理扩展表
  53. -- Create table
  54. create table QUESTIONNAIRE_MANAGE_EXTEND_T
  55. (
  56.   ACTIVITY_ID      NUMBER(6),
  57.   QUESTION_VERSION NUMBER(4),
  58.   QUESTIONID_NAME  VARCHAR2(300),
  59.   QUESTIONCONTENT  VARCHAR2(2000),
  60.   URL_PARAM        VARCHAR2(1000),
  61.   CREATE_DATE      DATE default SYSDATE,
  62.   CTEATOR          VARCHAR2(300),
  63.   UPD_TIME         DATE,
  64.   UPD_USER         VARCHAR2(300),
  65.   IF_VALID         NUMBER(2) default 1,
  66.   LANG             VARCHAR2(8),
  67.   REMARK           VARCHAR2(150)
  68. );
  69. -- Add comments to the table 
  70. comment on table QUESTIONNAIRE_MANAGE_EXTEND_T is '调查问卷题库管理表扩展';
  71. -- Add comments to the columns 
  72. comment on column QUESTIONNAIRE_MANAGE_EXTEND_T.ACTIVITY_ID is '活动id';
  73. comment on column QUESTIONNAIRE_MANAGE_EXTEND_T.QUESTION_VERSION is '题版本号';
  74. comment on column QUESTIONNAIRE_MANAGE_EXTEND_T.QUESTIONID_NAME is '活动题目';
  75. comment on column QUESTIONNAIRE_MANAGE_EXTEND_T.QUESTIONCONTENT is '活动描述';
  76. comment on column QUESTIONNAIRE_MANAGE_EXTEND_T.URL_PARAM is '图片等url地址';
  77. comment on column QUESTIONNAIRE_MANAGE_EXTEND_T.CREATE_DATE is '创建时间';
  78. comment on column QUESTIONNAIRE_MANAGE_EXTEND_T.CTEATOR is '创建人';
  79. comment on column QUESTIONNAIRE_MANAGE_EXTEND_T.UPD_TIME is '修改时间';
  80. comment on column QUESTIONNAIRE_MANAGE_EXTEND_T.UPD_USER is '修改人';
  81. comment on column QUESTIONNAIRE_MANAGE_EXTEND_T.IF_VALID is '是否有效 1-有效 0-无效，无效时相当与删除';
  82. comment on column QUESTIONNAIRE_MANAGE_EXTEND_T.LANG is '语言';
  83. comment on column QUESTIONNAIRE_MANAGE_EXTEND_T.REMARK is '描述';
  85. 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)
  86. values (1, 1, '第一套题', '第一套题简介', '第一套题封面url', to_date('19-02-2019', 'dd-mm-yyyy'), 'cheng', null, '', 1, '中文', '');
  88. 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)
  89. values (1, 1, '第一套题（英文）', '第一套题简介（英文）', '第一套题封面英文url', to_date('19-02-2019', 'dd-mm-yyyy'), 'cheng', null, '', 1, '英文', '');
  91. 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)
  92. values (1, 1, '第一套题（日文）', '第一套题简介（日文）', '第一套题封面日文url', to_date('19-02-2019', 'dd-mm-yyyy'), 'cheng', null, '', 1, '日文', '');
  96. --3.QUE_ANSWER_FREQUENCY_T
  97. --SELECT * FROM QUE_ANSWER_FREQUENCY_T T;--答题次数查询表
  98. -- Create table
  99. create table QUE_ANSWER_FREQUENCY_T
  100. (
  101.   USER_ID          VARCHAR2(32),
  102.   ACTIVITY_ID      NUMBER(6),
  103.   QUESTION_VERSION NUMBER(4),
  104.   CREATE_DATE      DATE default SYSDATE,
  105.   IF_VALID         NUMBER(2) default 1,
  106.   REMARK           VARCHAR2(150)
  107. );
  108. -- Add comments to the table 
  109. comment on table QUE_ANSWER_FREQUENCY_T is '调查问卷题库答题次数记录表';
  110. -- Add comments to the columns 
  111. comment on column QUE_ANSWER_FREQUENCY_T.USER_ID is '用户唯一id';
  112. comment on column QUE_ANSWER_FREQUENCY_T.ACTIVITY_ID is '问卷活动id';
  113. comment on column QUE_ANSWER_FREQUENCY_T.QUESTION_VERSION is '问卷版本';
  114. comment on column QUE_ANSWER_FREQUENCY_T.CREATE_DATE is '创建时间';
  115. comment on column QUE_ANSWER_FREQUENCY_T.IF_VALID is '是否有效：1有效 0无效';
  116. comment on column QUE_ANSWER_FREQUENCY_T.REMARK is '描述';
  118. insert into QUE_ANSWER_FREQUENCY_T (USER_ID, ACTIVITY_ID, QUESTION_VERSION, CREATE_DATE, IF_VALID, REMARK)
  119. values ('18730733961', 1, 1, to_date('04-03-2019', 'dd-mm-yyyy'), 1, '');
  121. insert into QUE_ANSWER_FREQUENCY_T (USER_ID, ACTIVITY_ID, QUESTION_VERSION, CREATE_DATE, IF_VALID, REMARK)
  122. values ('18730733961', 1, 1, to_date('11-03-2019 11:12:29', 'dd-mm-yyyy hh24:mi:ss'), 1, '');
  124. insert into QUE_ANSWER_FREQUENCY_T (USER_ID, ACTIVITY_ID, QUESTION_VERSION, CREATE_DATE, IF_VALID, REMARK)
  125. values ('18730733961', 1, 1, to_date('11-03-2019 11:16:42', 'dd-mm-yyyy hh24:mi:ss'), 1, '');
  127. insert into QUE_ANSWER_FREQUENCY_T (USER_ID, ACTIVITY_ID, QUESTION_VERSION, CREATE_DATE, IF_VALID, REMARK)
  128. values ('18730733961', 1, 1, to_date('11-03-2019 11:23:03', 'dd-mm-yyyy hh24:mi:ss'), 1, '');
  130. insert into QUE_ANSWER_FREQUENCY_T (USER_ID, ACTIVITY_ID, QUESTION_VERSION, CREATE_DATE, IF_VALID, REMARK)
  131. values ('18730733961', 1, 1, to_date('11-03-2019 15:56:41', 'dd-mm-yyyy hh24:mi:ss'), 1, '');
  133. insert into QUE_ANSWER_FREQUENCY_T (USER_ID, ACTIVITY_ID, QUESTION_VERSION, CREATE_DATE, IF_VALID, REMARK)
  134. values ('18730733961', 1, 1, to_date('11-03-2019 22:46:33', 'dd-mm-yyyy hh24:mi:ss'), 1, '');
  136. insert into QUE_ANSWER_FREQUENCY_T (USER_ID, ACTIVITY_ID, QUESTION_VERSION, CREATE_DATE, IF_VALID, REMARK)
  137. values ('18730733961', 1, 1, to_date('12-03-2019 11:26:36', 'dd-mm-yyyy hh24:mi:ss'), 1, '');
  139. insert into QUE_ANSWER_FREQUENCY_T (USER_ID, ACTIVITY_ID, QUESTION_VERSION, CREATE_DATE, IF_VALID, REMARK)
  140. values ('18730733961', 1, 1, to_date('12-03-2019 14:53:54', 'dd-mm-yyyy hh24:mi:ss'), 1, '');
  143. --4.QUESTIONNAIRE_INFO_T
  144. --SELECT * FROM QUESTIONNAIRE_INFO_T T;--题干表
  145. -- Create table
  146. create table QUESTIONNAIRE_INFO_T
  147. (
  148.   QUESTIONID           NUMBER(8),
  149.   QUESTION_VERSION     NUMBER(4),
  150.   QUESTIONTYPE         VARCHAR2(32),
  151.   QUESTIONCONTENT      VARCHAR2(1000),
  152.   ANSWER_LEVEL         NUMBER(2),
  153.   REQUIRED             NUMBER(2),
  154.   QUESTION_SEQUENCE    NUMBER(6),
  155.   WATERMARK            VARCHAR2(150),
  156.   INDIVIDUALIZATION    VARCHAR2(32),
  157.   CHOICE_BLANKS_NUMBER NUMBER(2) default 1,
  158.   SHOW_BLANKS          VARCHAR2(64),
  159.   MINSCORE             NUMBER(3) default 0,
  160.   MAXSCORE             NUMBER(3) default 0,
  161.   OPTIONSCORE          NUMBER(3) default 10,
  162.   IF_VALID             NUMBER(2) default 1,
  163.   CREATE_DATE          DATE default SYSDATE,
  164.   LANG                 VARCHAR2(8),
  165.   URL_PARAM            VARCHAR2(300),
  166.   REMARK               VARCHAR2(150)
  167. );
  168. -- Add comments to the table 
  169. comment on table QUESTIONNAIRE_INFO_T is '调查问卷题库表';
  170. -- Add comments to the columns 
  171. comment on column QUESTIONNAIRE_INFO_T.QUESTIONID is '题目ID';
  172. comment on column QUESTIONNAIRE_INFO_T.QUESTION_VERSION is '题的版本号';
  173. comment on column QUESTIONNAIRE_INFO_T.QUESTIONTYPE is '题目类型 single_choice单选/multiple_choice多选/score评分/essay简答/fill_blanks填空/yes_no是否题/true_false判断题';
  174. comment on column QUESTIONNAIRE_INFO_T.QUESTIONCONTENT is '题目内容，如：今天天气如何？';
  175. comment on column QUESTIONNAIRE_INFO_T.ANSWER_LEVEL is '问题等级，可以按等级给旅客展示';
  176. comment on column QUESTIONNAIRE_INFO_T.REQUIRED is '是否必答：1是必填 0非必填';
  177. comment on column QUESTIONNAIRE_INFO_T.QUESTION_SEQUENCE is '题展示的题号的排序，用来对前台排序展示';
  178. comment on column QUESTIONNAIRE_INFO_T.WATERMARK is '题干中的水印';
  179. comment on column QUESTIONNAIRE_INFO_T.INDIVIDUALIZATION is '个性化展示标志';
  180. comment on column QUESTIONNAIRE_INFO_T.CHOICE_BLANKS_NUMBER is '选则最大选则数--填空题填空数--其他题型默认为1';
  181. comment on column QUESTIONNAIRE_INFO_T.SHOW_BLANKS is '展示具体说明框的扩展信息表选项id字段';
  182. comment on column QUESTIONNAIRE_INFO_T.MINSCORE is '评分题最小评分数，默认为0';
  183. comment on column QUESTIONNAIRE_INFO_T.MAXSCORE is '评分题最大评分数，默认为10';
  184. comment on column QUESTIONNAIRE_INFO_T.OPTIONSCORE is '评分题默认评分数，默认为0';
  185. comment on column QUESTIONNAIRE_INFO_T.IF_VALID is '是否有效 0无效 1有效 ';
  186. comment on column QUESTIONNAIRE_INFO_T.CREATE_DATE is '创建时间';
  187. comment on column QUESTIONNAIRE_INFO_T.LANG is '语言';
  188. comment on column QUESTIONNAIRE_INFO_T.URL_PARAM is '题干包含图片url';
  189. comment on column QUESTIONNAIRE_INFO_T.REMARK is '描述';
  192. 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)
  193. values (7, 1, 'true_false', '对错题：中文', 1, 1, 700, '题干水印', '公共', 1, '', 0, 10, 0, 1, to_date('27-11-2018', 'dd-mm-yyyy'), '中文', '', '');
  195. 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)
  196. values (2, 1, 'multiple_choice', '多选题：中文：多选', 0, 1, 200, '题干水印', '有午餐可填', 1, '0001,0002', 0, 10, 0, 1, to_date('27-11-2018', 'dd-mm-yyyy'), '中文', '', '');
  198. 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)
  199. values (1, 1, 'single_choice', '单选题：日文：天气如何', 0, 1, 100, '题干水印', '有午餐可填', 1, '', 0, 10, 0, 1, to_date('27-11-2018', 'dd-mm-yyyy'), 'jn', '', '');
  201. 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)
  202. 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', '', '');
  204. 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)
  205. values (1, 1, 'single_choice', '单选题：中文：天气如何', 0, 1, 100, '题干水印', '有午餐可填', 1, '', 0, 10, 0, 1, to_date('27-11-2018', 'dd-mm-yyyy'), '中文', '', '');
  207. 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)
  208. values (3, 1, 'score', '评分题：中文', 0, 1, 900, '题干水印', '有午餐可填', 1, '', 0, 10, 0, 1, to_date('27-11-2018', 'dd-mm-yyyy'), '中文', '', '');
  210. 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)
  211. values (4, 1, 'essay', '简单题：中文', 0, 1, 400, '题干水印', '有午餐可填', 1, '', 0, 10, 0, 1, to_date('27-11-2018', 'dd-mm-yyyy'), '中文', '', '');
  213. 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)
  214. values (5, 1, 'fill_blanks', '填空题：中文', 0, 1, 5000, '题干水印', '有早餐可填', 2, '', 0, 10, 0, 1, to_date('27-11-2018', 'dd-mm-yyyy'), '中文', '', '');
  216. 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)
  217. values (6, 1, 'yes_no', '是否题：中文', 0, 1, 600, '题干水印', '公共', 1, '', 0, 10, 0, 1, to_date('27-11-2018', 'dd-mm-yyyy'), '中文', '', '');
  222. --5.QUESTIONNAIRE_EXTEND_INFO_T
  223. --SELECT * FROM QUESTIONNAIRE_EXTEND_INFO_T T;--选项表
  224. -- Create table
  225. create table QUESTIONNAIRE_EXTEND_INFO_T
  226. (
  227.   QUESTIONID        VARCHAR2(8),
  228.   OPTIONID          VARCHAR2(8),
  229.   OPTIONCONTENT     VARCHAR2(1000),
  230.   WATERMARK         VARCHAR2(150),
  231.   IF_VALID          NUMBER(2),
  232.   QUESTION_SEQUENCE NUMBER(3),
  233.   CREATE_DATE       DATE default SYSDATE,
  234.   LANG              VARCHAR2(8),
  235.   URL_PARAM         VARCHAR2(300),
  236.   REMARK            VARCHAR2(150)
  237. );
  238. -- Add comments to the table 
  239. comment on table QUESTIONNAIRE_EXTEND_INFO_T is '题库扩展表';
  240. -- Add comments to the columns 
  241. comment on column QUESTIONNAIRE_EXTEND_INFO_T.QUESTIONID is '题目ID';
  242. comment on column QUESTIONNAIRE_EXTEND_INFO_T.OPTIONID is '选项ID，如：0001';
  243. comment on column QUESTIONNAIRE_EXTEND_INFO_T.OPTIONCONTENT is '选项内容，如：米饭';
  244. comment on column QUESTIONNAIRE_EXTEND_INFO_T.WATERMARK is '选项中的水印，适用整个题中只有一个水印';
  245. comment on column QUESTIONNAIRE_EXTEND_INFO_T.IF_VALID is '是否有效 0无效 1有效';
  246. comment on column QUESTIONNAIRE_EXTEND_INFO_T.QUESTION_SEQUENCE is '选项等内容展示的题号的排序';
  247. comment on column QUESTIONNAIRE_EXTEND_INFO_T.CREATE_DATE is '创建时间';
  248. comment on column QUESTIONNAIRE_EXTEND_INFO_T.URL_PARAM is '该选项的url';
  249. comment on column QUESTIONNAIRE_EXTEND_INFO_T.REMARK is '描述';
  251. insert into QUESTIONNAIRE_EXTEND_INFO_T (QUESTIONID, OPTIONID, OPTIONCONTENT, WATERMARK, IF_VALID, QUESTION_SEQUENCE, CREATE_DATE, LANG, URL_PARAM, REMARK)
  252. values ('1', '0001', '选项A', '选择A水印', 1, 100, to_date('28-11-2018 12:00:00', 'dd-mm-yyyy hh24:mi:ss'), '中文', '', '');
  254. insert into QUESTIONNAIRE_EXTEND_INFO_T (QUESTIONID, OPTIONID, OPTIONCONTENT, WATERMARK, IF_VALID, QUESTION_SEQUENCE, CREATE_DATE, LANG, URL_PARAM, REMARK)
  255. values ('1', '0001', '选项A英文', '选择A英文水印', 1, 100, to_date('28-11-2018', 'dd-mm-yyyy'), 'en', '', '');
  257. insert into QUESTIONNAIRE_EXTEND_INFO_T (QUESTIONID, OPTIONID, OPTIONCONTENT, WATERMARK, IF_VALID, QUESTION_SEQUENCE, CREATE_DATE, LANG, URL_PARAM, REMARK)
  258. values ('1', '0002', '选项B英文', '选择B英文水印', 1, 200, to_date('28-11-2018', 'dd-mm-yyyy'), 'en', '', '');
  260. insert into QUESTIONNAIRE_EXTEND_INFO_T (QUESTIONID, OPTIONID, OPTIONCONTENT, WATERMARK, IF_VALID, QUESTION_SEQUENCE, CREATE_DATE, LANG, URL_PARAM, REMARK)
  261. values ('1', '0001', '选项A日文', '选择A日文水印', 1, 100, to_date('28-11-2018', 'dd-mm-yyyy'), 'jn', '', '');
  263. insert into QUESTIONNAIRE_EXTEND_INFO_T (QUESTIONID, OPTIONID, OPTIONCONTENT, WATERMARK, IF_VALID, QUESTION_SEQUENCE, CREATE_DATE, LANG, URL_PARAM, REMARK)
  264. values ('1', '0002', '选项B', '选择B水印', 1, 200, to_date('28-11-2018', 'dd-mm-yyyy'), '中文', '', '');
  266. insert into QUESTIONNAIRE_EXTEND_INFO_T (QUESTIONID, OPTIONID, OPTIONCONTENT, WATERMARK, IF_VALID, QUESTION_SEQUENCE, CREATE_DATE, LANG, URL_PARAM, REMARK)
  267. values ('2', '0001', '选项A', '水印', 1, 100, to_date('28-11-2018', 'dd-mm-yyyy'), '中文', '', '');
  269. insert into QUESTIONNAIRE_EXTEND_INFO_T (QUESTIONID, OPTIONID, OPTIONCONTENT, WATERMARK, IF_VALID, QUESTION_SEQUENCE, CREATE_DATE, LANG, URL_PARAM, REMARK)
  270. values ('2', '0002', '选项B', '水印', 1, 200, to_date('28-11-2018', 'dd-mm-yyyy'), '中文', '', '');
  272. insert into QUESTIONNAIRE_EXTEND_INFO_T (QUESTIONID, OPTIONID, OPTIONCONTENT, WATERMARK, IF_VALID, QUESTION_SEQUENCE, CREATE_DATE, LANG, URL_PARAM, REMARK)
  273. values ('2', '0003', '选项C', '水印', 1, 300, to_date('28-11-2018', 'dd-mm-yyyy'), '中文', '', '');
  275. --6.QUESTIONNAIRE_ANSWER_T
  276. SELECT * FROM QUESTIONNAIRE_ANSWER_T T;--调查问卷答题表
  277. -- Create table
  278. create table QUESTIONNAIRE_ANSWER_T
  279. (
  280.   ACTIVITY_ID   NUMBER(6),
  281.   USER_ID       VARCHAR2(32),
  282.   QUESTIONID    NUMBER(8),
  283.   ANSWEROPTION  VARCHAR2(256),
  284.   CREATE_DATE   DATE default SYSDATE,
  285.   IF_VALID      NUMBER(2),
  286.   ANSWERCONTENT VARCHAR2(512)
  287. );
  288. -- Add comments to the table 
  289. comment on table QUESTIONNAIRE_ANSWER_T is '用户答题表';
  290. -- Add comments to the columns 
  291. comment on column QUESTIONNAIRE_ANSWER_T.ACTIVITY_ID is '问卷活动id';
  292. comment on column QUESTIONNAIRE_ANSWER_T.USER_ID is '用户唯一标识,若以后系统会员卡号扩位数等，该值不变/若没有可以为会员卡号';
  293. comment on column QUESTIONNAIRE_ANSWER_T.QUESTIONID is '题目id';
  294. comment on column QUESTIONNAIRE_ANSWER_T.ANSWEROPTION is '答题选项：选择/是否（1是 0否）/对错（1对 0错）/评分题选则';
  295. comment on column QUESTIONNAIRE_ANSWER_T.CREATE_DATE is '创建时间';
  296. comment on column QUESTIONNAIRE_ANSWER_T.IF_VALID is '是否有效:1有效 0无效';
  297. comment on column QUESTIONNAIRE_ANSWER_T.ANSWERCONTENT is '答题内容：选择/是否/对错/评分的选择说明 或 填空题 简答题';
  299. insert into QUESTIONNAIRE_ANSWER_T (ACTIVITY_ID, USER_ID, QUESTIONID, ANSWEROPTION, CREATE_DATE, IF_VALID, ANSWERCONTENT)
  300. values (1, '18730733961', 1, '0001', to_date('11-03-2019 11:16:42', 'dd-mm-yyyy hh24:mi:ss'), 1, '');
  302. insert into QUESTIONNAIRE_ANSWER_T (ACTIVITY_ID, USER_ID, QUESTIONID, ANSWEROPTION, CREATE_DATE, IF_VALID, ANSWERCONTENT)
  303. values (1, '18730733961', 2, '0001,0002', to_date('11-03-2019 11:16:42', 'dd-mm-yyyy hh24:mi:ss'), 1, '多选题说明，因为都会用到所有选择了两个选项');
  305. insert into QUESTIONNAIRE_ANSWER_T (ACTIVITY_ID, USER_ID, QUESTIONID, ANSWEROPTION, CREATE_DATE, IF_VALID, ANSWERCONTENT)
  306. values (1, '18730733961', 4, '', to_date('11-03-2019 11:16:42', 'dd-mm-yyyy hh24:mi:ss'), 1, '简答题的答案');
  308. insert into QUESTIONNAIRE_ANSWER_T (ACTIVITY_ID, USER_ID, QUESTIONID, ANSWEROPTION, CREATE_DATE, IF_VALID, ANSWERCONTENT)
  309. values (1, '18730733961', 6, '1', to_date('11-03-2019 11:16:42', 'dd-mm-yyyy hh24:mi:ss'), 1, '是否题，1选“是”，0选“否”');
  311. insert into QUESTIONNAIRE_ANSWER_T (ACTIVITY_ID, USER_ID, QUESTIONID, ANSWEROPTION, CREATE_DATE, IF_VALID, ANSWERCONTENT)
  312. values (1, '18730733961', 7, '1', to_date('11-03-2019 11:16:42', 'dd-mm-yyyy hh24:mi:ss'), 1, '对错题，1选“对”，0选“错”');
  314. insert into QUESTIONNAIRE_ANSWER_T (ACTIVITY_ID, USER_ID, QUESTIONID, ANSWEROPTION, CREATE_DATE, IF_VALID, ANSWERCONTENT)
  315. values (1, '18730733961', 3, '9', to_date('11-03-2019 11:16:42', 'dd-mm-yyyy hh24:mi:ss'), 1, '评分题：描述评分原因');
  317. insert into QUESTIONNAIRE_ANSWER_T (ACTIVITY_ID, USER_ID, QUESTIONID, ANSWEROPTION, CREATE_DATE, IF_VALID, ANSWERCONTENT)
  318. values (1, '18730733961', 5, '', to_date('11-03-2019 11:16:42', 'dd-mm-yyyy hh24:mi:ss'), 1, '填空1#填空2（旅客不能输入“#”）');
  320. insert into QUESTIONNAIRE_ANSWER_T (ACTIVITY_ID, USER_ID, QUESTIONID, ANSWEROPTION, CREATE_DATE, IF_VALID, ANSWERCONTENT)
  321. values (1, '18730733961', 1, '0001', to_date('11-03-2019 11:23:03', 'dd-mm-yyyy hh24:mi:ss'), 1, '');
  323. insert into QUESTIONNAIRE_ANSWER_T (ACTIVITY_ID, USER_ID, QUESTIONID, ANSWEROPTION, CREATE_DATE, IF_VALID, ANSWERCONTENT)
  324. values (1, '18730733961', 2, '0001,0002', to_date('11-03-2019 11:23:03', 'dd-mm-yyyy hh24:mi:ss'), 1, '多选题说明，因为都会用到所有选择了两个选项');
  326. insert into QUESTIONNAIRE_ANSWER_T (ACTIVITY_ID, USER_ID, QUESTIONID, ANSWEROPTION, CREATE_DATE, IF_VALID, ANSWERCONTENT)
  327. values (1, '18730733961', 4, '', to_date('11-03-2019 11:23:03', 'dd-mm-yyyy hh24:mi:ss'), 1, '简答题的答案');
  329. insert into QUESTIONNAIRE_ANSWER_T (ACTIVITY_ID, USER_ID, QUESTIONID, ANSWEROPTION, CREATE_DATE, IF_VALID, ANSWERCONTENT)
  330. values (1, '18730733961', 6, '1', to_date('11-03-2019 11:23:03', 'dd-mm-yyyy hh24:mi:ss'), 1, '是否题，1选“是”，0选“否”');
  332. insert into QUESTIONNAIRE_ANSWER_T (ACTIVITY_ID, USER_ID, QUESTIONID, ANSWEROPTION, CREATE_DATE, IF_VALID, ANSWERCONTENT)
  333. values (1, '18730733961', 7, '1', to_date('11-03-2019 11:23:03', 'dd-mm-yyyy hh24:mi:ss'), 1, '对错题，1选“对”，0选“错”');
  335. insert into QUESTIONNAIRE_ANSWER_T (ACTIVITY_ID, USER_ID, QUESTIONID, ANSWEROPTION, CREATE_DATE, IF_VALID, ANSWERCONTENT)
  336. values (1, '18730733961', 3, '9', to_date('11-03-2019 11:23:03', 'dd-mm-yyyy hh24:mi:ss'), 1, '评分题：描述评分原因');
  338. insert into QUESTIONNAIRE_ANSWER_T (ACTIVITY_ID, USER_ID, QUESTIONID, ANSWEROPTION, CREATE_DATE, IF_VALID, ANSWERCONTENT)
  339. values (1, '18730733961', 5, '', to_date('11-03-2019 11:23:03', 'dd-mm-yyyy hh24:mi:ss'), 1, '填空1#填空2（旅客不能输入“#”）');
  341. insert into QUESTIONNAIRE_ANSWER_T (ACTIVITY_ID, USER_ID, QUESTIONID, ANSWEROPTION, CREATE_DATE, IF_VALID, ANSWERCONTENT)
  342. values (1, '18730733961', 2, '0001,0002', to_date('11-03-2019 15:56:41', 'dd-mm-yyyy hh24:mi:ss'), 1, '多选题说明，因为都会用到所有选择了两个选项');
  344. insert into QUESTIONNAIRE_ANSWER_T (ACTIVITY_ID, USER_ID, QUESTIONID, ANSWEROPTION, CREATE_DATE, IF_VALID, ANSWERCONTENT)
  345. values (1, '18730733961', 4, '', to_date('11-03-2019 15:56:41', 'dd-mm-yyyy hh24:mi:ss'), 1, '简答题的答案');
  347. insert into QUESTIONNAIRE_ANSWER_T (ACTIVITY_ID, USER_ID, QUESTIONID, ANSWEROPTION, CREATE_DATE, IF_VALID, ANSWERCONTENT)
  348. values (1, '18730733961', 6, '1', to_date('11-03-2019 15:56:41', 'dd-mm-yyyy hh24:mi:ss'), 1, '是否题，1选“是”，0选“否”');
  350. insert into QUESTIONNAIRE_ANSWER_T (ACTIVITY_ID, USER_ID, QUESTIONID, ANSWEROPTION, CREATE_DATE, IF_VALID, ANSWERCONTENT)
  351. values (1, '18730733961', 7, '1', to_date('11-03-2019 15:56:41', 'dd-mm-yyyy hh24:mi:ss'), 1, '对错题，1选“对”，0选“错”');
  353. insert into QUESTIONNAIRE_ANSWER_T (ACTIVITY_ID, USER_ID, QUESTIONID, ANSWEROPTION, CREATE_DATE, IF_VALID, ANSWERCONTENT)
  354. values (1, '18730733961', 3, '9', to_date('11-03-2019 15:56:41', 'dd-mm-yyyy hh24:mi:ss'), 1, '评分题：描述评分原因');
  356. insert into QUESTIONNAIRE_ANSWER_T (ACTIVITY_ID, USER_ID, QUESTIONID, ANSWEROPTION, CREATE_DATE, IF_VALID, ANSWERCONTENT)
  357. values (1, '18730733961', 5, '', to_date('11-03-2019 15:56:41', 'dd-mm-yyyy hh24:mi:ss'), 1, '填空1#填空2（旅客不能输入“#”）');
  359. insert into QUESTIONNAIRE_ANSWER_T (ACTIVITY_ID, USER_ID, QUESTIONID, ANSWEROPTION, CREATE_DATE, IF_VALID, ANSWERCONTENT)
  360. values (1, '18730733961', 1, '0001', to_date('12-03-2019 14:53:54', 'dd-mm-yyyy hh24:mi:ss'), 1, '');
  362. insert into QUESTIONNAIRE_ANSWER_T (ACTIVITY_ID, USER_ID, QUESTIONID, ANSWEROPTION, CREATE_DATE, IF_VALID, ANSWERCONTENT)
  363. values (1, '18730733961', 2, '0001,0002', to_date('12-03-2019 14:53:54', 'dd-mm-yyyy hh24:mi:ss'), 1, '多选题说明，因为都会用到所有选择了两个选项');
  365. insert into QUESTIONNAIRE_ANSWER_T (ACTIVITY_ID, USER_ID, QUESTIONID, ANSWEROPTION, CREATE_DATE, IF_VALID, ANSWERCONTENT)
  366. values (1, '18730733961', 4, '', to_date('12-03-2019 14:53:54', 'dd-mm-yyyy hh24:mi:ss'), 1, '简答题的答案');
  368. insert into QUESTIONNAIRE_ANSWER_T (ACTIVITY_ID, USER_ID, QUESTIONID, ANSWEROPTION, CREATE_DATE, IF_VALID, ANSWERCONTENT)
  369. values (1, '18730733961', 6, '1', to_date('12-03-2019 14:53:54', 'dd-mm-yyyy hh24:mi:ss'), 1, '是否题，1选“是”，0选“否”');
  371. insert into QUESTIONNAIRE_ANSWER_T (ACTIVITY_ID, USER_ID, QUESTIONID, ANSWEROPTION, CREATE_DATE, IF_VALID, ANSWERCONTENT)
  372. values (1, '18730733961', 7, '1', to_date('12-03-2019 14:53:54', 'dd-mm-yyyy hh24:mi:ss'), 1, '对错题，1选“对”，0选“错”');
  374. insert into QUESTIONNAIRE_ANSWER_T (ACTIVITY_ID, USER_ID, QUESTIONID, ANSWEROPTION, CREATE_DATE, IF_VALID, ANSWERCONTENT)
  375. values (1, '18730733961', 3, '9', to_date('12-03-2019 14:53:54', 'dd-mm-yyyy hh24:mi:ss'), 1, '评分题：描述评分原因');
  377. insert into QUESTIONNAIRE_ANSWER_T (ACTIVITY_ID, USER_ID, QUESTIONID, ANSWEROPTION, CREATE_DATE, IF_VALID, ANSWERCONTENT)
  378. values (1, '18730733961', 5, '', to_date('12-03-2019 14:53:54', 'dd-mm-yyyy hh24:mi:ss'), 1, '填空1#填空2（旅客不能输入“#”）');
  380. insert into QUESTIONNAIRE_ANSWER_T (ACTIVITY_ID, USER_ID, QUESTIONID, ANSWEROPTION, CREATE_DATE, IF_VALID, ANSWERCONTENT)
  381. values (1, '18730733961', 2, '0001,0002', to_date('11-03-2019 22:46:33', 'dd-mm-yyyy hh24:mi:ss'), 1, '多选题说明，因为都会用到所有选择了两个选项');
  383. insert into QUESTIONNAIRE_ANSWER_T (ACTIVITY_ID, USER_ID, QUESTIONID, ANSWEROPTION, CREATE_DATE, IF_VALID, ANSWERCONTENT)
  384. values (1, '18730733961', 4, '', to_date('11-03-2019 22:46:33', 'dd-mm-yyyy hh24:mi:ss'), 1, '简答题的答案');
  386. insert into QUESTIONNAIRE_ANSWER_T (ACTIVITY_ID, USER_ID, QUESTIONID, ANSWEROPTION, CREATE_DATE, IF_VALID, ANSWERCONTENT)
  387. values (1, '18730733961', 6, '1', to_date('11-03-2019 22:46:33', 'dd-mm-yyyy hh24:mi:ss'), 1, '是否题，1选“是”，0选“否”');
  389. insert into QUESTIONNAIRE_ANSWER_T (ACTIVITY_ID, USER_ID, QUESTIONID, ANSWEROPTION, CREATE_DATE, IF_VALID, ANSWERCONTENT)
  390. values (1, '18730733961', 7, '1', to_date('11-03-2019 22:46:33', 'dd-mm-yyyy hh24:mi:ss'), 1, '对错题，1选“对”，0选“错”');
  392. insert into QUESTIONNAIRE_ANSWER_T (ACTIVITY_ID, USER_ID, QUESTIONID, ANSWEROPTION, CREATE_DATE, IF_VALID, ANSWERCONTENT)
  393. values (1, '18730733961', 3, '9', to_date('11-03-2019 22:46:33', 'dd-mm-yyyy hh24:mi:ss'), 1, '评分题：描述评分原因');
  395. insert into QUESTIONNAIRE_ANSWER_T (ACTIVITY_ID, USER_ID, QUESTIONID, ANSWEROPTION, CREATE_DATE, IF_VALID, ANSWERCONTENT)
  396. values (1, '18730733961', 5, '', to_date('11-03-2019 22:46:33', 'dd-mm-yyyy hh24:mi:ss'), 1, '填空1#填空2（旅客不能输入“#”）');
  398. insert into QUESTIONNAIRE_ANSWER_T (ACTIVITY_ID, USER_ID, QUESTIONID, ANSWEROPTION, CREATE_DATE, IF_VALID, ANSWERCONTENT)
  399. values (1, '18730733961', 1, '0001', to_date('12-03-2019 11:26:36', 'dd-mm-yyyy hh24:mi:ss'), 1, '');
  401. insert into QUESTIONNAIRE_ANSWER_T (ACTIVITY_ID, USER_ID, QUESTIONID, ANSWEROPTION, CREATE_DATE, IF_VALID, ANSWERCONTENT)
  402. values (1, '18730733961', 2, '0001,0002', to_date('12-03-2019 11:26:36', 'dd-mm-yyyy hh24:mi:ss'), 1, '多选题说明，因为都会用到所有选择了两个选项');
  404. insert into QUESTIONNAIRE_ANSWER_T (ACTIVITY_ID, USER_ID, QUESTIONID, ANSWEROPTION, CREATE_DATE, IF_VALID, ANSWERCONTENT)
  405. values (1, '18730733961', 4, '', to_date('12-03-2019 11:26:36', 'dd-mm-yyyy hh24:mi:ss'), 1, '简答题的答案');
  407. insert into QUESTIONNAIRE_ANSWER_T (ACTIVITY_ID, USER_ID, QUESTIONID, ANSWEROPTION, CREATE_DATE, IF_VALID, ANSWERCONTENT)
  408. values (1, '18730733961', 6, '1', to_date('12-03-2019 11:26:36', 'dd-mm-yyyy hh24:mi:ss'), 1, '是否题，1选“是”，0选“否”');
  410. insert into QUESTIONNAIRE_ANSWER_T (ACTIVITY_ID, USER_ID, QUESTIONID, ANSWEROPTION, CREATE_DATE, IF_VALID, ANSWERCONTENT)
  411. values (1, '18730733961', 7, '1', to_date('12-03-2019 11:26:36', 'dd-mm-yyyy hh24:mi:ss'), 1, '对错题，1选“对”，0选“错”');
  413. insert into QUESTIONNAIRE_ANSWER_T (ACTIVITY_ID, USER_ID, QUESTIONID, ANSWEROPTION, CREATE_DATE, IF_VALID, ANSWERCONTENT)
  414. values (1, '18730733961', 3, '9', to_date('12-03-2019 11:26:36', 'dd-mm-yyyy hh24:mi:ss'), 1, '评分题：描述评分原因');
  416. insert into QUESTIONNAIRE_ANSWER_T (ACTIVITY_ID, USER_ID, QUESTIONID, ANSWEROPTION, CREATE_DATE, IF_VALID, ANSWERCONTENT)
  417. values (1, '18730733961', 5, '', to_date('12-03-2019 11:26:36', 'dd-mm-yyyy hh24:mi:ss'), 1, '填空1#填空2（旅客不能输入“#”）');
  421. --7.STANDARD_ANSWER_SCORE
  422. --SELECT * FROM STANDARD_ANSWER_SCORE T;--旅客评分表--无业务需要暂无评分
  423. -- Create table
  424. create table STANDARD_ANSWER_SCORE
  425. (
  426.   ACTIVITY_ID     NUMBER(4),
  427.   QUESTIONID      NUMBER(8),
  428.   STANDARD_ANSWER VARCHAR2(516) default '',
  429.   SCORE           NUMBER(4,2),
  430.   IF_VALID        NUMBER(2),
  431.   REMARK          VARCHAR2(150)
  432. );
  433. -- Add comments to the table 
  434. comment on table STANDARD_ANSWER_SCORE is '答题评分表';
  435. -- Add comments to the columns 
  436. comment on column STANDARD_ANSWER_SCORE.ACTIVITY_ID is '活动id，当该参数为0时是默认答案，当含有活动id的答案时以该答案为准';
  437. comment on column STANDARD_ANSWER_SCORE.QUESTIONID is '题目id';
  438. comment on column STANDARD_ANSWER_SCORE.STANDARD_ANSWER is '标准答案';
  439. comment on column STANDARD_ANSWER_SCORE.SCORE is '得分';
  440. comment on column STANDARD_ANSWER_SCORE.IF_VALID is '是否有效:1有效 0无效';
  441. comment on column STANDARD_ANSWER_SCORE.REMARK is '描述';
  443. 查看用户答题记录
  445. --查询这个版本的所有题，把所有题号都放入下面语句
  446. select * from questionnaire_info_t t where t.question_version='1' and lang='中文' and if_valid='1';
  447. select * from QUE_ANSWER_FREQUENCY_T t;
  448. --查看用户答题记录
  449. select aa.create_date,aa.user_id,aa.activity_id
  450. ,a.questionid as "a",a.answeroption,a.answercontent
  451. ,b.questionid as "b",b.answeroption,b.answercontent
  452. ,c.questionid as "c",c.answeroption,c.answercontent
  453. ,d.questionid as "d",d.answeroption,d.answercontent
  454. ,e.questionid as "e",e.answeroption,e.answercontent
  455. ,f.questionid as "f",f.answeroption,f.answercontent
  456. ,g.questionid as "g",g.answeroption,g.answercontent
  457.  from
  458. (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
  459. left join
  460. (select user_id,activity_id,questionid,answeroption,answercontent,create_date from questionnaire_answer_t t where if_valid='1' and questionid='1' ) a
  461. on a.user_id=aa.user_id and a.create_date = aa.create_date and a.activity_id = aa.activity_id
  462. left join
  463. (select user_id,activity_id,questionid,answeroption,answercontent,create_date from questionnaire_answer_t t where if_valid='1' and questionid='2' ) b
  464. on aa.user_id = b.user_id and aa.create_date=b.create_date and aa.activity_id=b.activity_id
  465. left join
  466. (select user_id,activity_id,questionid,answeroption,answercontent,create_date from questionnaire_answer_t t where if_valid='1' and questionid='3' ) c
  467. on aa.user_id = c.user_id and aa.create_date=c.create_date and aa.activity_id=c.activity_id
  468. left join
  469. (select user_id,activity_id,questionid,answeroption,answercontent,create_date from questionnaire_answer_t t where if_valid='1' and questionid='4' ) d
  470. on d.user_id = aa.user_id and d.create_date=aa.create_date and d.activity_id=aa.activity_id
  471. left join
  472. (select user_id,activity_id,questionid,answeroption,answercontent,create_date from questionnaire_answer_t t where if_valid='1' and questionid='5' ) e
  473. on aa.user_id = e.user_id and aa.create_date=e.create_date and aa.activity_id=e.activity_id
  474. left join
  475. (select user_id,activity_id,questionid,answeroption,answercontent,create_date from questionnaire_answer_t t where if_valid='1' and questionid='6' ) f
  476. on f.user_id = aa.user_id and f.create_date=aa.create_date and f.activity_id=aa.activity_id
  477. left join
  478. (select user_id,activity_id,questionid,answeroption,answercontent,create_date from questionnaire_answer_t t where if_valid='1' and questionid='7' ) g
  479. on aa.user_id = g.user_id and aa.create_date=g.create_date and aa.activity_id=g.activity_id;

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

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

发表评论

表情：
评论列表 （有 0 条评论，315人围观）

还没有评论，来说两句吧...

相关阅读

    相关 SpringMVC+Mybatis写调查问卷管理系统

    最近一直想找点事情做，前几个星期马原老师说要做一份社会调查需要用到调查问卷，然后基本都是网上直接用现成的网站直接做的，我在想自己是不是也能做一个类似简单的管理系统呢。。然后一做

    蔚落蔚落/ 0 赞/ 274 阅读

    相关 HDU6344 调查问卷

    状态压缩 + 模拟 把AB串压缩成二进制，A用1表示，B用0表示。 枚举所有问题的子集，选中的问题用1表示，其余的用0表示。对于每个子集，我们去和所有问题按位与，这样对

    今天药忘吃喽~今天药忘吃喽~/ 0 赞/ 362 阅读