实现效果
数据库表：
问卷题目表
CREATE TABLE "public"."tc_exam_paper_subject" (
"subject_id" int4 DEFAULT nextval('tc_exam_paper_subject_subject_id_seq'::regclass) NOT NULL,
"parent_id" int4,
"level_no" int4,
"identifier" varchar(100) COLLATE "default",
"paper_id" int4,
"type" int4,
"code" varchar(50) COLLATE "default",
"name" varchar(50) COLLATE "default",
"note" varchar(200) COLLATE "default",
"remark" varchar(200) COLLATE "default",
"serial_no" varchar(20) COLLATE "default",
"sort_no" int4,
"is_required" bool,
"classification" int4,
"answer_type" int4,
"answer_length" int4,
"answer_decimal" int4,
"style" int4,
"pattern" int4,
"xml_config" varchar(1024) COLLATE "default",
"column_count" int4,
"score" numeric(18,4),
"org_id" int4,
"version_no" int4,
"created_by" int4,
"updated_by" int4,
"created_date" timestamp(6),
"updated_date" timestamp(6),
"class_type" int4,
"start_time" timestamp(6),
"end_time" timestamp(6),
"class_count" int4 DEFAULT 1 NOT NULL,
CONSTRAINT "tc_exam_paper_subject_pkey" PRIMARY KEY ("subject_id")
)
WITH (OIDS=FALSE);
ALTER TABLE "public"."tc_exam_paper_subject" OWNER TO "postgres";
COMMENT ON TABLE "public"."tc_exam_paper_subject" IS '医疗问卷题目';
COMMENT ON COLUMN "public"."tc_exam_paper_subject"."subject_id" IS '题目ID';
COMMENT ON COLUMN "public"."tc_exam_paper_subject"."parent_id" IS '上级题目ID';
COMMENT ON COLUMN "public"."tc_exam_paper_subject"."level_no" IS '层级号码';
COMMENT ON COLUMN "public"."tc_exam_paper_subject"."identifier" IS '问卷内容条目的全局唯一标识符';
COMMENT ON COLUMN "public"."tc_exam_paper_subject"."paper_id" IS '问卷ID';
COMMENT ON COLUMN "public"."tc_exam_paper_subject"."type" IS '问卷内容条目的分类 （0=分组, 1=题目,2=答案，3=题目与答案）';
COMMENT ON COLUMN "public"."tc_exam_paper_subject"."code" IS '编码';
COMMENT ON COLUMN "public"."tc_exam_paper_subject"."name" IS '名称';
COMMENT ON COLUMN "public"."tc_exam_paper_subject"."note" IS '说明';
COMMENT ON COLUMN "public"."tc_exam_paper_subject"."remark" IS '备注';
COMMENT ON COLUMN "public"."tc_exam_paper_subject"."serial_no" IS '编号';
COMMENT ON COLUMN "public"."tc_exam_paper_subject"."sort_no" IS '次序';
COMMENT ON COLUMN "public"."tc_exam_paper_subject"."is_required" IS '是否必填';
COMMENT ON COLUMN "public"."tc_exam_paper_subject"."classification" IS '题型（1=填空题 2=选择题 3=选择填空题）';
COMMENT ON COLUMN "public"."tc_exam_paper_subject"."answer_type" IS '答案数据类型 （1=数值 2=文本 3=日期）';
COMMENT ON COLUMN "public"."tc_exam_paper_subject"."answer_length" IS '答案长度';
COMMENT ON COLUMN "public"."tc_exam_paper_subject"."answer_decimal" IS '答案的小数位数';
COMMENT ON COLUMN "public"."tc_exam_paper_subject"."style" IS '样式 （0=无 1=单选题 2=多选题，3=文本填空）';
COMMENT ON COLUMN "public"."tc_exam_paper_subject"."pattern" IS '风格 （1=普通 2=列表）';
COMMENT ON COLUMN "public"."tc_exam_paper_subject"."xml_config" IS '其他设置, 以xml方式存储扩展, 值域, 约束(如外部引用, 范围, 唯一性, 等), 和其他题目的关联性, 互斥性';
COMMENT ON COLUMN "public"."tc_exam_paper_subject"."column_count" IS '排列栏数';
COMMENT ON COLUMN "public"."tc_exam_paper_subject"."score" IS '分值，权重';
COMMENT ON COLUMN "public"."tc_exam_paper_subject"."org_id" IS '医院Id';
COMMENT ON COLUMN "public"."tc_exam_paper_subject"."version_no" IS '版本号';
COMMENT ON COLUMN "public"."tc_exam_paper_subject"."created_by" IS '创建人';
COMMENT ON COLUMN "public"."tc_exam_paper_subject"."updated_by" IS '修改人';
COMMENT ON COLUMN "public"."tc_exam_paper_subject"."created_date" IS '创建时间';
COMMENT ON COLUMN "public"."tc_exam_paper_subject"."updated_date" IS '修改时间';
COMMENT ON COLUMN "public"."tc_exam_paper_subject"."class_type" IS '问卷类型( 1:微信 2:CRM 3:现场咨询)';
问卷答案表：
CREATE TABLE "public"."tc_exam_paper_result_detail" (
"detail_id" int4 DEFAULT nextval('tc_exam_paper_result_detail_detail_id_seq'::regclass) NOT NULL,
"paper_id" int4,
"subject_id" int4,
"subject_identifier" varchar(100) COLLATE "default",
"subject_style" int4,
"source" int4,
"answer_content" varchar(1000) COLLATE "default",
"score" numeric,
"remark" varchar(200) COLLATE "default",
"org_id" int4,
"created_user" varchar(50) COLLATE "default",
"created_by" int4,
"updated_by" int4,
"created_date" timestamp(6),
"updated_date" timestamp(6),
"answer_name" varchar(50) COLLATE "default",
"answer_phone" varchar(50) COLLATE "default",
CONSTRAINT "tc_exam_paper_result_detail_pkey" PRIMARY KEY ("detail_id")
)
WITH (OIDS=FALSE);
ALTER TABLE "public"."tc_exam_paper_result_detail" OWNER TO "postgres";
COMMENT ON TABLE "public"."tc_exam_paper_result_detail" IS '调查问卷的反馈结果详细';
COMMENT ON COLUMN "public"."tc_exam_paper_result_detail"."detail_id" IS '问卷结果详细id';
COMMENT ON COLUMN "public"."tc_exam_paper_result_detail"."paper_id" IS '问卷结果Id';
COMMENT ON COLUMN "public"."tc_exam_paper_result_detail"."subject_id" IS '问卷题目ID';
COMMENT ON COLUMN "public"."tc_exam_paper_result_detail"."subject_identifier" IS '问卷题目全局唯一标识符';
COMMENT ON COLUMN "public"."tc_exam_paper_result_detail"."subject_style" IS '问卷题目样式 （0=分组, 1=题目,2=答案，3=题目与答案）';
COMMENT ON COLUMN "public"."tc_exam_paper_result_detail"."source" IS '结果来源 1=门诊, 2=住院, 4=体检, 8=CRM';
COMMENT ON COLUMN "public"."tc_exam_paper_result_detail"."answer_content" IS '答题内容[文本输入时才有]';
COMMENT ON COLUMN "public"."tc_exam_paper_result_detail"."score" IS '得分';
COMMENT ON COLUMN "public"."tc_exam_paper_result_detail"."remark" IS '备注';
COMMENT ON COLUMN "public"."tc_exam_paper_result_detail"."org_id" IS '医院Id';
COMMENT ON COLUMN "public"."tc_exam_paper_result_detail"."created_user" IS '创建人名字';
COMMENT ON COLUMN "public"."tc_exam_paper_result_detail"."created_by" IS '创建人';
COMMENT ON COLUMN "public"."tc_exam_paper_result_detail"."updated_by" IS '更新人';
COMMENT ON COLUMN "public"."tc_exam_paper_result_detail"."created_date" IS '创建日期';
COMMENT ON COLUMN "public"."tc_exam_paper_result_detail"."updated_date" IS '修改时间';
1、2、3、4、5 是分数
下面是我写的SQL 写了截取 和 分数。 可以加Q交流：2353475000
select a.sort_no 题号,a.name 问卷类型, split_part(a.note,'#%#',3) 问卷题目,b.answer_content 问卷答案
,c.username 姓名
,c.phone 电话号码
,b.created_by 创建人ID
,CASE b.answer_content WHEN '非常满意' THEN 5
WHEN '满意' THEN 4
WHEN '一般' THEN 3
WHEN '不满意' THEN 2
WHEN '非常不满意' THEN 1
END 分数
from tc_exam_paper_subject a
JOIN tc_exam_paper_result_detail b on a.org_id='5093'and a.subject_id=b.subject_id
LEFT OUTER JOIN tc_user c on b.created_by=c.user_id
where c.username like '%{username}%'
and c.phone like '%{phone}%'
and a.name like '%{name}%'
ORDER by a.paper_id
数据库数据
