หลังจากไปนั่งคิดมาครึ่งวัน ได้ออกมาแบบนี้ รบกวนเซียนทั้งหลายดูให้หน่อยค่ะ ว่าผิดตรงไหนอ่ะ
set @d1 = '2012-01-01';
set @d2 = '2012-01-31';
select *
from
(select v.hn,concat(p.pname,p.fname,space(3),p.lname) as ptname,p.clinic,sc.bps,sc.bpd
,
(
select sc.bps
from opdscreen sc1
where sc1.vstdate between DATE_ADD(@d1, INTERVAL -1 MONTH) and DATE_ADD(@d2, INTERVAL -1 MONTH)
and sc1.hn = v.hn
) as bps1
,
(
select sc.bps
from opdscreen sc2
where sc2.vstdate between DATE_ADD(@d1, INTERVAL -2 MONTH) and DATE_ADD(@d2, INTERVAL -2 MONTH)
and sc2.hn = v.hn
) as bps2
,
(
select sc.bpd
from opdscreen sc3
where sc3.vstdate between DATE_ADD(@d1, INTERVAL -1 MONTH) and DATE_ADD(@d2, INTERVAL -1 MONTH)
and sc3.hn = v.hn
) as bpd1
,
(
select sc.bpd
from opdscreen sc4
where sc4.vstdate between DATE_ADD(@d1, INTERVAL -2 MONTH) and DATE_ADD(@d2, INTERVAL -2 MONTH)
and sc4.hn = v.hn
) as bpd2
from vn_stat v
left join opdscreen sc on sc.vn = v.vn
left join patient p on p.hn = v.hn
where v.vstdate between @d1 and @d2
and p.clinic like '%àºÒËÇÒ¹%'
and sc.bps is not null
and sc.bps >= '130' or sc.bpd >= '80'
and v.vstdate between @d1 and @d2
group by v.hn
order by v.aid desc ,v.vstdate,v.hn
) as ttemp
where bps >= '130' and bps1 >= '130' and bps2 >= '130'