แสดงกระทู้
1
แจ้งปัญหา / ขอความช่วยเหลือ / ขอความช่วยเหลือดูโค๊ด SQL
« เมื่อ: มิถุนายน 21, 2012, 12:01:58 PM »
ขอความช่วยเหลือจากเทพและอาจารย์ที่เก่งๆๆเรื่องโค๊ดน่ะค่ะ
คือ อยากได้ ข้อมูล DM ที่มีการตรวจ LAb แต่พอใช้โค๊ดดึงข้อมูลแล้ว
ข้อมูลมาหมดเลย คนคนนึงมาซ้ำเป็น 10
*อยากให้อาจารย์เทพๆๆช่วยดู หน่อยค่ะ อยากให้แสดงข้อมูลผู้ที่มาตรวจ Lab ล่าสุด และข้อมูล ไม่ซ้ำกันนน่ะค่ะ*
*ขอบคุณน่ะค่ะ*
--------------------------------------------------------------------------------------------------------------
select h.hn
, concat(pt.pname,pt.fname,' ',pt.lname) as ptname
, v.age_y as age_y
,pt.informaddr as address ,v.aid,v.pdx as PrincipleDiagnosis,v.dx0 as Cocombidity,v.dx1 as Complication,v.vstdate ,concat(os.bps," / ",os.bpd) as bps ,
os.bmi,os.fbs
,(
select concat(l1.lab_order_result," ",case when l1.lab_order_result = "-" then "" else i1.lab_items_unit end) as result
from lab_order l1 left outer join lab_items i1 on i1.lab_items_code = l1.lab_items_code where l1.lab_order_number = h.lab_order_number and l1.lab_items_code = "77") as BUN
,(
select concat(l3.lab_order_result," ",case when l3.lab_order_result = "-" then "" else i3.lab_items_unit end) as result
from lab_order l3 left outer join lab_items i3 on i3.lab_items_code = l3.lab_items_code where l3.lab_order_number = h.lab_order_number and l3.lab_items_code = "78") as Cr
,(
select concat(l5.lab_order_result," ",case when l5.lab_order_result = "-" then "" else i5.lab_items_unit end) as result
from lab_order l5 left outer join lab_items i5 on i5.lab_items_code = l5.lab_items_code where l5.lab_order_number = h.lab_order_number and l5.lab_items_code = "103") as TG
,(
select concat(l4.lab_order_result," ",case when l4.lab_order_result = "-" then "" else i4.lab_items_unit end) as result
from lab_order l4 left outer join lab_items i4 on i4.lab_items_code = l4.lab_items_code where l4.lab_order_number = h.lab_order_number and l4.lab_items_code = "102") as Chol
,(
select concat(l6.lab_order_result," ",case when l6.lab_order_result = "-" then "" else i6.lab_items_unit end) as result
from lab_order l6 left outer join lab_items i6 on i6.lab_items_code = l6.lab_items_code where l6.lab_order_number = h.lab_order_number and l6.lab_items_code = "91") as HDL
,(
select concat(l7.lab_order_result," ",case when l7.lab_order_result = "-" then "" else i7.lab_items_unit end) as result
from lab_order l7 left outer join lab_items i7 on i7.lab_items_code = l7.lab_items_code where l7.lab_order_number = h.lab_order_number and l7.lab_items_code = "92") as LDL
,(
select concat(l8.lab_order_result," ") as result
from lab_order l8 left outer join lab_items i8 on i8.lab_items_code = l8.lab_items_code where l8.lab_order_number = h.lab_order_number
and l8.lab_items_code = "226") as AlbuminUrine
from lab_head h
left outer join patient pt on pt.hn=h.hn
left outer join vn_stat v on v.vn=h.vn
left outer join opdscreen os on os.vn=h.vn
left outer join lab_perform_status lp on lp.lab_perform_status_id = h.lab_perform_status_id
where v.vstdate between "2011-10-01" and "2012-09-30" and
( v.pdx>= "I10" and v.pdx<= "I15")
คือ อยากได้ ข้อมูล DM ที่มีการตรวจ LAb แต่พอใช้โค๊ดดึงข้อมูลแล้ว
ข้อมูลมาหมดเลย คนคนนึงมาซ้ำเป็น 10
*อยากให้อาจารย์เทพๆๆช่วยดู หน่อยค่ะ อยากให้แสดงข้อมูลผู้ที่มาตรวจ Lab ล่าสุด และข้อมูล ไม่ซ้ำกันนน่ะค่ะ*
*ขอบคุณน่ะค่ะ*
--------------------------------------------------------------------------------------------------------------
select h.hn
, concat(pt.pname,pt.fname,' ',pt.lname) as ptname
, v.age_y as age_y
,pt.informaddr as address ,v.aid,v.pdx as PrincipleDiagnosis,v.dx0 as Cocombidity,v.dx1 as Complication,v.vstdate ,concat(os.bps," / ",os.bpd) as bps ,
os.bmi,os.fbs
,(
select concat(l1.lab_order_result," ",case when l1.lab_order_result = "-" then "" else i1.lab_items_unit end) as result
from lab_order l1 left outer join lab_items i1 on i1.lab_items_code = l1.lab_items_code where l1.lab_order_number = h.lab_order_number and l1.lab_items_code = "77") as BUN
,(
select concat(l3.lab_order_result," ",case when l3.lab_order_result = "-" then "" else i3.lab_items_unit end) as result
from lab_order l3 left outer join lab_items i3 on i3.lab_items_code = l3.lab_items_code where l3.lab_order_number = h.lab_order_number and l3.lab_items_code = "78") as Cr
,(
select concat(l5.lab_order_result," ",case when l5.lab_order_result = "-" then "" else i5.lab_items_unit end) as result
from lab_order l5 left outer join lab_items i5 on i5.lab_items_code = l5.lab_items_code where l5.lab_order_number = h.lab_order_number and l5.lab_items_code = "103") as TG
,(
select concat(l4.lab_order_result," ",case when l4.lab_order_result = "-" then "" else i4.lab_items_unit end) as result
from lab_order l4 left outer join lab_items i4 on i4.lab_items_code = l4.lab_items_code where l4.lab_order_number = h.lab_order_number and l4.lab_items_code = "102") as Chol
,(
select concat(l6.lab_order_result," ",case when l6.lab_order_result = "-" then "" else i6.lab_items_unit end) as result
from lab_order l6 left outer join lab_items i6 on i6.lab_items_code = l6.lab_items_code where l6.lab_order_number = h.lab_order_number and l6.lab_items_code = "91") as HDL
,(
select concat(l7.lab_order_result," ",case when l7.lab_order_result = "-" then "" else i7.lab_items_unit end) as result
from lab_order l7 left outer join lab_items i7 on i7.lab_items_code = l7.lab_items_code where l7.lab_order_number = h.lab_order_number and l7.lab_items_code = "92") as LDL
,(
select concat(l8.lab_order_result," ") as result
from lab_order l8 left outer join lab_items i8 on i8.lab_items_code = l8.lab_items_code where l8.lab_order_number = h.lab_order_number
and l8.lab_items_code = "226") as AlbuminUrine
from lab_head h
left outer join patient pt on pt.hn=h.hn
left outer join vn_stat v on v.vn=h.vn
left outer join opdscreen os on os.vn=h.vn
left outer join lab_perform_status lp on lp.lab_perform_status_id = h.lab_perform_status_id
where v.vstdate between "2011-10-01" and "2012-09-30" and
( v.pdx>= "I10" and v.pdx<= "I15")
