- create table ACMS_UNITY_SCORE
- (
- unity_id VARCHAR2(40) not null,
- product_id VARCHAR2(40),
- exam_type VARCHAR2(40),
- public_date DATE,
- validate_date DATE,
- score NUMBER(10,2),
- course_id VARCHAR2(40),
- user_id VARCHAR2(40) not null,
- department_id VARCHAR2(40),
- exam_date DATE
- )
- //------------------------------------
- select count(*)
- from (select *
- from (select au.user_id,
- au.user_name,
- aus.department_id,
- max(case aus.course_id
- when '1' then
- aus.score
- end) as fg,
- max(case aus.course_id
- when '2' then
- aus.score
- end) as jc,
- max(case aus.course_id
- when '3' then
- aus.score
- end) as ds,
- max(case aus.course_id
- when '4' then
- aus.score
- end) as zs,
- au.mobile
- from acms_unity_score aus, acms_user au
- where to_char(aus.public_date, 'yyyy-MM-dd') between
- '2011-02-01' and '2011-12-12'
- and (aus.exam_type = 'exam.type.01' or
- aus.exam_type = 'exam.type.02')
- and aus.user_id = au.user_id
- and aus.department_id in
- (select cd.department_id
- from core_department cd
- where cd.department_code like '002%')
- group by au.user_id,
- aus.department_id,
- au.user_name,
- au.mobile)
- where fg >= 60
- and jc >= 60
- and (ds * 0.7 + zs * 0.3) >= 60)
- select *
- from (select au.user_id,
- max(case ar.course_id
- when '1' then
- ar.result
- end) as fg,
- max(case ar.course_id
- when '2' then
- ar.result
- end) as jc,
- max(case ar.course_id
- when '3' then
- ar.result
- end) as ds,
- max(case ar.course_id
- when '4' then
- ar.result
- end) as zs
- from acms_result ar, acms_user au
- where ar.user_id = au.user_id
- and ar.batch_id = 'ff80808133343d3d013334b96de72bdc'
- group by au.user_id)
- where fg >= 60
- and jc >= 60
- and (ds * 0.7 + zs * 0.3) >= 60
- -------------------------------------------------------------------------------------
- select user_name,
- card_id,
- mobile,
- fg,
- jc,
- round(ds * 0.7 + zs * 0.3)
- from (select au.user_id,
- au.user_name,
- au.card_id,
- aus.department_id,
- au.mobile,
- max(case aus.course_id
- when '1' then
- aus.score
- end) as fg,
- max(case aus.course_id
- when '2' then
- aus.score
- end) as jc,
- max(case aus.course_id
- when '3' then
- aus.score
- end) as ds,
- max(case aus.course_id
- when '4' then
- aus.score
- end) as zs
- from acms_unity_score aus, acms_user au
- where to_char(aus.exam_date, 'yyyy-MM-dd') between
- '2011-12-01' and '2011-12-22'
- and (aus.exam_type = 'exam.type.01' or
- aus.exam_type = 'exam.type.02')
- and aus.user_id = au.user_id
- and aus.department_id in
- (select cd.department_id
- from core_department cd
- where cd.department_code like '%002005034%')
- group by au.user_id,
- aus.department_id,
- au.user_name,
- au.card_id,
- au.mobile)
- where fg >= 60
- and jc >= 60
- and (ds * 0.7 + zs * 0.3) >= 60
转载于:https://blog.51cto.com/lya041/743855