สอบถามคำสัง SQL ครับ

[panus_t]

สอบถามคำสัง SQL ครับ
จากตัวรายงาน ต้องการ ตัดช่อง Lab และ ยา ออก แต่ให้นำคำสั่งดึง Lab และ ยา ไปรวมกับ SQL หลักได้อย่างไรครับ
ความต้องการคือ
1.ช่อง Lab และ ยา ไม่ต้องแสดงใน paper และ แต่จะให้แสดง ตอนส่งรายงานออกเป็น Excel แทน
2.ในไฟล์ Excel แสดงรายชื่อไม่ซ้ำกันด้วยครับ

[panus_t]

select ov.vn,ov.vstdate,pt.hn,concat (pt.pname,' ',pt.fname,'  ',pt.lname) as ptname,ov.age_y,os.bw,os.height,os.bmi,
os.fbs,os.bps,os.bpd,pt.addrpart,pt.moopart,ta.full_name,concat (ov.pdx) as pdx,concat (ov.dx0) as dx0,
concat (ov.dx1) as dx1,concat (ov.dx2) as dx2,concat (ov.dx3) as dx3,concat (ov.dx4) as dx4,concat (ov.dx5) as dx5 ,
lh.lab_order_number
from vn_stat ov
left outer join patient pt on pt.hn=ov.hn
left outer join ovst ovst on ov.vn=ovst.vn
left outer join thaiaddress ta on ov.aid=ta.addressid
left outer join icd101 icd on icd.code=ov.pdx
left outer join opdscreen os on os.vn=ov.vn
left outer join lab_head lh on lh.vn=ov.vn
where ov.vstdate between "2011-05-01" and  "2011-05-31"
and ov.aid<>"300301"
and ( (ov.pdx >= "E100" and ov.pdx <= "E119")
or (ov.dx0 >= "E100" and ov.dx0 <= "E119")
or (ov.dx1 >= "E100" and ov.dx1 <= "E119")
or (ov.dx2 >= "E100" and ov.dx2 <= "E119")
or (ov.dx3 >= "E100" and ov.dx3 <= "E119")
or (ov.dx4 >= "E100" and ov.dx4 <= "E119")
or (ov.dx5 >= "E100" and ov.dx5 <= "E119") )
order by pt.tmbpart , pt.moopart , pt.addrpart

จากโค้ดด้านบน ผมต้องการดึง Lab มาต่อท้ายโดยมีโค้ดังนี้

select concat(li.lab_items_name," = ",lo.lab_order_result) as
lab_result  from lab_head lh
left outer join lab_order lo on lo.lab_order_number=lh.lab_order_number
left outer join lab_items li on li.lab_items_code=lo.lab_items_code
where lh.vn="'+dbpipeline['vn']+'"  and lo.lab_items_code in ("78","92","193","400")


ต้องทำไปแทรกโค้ดอันแรกได้หรือเปล่าครับ

[lo_singburihosp]

select ov.vn,ov.vstdate,pt.hn,concat (pt.pname,' ',pt.fname,'  ',pt.lname) as ptname,ov.age_y,os.bw,os.height,os.bmi,
os.fbs,os.bps,os.bpd,pt.addrpart,pt.moopart,ta.full_name,concat (ov.pdx) as pdx,concat (ov.dx0) as dx0,
concat (ov.dx1) as dx1,concat (ov.dx2) as dx2,concat (ov.dx3) as dx3,concat (ov.dx4) as dx4,concat (ov.dx5) as dx5 ,
lh.lab_order_number,
l1.lab_order_result,l2.lab_order_result,l3.lab_order_result,l4.lab_order_resultfrom vn_stat ov
left outer join patient pt on pt.hn=ov.hn
left outer join ovst ovst on ov.vn=ovst.vn
left outer join thaiaddress ta on ov.aid=ta.addressid
left outer join icd101 icd on icd.code=ov.pdx
left outer join opdscreen os on os.vn=ov.vn
left outer join lab_head lh on lh.vn=ov.vn
left outer join lab_order l1 on l1.lab_order_number = lh.lab_order_number and l1.lab_items_code = '78'
left outer join lab_order l2 on l2.lab_order_number = lh.lab_order_number and l1.lab_items_code = '92'
left outer join lab_order l3 on l3.lab_order_number = lh.lab_order_number and l1.lab_items_code = '193'
left outer join lab_order l4 on l4.lab_order_number = lh.lab_order_number and l1.lab_items_code = '400'where ov.vstdate between "2011-05-01" and  "2011-05-31"
and ov.aid<>"300301"
and ( (ov.pdx >= "E100" and ov.pdx <= "E119")
or (ov.dx0 >= "E100" and ov.dx0 <= "E119")
or (ov.dx1 >= "E100" and ov.dx1 <= "E119")
or (ov.dx2 >= "E100" and ov.dx2 <= "E119")
or (ov.dx3 >= "E100" and ov.dx3 <= "E119")
or (ov.dx4 >= "E100" and ov.dx4 <= "E119")
or (ov.dx5 >= "E100" and ov.dx5 <= "E119") )
order by pt.tmbpart , pt.moopart , pt.addrpart


ลองดูนะครับ

[panus_t]

ขอบคุณครับ ... ผมต้องการดึงยาอีก แต่ทำไม่ได้ครับ จำนวนเรคคอร์ดมันเพิ่มขึ้น (ขื่อผู้ป่วยซ้ำ) เลยขอความช่วยเหลือดูโค้ดให้อีกทีครับ

select ov.vn,ov.vstdate,pt.hn,concat (pt.pname,' ',pt.fname,'  ',pt.lname) as ptname,ov.age_y,os.bw,os.height,os.bmi,
os.fbs,os.bps,os.bpd,pt.addrpart,pt.moopart,ta.full_name,concat (ov.pdx) as pdx,concat (ov.dx0) as dx0,
concat (ov.dx1) as dx1,concat (ov.dx2) as dx2,concat (ov.dx3) as dx3,concat (ov.dx4) as dx4,concat (ov.dx5) as dx5 ,
lh.lab_order_number,li.lab_items_name,lo.lab_order_result as Creatinine,lo92.lab_order_result as LDL,
lo193.lab_order_result as HbA1C,lo400.lab_order_result as UrineMicroalbumin,concat (di.name," - ",di.strength) as drug_namefrom vn_stat ov
left outer join patient pt on pt.hn=ov.hn
left outer join ovst ovst on ov.vn=ovst.vn
left outer join thaiaddress ta on ov.aid=ta.addressid
left outer join icd101 icd on icd.code=ov.pdx
left outer join opdscreen os on os.vn=ov.vn
left outer join lab_head lh on lh.vn=ov.vn
left outer join lab_order lo on lo.lab_order_number=lh.lab_order_number and lo.lab_items_code="78"
left outer join lab_order lo92 on lo92.lab_order_number=lh.lab_order_number and lo92.lab_items_code="92"
left outer join lab_order lo193 on lo193.lab_order_number=lh.lab_order_number and lo193.lab_items_code="193"
left outer join lab_order lo400 on lo400.lab_order_number=lh.lab_order_number and lo400.lab_items_code="400"
left outer join lab_items li on li.lab_items_code=lo.lab_items_code
left outer join opitemrece ot on ot.vn=ov.vn
left outer join drugitems di on di.icode=ot.icode and di.icode="1000039"
where ov.vstdate between "2011-05-01" and  "2011-05-31"
and ov.aid<>"300301"
and ( (ov.pdx >= "E100" and ov.pdx <= "E119")
or (ov.dx0 >= "E100" and ov.dx0 <= "E119")
or (ov.dx1 >= "E100" and ov.dx1 <= "E119")
or (ov.dx2 >= "E100" and ov.dx2 <= "E119")
or (ov.dx3 >= "E100" and ov.dx3 <= "E119")
or (ov.dx4 >= "E100" and ov.dx4 <= "E119")
or (ov.dx5 >= "E100" and ov.dx5 <= "E119") )
order by pt.tmbpart , pt.moopart , pt.addrpart

เพิ่มยาอีกหลายตัวครับ
icode
1000038
1000122
1500801
1530045
1000278
1460090