select judge_code,
JUDGE_TEXT,
sum(man) as ManSum,
sum(woman) as WomanSum,
count(JUDGE_TEXT) as CountSum
from (select t2.param_name,
t2.JUDGE_TEXT,
t2.judge_code,
t1.id,
case t1.gender when '1' then 1 else 0 end man,
case t1.gender when '2' then 1 else 0 end woman
from (select t1.id,
t2.gender,
t2.organ_id from (select ROW_NUMBER() OVER(PARTITION BY client_id ORDER BY report_date desc, id desc) as Row1,
id,
client_id,
organ_id
from report_info
where 1 = 1
/*and organ_id in ('2')
and report_date >= to_date('2002-08-10','yyyy-MM-dd')
and report_date < to_date('2015-08-10','yyyy-MM-dd')
and client_id in (select id from client_info where company in ('TTM','180医院AK2'))*/
) t1 inner join (select id,
gender,
organ_id
from client_info) t2 on t1.client_id = t2.id and t1.organ_id = t2.organ_id) t1
inner join (select trp.param_name,
trpv.report_id,
trpv.PARAM_ID,
trpv.JUDGE_TEXT,
trpv.judge_code
from report_param trp, report_param_value trpv
where trp.id = trpv.param_id
and trpv.item_value != 0
and trp.id = 'RP003'
and judge_code != 0) t2
on t1.id = t2.report_id)
group by judge_code, JUDGE_TEXT;
