首页新闻找找看学习计划

求助大佬,这样的SQL怎么写,在线等!!!

0
悬赏园豆:100 [待解决问题]

实现效果

数据库表: 
问卷题目表
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

wangl200的主页 wangl200 | 初学一级 | 园豆:102
提问于:2019-06-28 16:21

图片403了

默卿 4个月前

@默卿: ok. 给你个地址 应该可以看到,谢谢
https://bbs.csdn.net/topics/392729334

wangl200 4个月前

@默卿: 图片问题已经修改好了,谢谢提醒

wangl200 4个月前
< >
分享
所有回答(2)
0

这个其实是行转列问题,有两种方法;

1.静态行转列

2.动态行转列(列个数不确定)

有很多类似这种列子,你可以参考一下:

https://blog.csdn.net/sinat_27406925/article/details/77507478

https://www.cnblogs.com/xiaoxi/p/7151433.html

梦里寻人 | 园豆:11375 (专家六级) | 2019-06-28 17:27
0
科技改变未来☆ | 园豆:219 (菜鸟二级) | 2019-07-03 15:07
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册