17,086
社区成员
发帖
与我相关
我的任务
分享
select zonecode as zonecode,
sum(t.counts) as counts,
sum(case
when t.flwcount = 1 then
1
else
0
end) as sumflw1, --完成一次w
sum(case
when t.flwcount = 2 then
1
else
0
end) as sumflw2, --完成两次w
sum(case
when t.flwcount = 2 then
1
else
0
end) as sumflw3, --完成三次w
sum(case
when t.flwcount = 2 then
1
else
0
end) as sumflw4, --完成四次w
sum(case
when t.cd4count = 1 then
1
else
0
end) as cd41, --完成一次a检测
sum(case
when t.cd4count = 2 then
1
else
0
end) as cd42, --完成两次a检测
sum(case
when t.virunlncount = 1 then
1
else
0
end) as viruln1, --完成一次c检测
sum(t.virunlnno) as noviruln ,--c被抑制
sum(t.initvirunlncount) as avirulnsum
from (select /*+ index(a PK_AIDSZH_SGRA_ADULT_INFO) */ a.card_id as card_id,
a.zonecode as zonecode,
sum(case
when a.DT_ANTIVIRUS <= to_date('2008-12-31', 'yyyy-mm-dd') and
a.tm_create <= to_date('2006-06-30', 'yyyy-mm-dd') then
1
else
0
end) as counts,
0 as flwcount, -- 当年完成w次数
sum(case
when a.cd4 is not null and
a.DT_ANTIVIRUS <= to_date('2008-12-31', 'yyyy-mm-dd') and
a.tm_create <= to_date('2006-06-30', 'yyyy-mm-dd') then
1
else
0
end) as cd4count, --当年完成a检测数
sum(case
when a.viruln is not null and
a.DT_ANTIVIRUS <= to_date('2008-12-31', 'yyyy-mm-dd') and
a.tm_create <= to_date('2006-06-30', 'yyyy-mm-dd') then
1
else
0
end) as virunlncount, --当年完成c次数
0 as virunlnno,
sum(case
when a.viruln is not null and
a.DT_ANTIVIRUS >= to_date('2009-01-01', 'yyyy-mm-dd') and
a.tm_create <= to_date('2006-06-30', 'yyyy-mm-dd') then
1
else
0
end) as initvirunlncount
from aidszh_sgra_adult_info a
where not exists (select ''
from aidszh_sgra_adult_flw m
where a.card_id = m.card_id)
and a.zonecode like '11%'
group by a.card_id, a.zonecode
union all
select /*+ index(b PK_AIDISZH_SGRA_CHILD) index(c IDX_SGRA_adult_FLW) index(d IDX_SGRA_NEW_adult_IDCARD) */ b.card_id,
b.zonecode as zonecode,
/* sum(case
when b.DT_ANTIVIRUS <= to_date('2008-12-31', 'yyyy-mm-dd') and
c.tm_create <= to_date('2006-06-30', 'yyyy-mm-dd') and
c.is_flw = '1' and c.clinic_treatment != '3' and ( d.end_cause <>'1' and d.end_cause <>'2' or d.end_cause is null ) then
1
else
0
end) as counts, --总人数,但是产生了笛卡尔积 */
count(distinct(b.card_id)) --总人数,但是缺少限制条件
sum(case
when c.card_id is not null and
b.DT_ANTIVIRUS <= to_date('2008-12-31', 'yyyy-mm-dd') and
c.tm_create <= to_date('2006-06-30', 'yyyy-mm-dd') and
c.is_flw = '1' and c.clinic_treatment != '3' and ( d.end_cause <>'1' and d.end_cause <>'2' or d.end_cause is null ) then
1
else
0
end) as flwcount, --当年完成w次数
sum(case
when c.cd4 is not null and
b.DT_ANTIVIRUS <= to_date('2008-12-31', 'yyyy-mm-dd') and
c.tm_create <= to_date('2006-06-30', 'yyyy-mm-dd') and
c.is_flw = '1' and c.clinic_treatment != '3' and ( d.end_cause <>'1' and d.end_cause <>'2' or d.end_cause is null ) then
1
else
0
end) as cd4count, --完成a检测数
sum(case
when c.viruln is not null and
b.DT_ANTIVIRUS <= to_date('2008-12-31', 'yyyy-mm-dd') and
c.tm_create <= to_date('2006-06-30', 'yyyy-mm-dd') and
c.is_flw = '1' and c.clinic_treatment != '3' and ( d.end_cause <>'1' and d.end_cause <>'2' or d.end_cause is null ) then
1
else
0
end) as virunlncount,
sum(case
when b.viruln is not null and b.viruln <> '0' and
c.tm_create <= to_date('2006-06-30', 'yyyy-mm-dd') and
b.DT_ANTIVIRUS > to_date('2009-01-01', 'yyyy-mm-dd') and
c.viruln = '0' and
(c.dt_flworinterp - b.DT_ANTIVIRUS >= 180) and
(c.dt_flworinterp - b.DT_ANTIVIRUS <= 360)
and
c.is_flw = '1' and c.clinic_treatment != '3' and ( d.end_cause <>'1' and d.end_cause <>'2' or d.end_cause is null ) then
1
else
0
end) as virunlnno, --c被抑制
sum(case
when b.viruln is not null and
b.DT_ANTIVIRUS >= to_date('2009-01-01', 'yyyy-mm-dd') and
c.tm_create <= to_date('2006-06-30', 'yyyy-mm-dd') and
c.is_flw = '1' and c.clinic_treatment != '3' and ( d.end_cause <>'1' and d.end_cause <>'2' or d.end_cause is null ) then
1
else
0
end) as initvirunlncount
from aidszh_sgra_adult_info b, aidszh_sgra_adult_flw c,aidszh_sgra_adult_newstatus d
where b.card_id = c.card_id and d.card_id=b.card_id
and b.zonecode like '11%'
group by b.card_id, b.zonecode) t
group by zonecode
sum(case
when b.DT_ANTIVIRUS <= to_date('2008-12-31', 'yyyy-mm-dd') and
c.tm_create <= to_date('2006-06-30', 'yyyy-mm-dd') and
c.is_flw = '1' and c.clinic_treatment != '3' and ( d.end_cause <>'1' and d.end_cause <>'2' or d.end_cause is null ) then
1
else
0
end) as counts
select zonecode as zonecode,
sum(t.count1)+sum(decode(t.counts,0,0,1)) as counts,
sum(case
when t.flwcount = 1 then
1
else
0
end) as sumflw1, --完成一次w
sum(case
when t.flwcount = 2 then
1
else
0
end) as sumflw2, --完成两次w
sum(case
when t.flwcount = 2 then
1
else
0
end) as sumflw3, --完成三次w
sum(case
when t.flwcount = 2 then
1
else
0
end) as sumflw4, --完成四次w
sum(case
when t.cd4count = 1 then
1
else
0
end) as cd41, --完成一次a检测
sum(case
when t.cd4count = 2 then
1
else
0
end) as cd42, --完成两次a检测
sum(case
when t.virunlncount = 1 then
1
else
0
end) as viruln1, --完成一次c检测
sum(t.virunlnno) as noviruln ,--c被抑制
sum(t.initvirunlncount) as avirulnsum
from (select /*+ index(a PK_AIDSZH_SGRA_ADULT_INFO) */ a.card_id as card_id,
a.zonecode as zonecode,
0 as counts,
sum(case
when a.DT_ANTIVIRUS <= to_date('2008-12-31', 'yyyy-mm-dd') and
a.tm_create <= to_date('2006-06-30', 'yyyy-mm-dd') then
1
else
0
end) as count1,
0 as flwcount, -- 当年完成w次数
sum(case
when a.cd4 is not null and
a.DT_ANTIVIRUS <= to_date('2008-12-31', 'yyyy-mm-dd') and
a.tm_create <= to_date('2006-06-30', 'yyyy-mm-dd') then
1
else
0
end) as cd4count, --当年完成a检测数
sum(case
when a.viruln is not null and
a.DT_ANTIVIRUS <= to_date('2008-12-31', 'yyyy-mm-dd') and
a.tm_create <= to_date('2006-06-30', 'yyyy-mm-dd') then
1
else
0
end) as virunlncount, --当年完成c次数
0 as virunlnno,
sum(case
when a.viruln is not null and
a.DT_ANTIVIRUS >= to_date('2009-01-01', 'yyyy-mm-dd') and
a.tm_create <= to_date('2006-06-30', 'yyyy-mm-dd') then
1
else
0
end) as initvirunlncount
from aidszh_sgra_adult_info a
where not exists (select ''
from aidszh_sgra_adult_flw m
where a.card_id = m.card_id)
and a.zonecode like '11%'
group by a.card_id, a.zonecode
union all
select /*+ index(b PK_AIDISZH_SGRA_CHILD) index(c IDX_SGRA_adult_FLW) index(d IDX_SGRA_NEW_adult_IDCARD) */ b.card_id,
b.zonecode as zonecode,
sum(case
when b.DT_ANTIVIRUS <= to_date('2008-12-31', 'yyyy-mm-dd') and
c.tm_create <= to_date('2006-06-30', 'yyyy-mm-dd') and
c.is_flw = '1' and c.clinic_treatment != '3' and ( d.end_cause <>'1' and d.end_cause <>'2' or d.end_cause is null ) then
1
else
0
end) as counts,
0 as count1,
sum(case
when c.card_id is not null and
b.DT_ANTIVIRUS <= to_date('2008-12-31', 'yyyy-mm-dd') and
c.tm_create <= to_date('2006-06-30', 'yyyy-mm-dd') and
c.is_flw = '1' and c.clinic_treatment != '3' and ( d.end_cause <>'1' and d.end_cause <>'2' or d.end_cause is null ) then
1
else
0
end) as flwcount, --当年完成w次数
sum(case
when c.cd4 is not null and
b.DT_ANTIVIRUS <= to_date('2008-12-31', 'yyyy-mm-dd') and
c.tm_create <= to_date('2006-06-30', 'yyyy-mm-dd') and
c.is_flw = '1' and c.clinic_treatment != '3' and ( d.end_cause <>'1' and d.end_cause <>'2' or d.end_cause is null ) then
1
else
0
end) as cd4count, --完成a检测数
sum(case
when c.viruln is not null and
b.DT_ANTIVIRUS <= to_date('2008-12-31', 'yyyy-mm-dd') and
c.tm_create <= to_date('2006-06-30', 'yyyy-mm-dd') and
c.is_flw = '1' and c.clinic_treatment != '3' and ( d.end_cause <>'1' and d.end_cause <>'2' or d.end_cause is null ) then
1
else
0
end) as virunlncount,
sum(case
when b.viruln is not null and b.viruln <> '0' and
c.tm_create <= to_date('2006-06-30', 'yyyy-mm-dd') and
b.DT_ANTIVIRUS > to_date('2009-01-01', 'yyyy-mm-dd') and
c.viruln = '0' and
(c.dt_flworinterp - b.DT_ANTIVIRUS >= 180) and
(c.dt_flworinterp - b.DT_ANTIVIRUS <= 360)
and
c.is_flw = '1' and c.clinic_treatment != '3' and ( d.end_cause <>'1' and d.end_cause <>'2' or d.end_cause is null ) then
1
else
0
end) as virunlnno, --c被抑制
sum(case
when b.viruln is not null and
b.DT_ANTIVIRUS >= to_date('2009-01-01', 'yyyy-mm-dd') and
c.tm_create <= to_date('2006-06-30', 'yyyy-mm-dd') and
c.is_flw = '1' and c.clinic_treatment != '3' and ( d.end_cause <>'1' and d.end_cause <>'2' or d.end_cause is null ) then
1
else
0
end) as initvirunlncount
from aidszh_sgra_adult_info b, aidszh_sgra_adult_flw c,aidszh_sgra_adult_newstatus d
where b.card_id = c.card_id and d.card_id=b.card_id
and b.zonecode like '11%'
group by b.card_id, b.zonecode) t
group by zonecode