query นี้ใช้ได้เปล่าครับ
count >>>pdx,dx0,dx1,dx2,dx3,dx4,dx5 แล้วมา union กัน ทั้ง opd ipd เก็บลง temptable
แล้ว group by และ sum ออกมา 10 อันดับโรค
set @dt1 := '2010-09-01';
set @dt2 := '2010-09-30';
select tt.icd,i.name,sum(tt.cc) as ss
from
(select v.pdx as icd,count(v.hn) as cc
from vn_stat v
where vstdate between @dt1 and @dt2
and v.pdx <> ''
group by v.pdx
union
select v.dx0 as icd,count(v.hn) as cc
from vn_stat v
where vstdate between @dt1 and @dt2
and v.dx0 <> ''
group by v.dx0
union
select v.dx1 as icd,count(v.hn) as cc
from vn_stat v
where vstdate between @dt1 and @dt2
and v.dx1 <> ''
group by v.dx1
union
select v.dx2 as icd,count(v.hn) as cc
from vn_stat v
where vstdate between @dt1 and @dt2
and v.dx2 <> ''
group by v.dx2
union
select v.dx3 as icd,count(v.hn) as cc
from vn_stat v
where vstdate between @dt1 and @dt2
and v.dx3 <> ''
group by v.dx3
union
select v.dx4 as icd,count(v.hn) as cc
from vn_stat v
where vstdate between @dt1 and @dt2
and v.dx4 <> ''
group by v.dx4
union
select v.dx5 as icd,count(v.hn) as cc
from vn_stat v
where vstdate between @dt1 and @dt2
and v.dx5 <> ''
group by v.dx5
union
select a.pdx as icd,count(a.hn) as cc
from an_stat a
where dchdate between @dt1 and @dt2
and a.pdx <> ''
group by a.pdx
union
select a.dx0 as icd,count(a.hn) as cc
from an_stat a
where dchdate between @dt1 and @dt2
and a.dx0 <> ''
group by a.dx0
union
select a.dx1 as icd,count(a.hn) as cc
from an_stat a
where dchdate between @dt1 and @dt2
and a.dx1 <> ''
group by a.dx1
union
select a.dx2 as icd,count(a.hn) as cc
from an_stat a
where dchdate between @dt1 and @dt2
and a.dx2 <> ''
group by a.dx2
union
select a.dx3 as icd,count(a.hn) as cc
from an_stat a
where dchdate between @dt1 and @dt2
and a.dx3 <> ''
group by a.dx3
union
select a.dx4 as icd,count(a.hn) as cc
from an_stat a
where dchdate between @dt1 and @dt2
and a.dx4 <> ''
group by a.dx4
union
select a.dx5 as icd,count(a.hn) as cc
from an_stat a
where dchdate between @dt1 and @dt2
and a.dx5 <> ''
group by a.dx5
) as tt
left join icd101 i on i.code = tt.icd
group by tt.icd
order by sum(tt.cc) desc
limit 10
หัวข้อ: หา 10 อันดับโรคโดย รวม opd ipd เข้าด้วยกันทำไงครับ (อ่าน 10401 ครั้ง)