有四张表,结果表(result),对象表(object),试题表(question),选项表(option)
结果表中有字段,对象ID(objectID),试题ID(questionID),选项ID(optionID),就是说某某对象,参与了某道试题,他的选择是(A,B,C,D,E),得分是score选项表通过questionID与试题表关联要求通过一条语句检索出,参加每一道“试题”的“对象”对象有多少人,分别选择(A,B,C,D,E)有多少人,如下:试题 内容 平均得分 最高分 A B C
a aaa 3.5 4.1 20.0% 40.0% 40.0%
b bbb 4.2 4.3 10.0% 30.0% 60.0%
结果表中有字段,对象ID(objectID),试题ID(questionID),选项ID(optionID),就是说某某对象,参与了某道试题,他的选择是(A,B,C,D,E),得分是score选项表通过questionID与试题表关联要求通过一条语句检索出,参加每一道“试题”的“对象”对象有多少人,分别选择(A,B,C,D,E)有多少人,如下:试题 内容 平均得分 最高分 A B C
a aaa 3.5 4.1 20.0% 40.0% 40.0%
b bbb 4.2 4.3 10.0% 30.0% 60.0%
round(sum(decode(a.optionid,'A',1,0))/count(*)*100,1)||'%' choose_A,
round(sum(decode(a.optionid,'B',1,0))/count(*)*100,1)||'%' choose_B,
round(sum(decode(a.optionid,'C',1,0))/count(*)*100,1)||'%' choose_C,
round(sum(decode(a.optionid,'D',1,0))/count(*)*100,1)||'%' choose_D,
round(sum(decode(a.optionid,'E',1,0))/count(*)*100,1)||'%' choose_E
from
result a,question b
where a.questionid=b.id
select
questionID
,avg(score) as aver_scope
,max(score) as max_scope
,sum(case optionID when 'A' then 1 else 0 end)/count(*)*100 sum_a
,sum(case optionID when 'B' then 1 else 0 end)/count(*)*100 sum_b
,sum(case optionID when 'C' then 1 else 0 end)/count(*)*100 sum_c
,sum(case optionID when 'D' then 1 else 0 end)/count(*)*100 sum_d
,sum(case optionID when 'E' then 1 else 0 end)/count(*)*100 sum_e
from result
group by questionID
order by questionID
结果表:
CREATE TABLE RESULT
(
C_OID_RESULT bigint NOT NULL,
C_OBJECTID bigint NOT NULL,
C_QUESTIONID bigint NOT NULL,
C_SCORE decimal(18,6) NULL,
C_OPTION bigint NULL
)
对象表:
CREATE TABLE OBJECT
(
C_OID_OBJECT bigint NOT NULL,
C_NAME varchar(256) NOT NULL,
)
试题表:
CREATE TABLE QUESTION
(
C_OID_QUESTION bigint NOT NULL,
C_TITLE varchar(512) NOT NULL,
C_TYPE varchar(16) NOT NULL,
C_DESC varchar(1024) NULL,
)
选项表:
CREATE TABLE OPTION
(
C_OID_OPTION bigint NOT NULL,
C_OPTIONNAME varchar(256) NOT NULL,
C_DESCRIPTION varchar(1024) NULL,
C_QUESTIONOID bigint NOT NULL
)
结果表数据
INSERT INTO RESULT ( C_OID_RESULT, C_OBJECTID, C_QUESTIONID, C_SCORE, C_OPTION )
VALUES ( 342753562, 342753497, 342753515, 3.000000, 342753549 )
go
INSERT INTO RESULT ( C_OID_RESULT, C_OBJECTID, C_QUESTIONID, C_SCORE, C_OPTION )
VALUES ( 342753563, 342753497, 342753511, 2.000000, 342753543 )
go
INSERT INTO RESULT ( C_OID_RESULT, C_OBJECTID, C_QUESTIONID, C_SCORE, C_OPTION )
VALUES ( 342753564, 342753497, 342753514, 2.000000, 342753533 )
go
INSERT INTO RESULT ( C_OID_RESULT, C_OBJECTID, C_QUESTIONID, C_SCORE, C_OPTION )
VALUES ( 342753565, 342753497, 342753516, 2.000000, 342753553 )
go
试题表:
INSERT INTO QUESTION ( C_OID_QUESTION, C_TITLE, C_TYPE, C_DESC )
VALUES ( 342753515, '1、请综合考虑您对目前工作的满意程度?', '1', '123' )
go
INSERT INTO QUESTION ( C_OID_QUESTION, C_TITLE, C_TYPE, C_DESC )
VALUES ( 342753511, '2、您和同事之间的关系融洽程度如何?', '1', '您和同事之间的关系融洽程度如何?' )
go
INSERT INTO QUESTION ( C_OID_QUESTION, C_TITLE, C_TYPE, C_DESC )
VALUES ( 342753514, '3、公司或部门对您提出的合理化建议的处理和反馈情况,您满意吗?', '1', '公司或部门对您提出的合理化建议的处理和反馈情况,您满意吗?' )
go
INSERT INTO QUESTION ( C_OID_QUESTION, C_TITLE, C_TYPE, C_DESC )
VALUES ( 342753516, '4、您认为您的收入和工作表现及业绩的挂钩情况', '1', '' )
go
选项表:针对试题'1、请综合考虑您对目前工作的满意程度?'
INSERT INTO OPTION ( C_OID_OPTION, C_OPTIONNAME, C_DESCRIPTION, C_QUESTIONOID )
VALUES ( 341999625, 'A.非常不满意', NULL, 342753515)
go
INSERT INTO OPTION ( C_OID_OPTION, C_OPTIONNAME, C_DESCRIPTION, C_QUESTIONOID )
VALUES ( 341999626, 'B.不满意', NULL, 342753515)
go
INSERT INTO OPTION ( C_OID_OPTION, C_OPTIONNAME, C_DESCRIPTION, C_QUESTIONOID )
VALUES ( 341999627, 'C.一般', NULL, 342753515)
go
INSERT INTO OPTION ( C_OID_OPTION, C_OPTIONNAME, C_DESCRIPTION, C_QUESTIONOID )
VALUES ( 341999628, 'D.满意', NULL, 342753515)
go
INSERT INTO OPTION ( C_OID_OPTION, C_OPTIONNAME, C_DESCRIPTION, C_QUESTIONOID )
VALUES ( 341999629, 'E.很满意', NULL, 342753515)
go
对象表:
INSERT INTO OBJECT ( C_OID_OBJECT, C_NAME )
VALUES ( 342753497, '王爱群' )
go兄弟们如果嫌数据少,可以再自行插入一些,谢谢了试题 内容 平均得分 最高分 A B C
a aaa 3.5 4.1 20.0% 40.0% 40.0%
b bbb 4.2 4.3 10.0% 30.0% 60.0% 就是说不但能统计出参与每道试题(result 表中的score)的平均分,最高分,还能统计出相应选择各个选项的对象人数所占比例
from QUESTION t1,"OPTION" t2,(
select avg(C_SCORE) SCORE,MAX(C_SCORE) MSCORE,max(C_OPTION) C_OPTION,
sum(decode(substr(C_OPTION,1,1),'A',1,0)) A,
sum(decode(substr(C_OPTION,1,1),'B',1,0)) B,
sum(decode(substr(C_OPTION,1,1),'C',1,0)) C
from RESULT,"OPTION" where C_OPTION=C_OID_OPTION group by C_QUESTIONID
) t3
where T1.C_OID_QUESTION=t2.C_QUESTIONOID and T1.C_OID_QUESTION=t3.C_OPTION;
select C_OID_QUESTION,C_TITLE,SCORE,MSCORE,A,B,C
from QUESTION t1,"OPTION" t2,(
select avg(C_SCORE) SCORE,MAX(C_SCORE) MSCORE,max(C_OPTION) C_OPTION,
sum(decode(substr(C_OPTION,1,1),'A',1,0))/count(1) A,
sum(decode(substr(C_OPTION,1,1),'B',1,0))/count(1) B,
sum(decode(substr(C_OPTION,1,1),'C',1,0))/count(1) C
from RESULT,"OPTION" where C_OPTION=C_OID_OPTION group by C_QUESTIONID
) t3
where T1.C_OID_QUESTION=t2.C_QUESTIONOID and T1.C_OID_QUESTION=t3.C_OPTION;
sum( case when o.c_optionname like '%A%' then r.c_score else 0 end ) as A,
sum( case when o.c_optionname like '%B%' then r.c_score else 0 end ) as B,
sum( case when o.c_optionname like '%C%' then r.c_score else 0 end ) as C,
sum( case when o.c_optionname like '%D%' then r.c_score else 0 end ) as D,
sum( case when o.c_optionname like '%E%' then r.c_score else 0 end ) as E,
avg(r.c_score),max(r.c_score),
count(case when o.c_optionname like '%A%' then 1 else null end)/count(*),
count(case when o.c_optionname like '%B%' then 1 else null end)/count(*),
count(case when o.c_optionname like '%C%' then 1 else null end)/count(*),
count(case when o.c_optionname like '%D%' then 1 else null end)/count(*),
count(case when o.c_optionname like '%E%' then 1 else null end)/count(*),
sum(r.c_score)
from QUESTION q,"OPTION" o,RESULT r
where q.C_OID_QUESTION = o.C_QUESTIONOID
and o.C_QUESTIONOID=r.C_QUESTIONID
group by q.C_OID_QUESTION, q.C_TITLE