การเขียนคำสั่งเพื่อดึงข้อมูล FBS รายบุคคล ในปีงบประมาณ

[udomchok]

คำถาม :
พอจะมีคำสั่งที่แสดงhnนี้แล้วให้ข้อมูลที่อยู่คนละvisitรวมเป็นแถวเดียว ได้มั้ยครับ
เอา visit ล่าสุดในเดือนครับ
(https://www.facebook.com/groups/hosxp/permalink/1013295938690078/)

[udomchok]

คำตอบ :
select hn, sum(Octo) as 'ต.ค.', sum(Nov) as 'พ.ย.', sum(Dece) as 'ธ.ค.', sum(Jan) as 'ม.ค.', sum(Feb) as 'ก.พ.', sum(Mar) as 'มี.ค.', sum(Apr) as 'เม.ย.', sum(May) as 'พ.ค.', sum(Jun) as 'มิ.ย.', sum(Jul) as 'ก.ค.', sum(Aug) as 'ส.ค.',sum(Sep) as 'ก.ย.' from (
select hn,
if(month(vstdate)=10,fbs,'') as Octo,
if(month(vstdate)=11,fbs,'') as Nov,
if(month(vstdate)=12,fbs,'') as Dece,
if(month(vstdate)=1,fbs,'') as Jan,
if(month(vstdate)=2,fbs,'') as Feb,
if(month(vstdate)=3,fbs,'') as Mar,
if(month(vstdate)=4,fbs,'') as Apr,
if(month(vstdate)=5,fbs,'') as May,
if(month(vstdate)=6,fbs,'') as Jun,
if(month(vstdate)=7,fbs,'') as Jul,
if(month(vstdate)=8,fbs,'') as Aug,
if(month(vstdate)=9,fbs,'') as Sep
from opdscreen
where vstdate between "2012-10-01" and "2013-09-30" and fbs > 0
group by hn, month(vstdate)
) as t
group by hn

[B.nattha]

เยี่ยมเลยครับอาจารย์ ขอบคุณครับ

[udomchok]

มาแนวเดียวกัน https://www.facebook.com/groups/hosxp/permalink/1014940185192320/

คำถาม :
ขอ query แบบนี้พอจะมีมั้ยครับ

[udomchok]

คำตอบ :
Step 1
==============================================
select lh.hn, lh.vn, if(lab_items_code=801,lo.lab_order_result,"") as FBS, if(lab_items_code=738,lo.lab_order_result,"") as CHO, if(lab_items_code=739,lo.lab_order_result,"") as TG, if(lab_items_code=740,lo.lab_order_result,"") as HDL, if(lab_items_code=868,lo.lab_order_result,"") as LDL
from lab_head lh
join lab_order lo on lo.lab_order_number=lh.lab_order_number
where lh.order_date between "2013-10-01" and "2013-10-31"
and lo.lab_items_code in (801,738,739,740,868)
order by vn

[udomchok]

คำตอบ (ต่อ) :
Step 2 จับมารวมกันซะ
========================================
select t.hn, t.vn, sum(t.FBS) as fbs, sum(t.CHO) as cho, sum(t.TG) as tg, sum(t.HDL) as hdl, sum(t.LDL) as ldl
from (
select lh.hn, lh.vn, if(lab_items_code=801,lo.lab_order_result,"") as FBS, if(lab_items_code=738,lo.lab_order_result,"") as CHO, if(lab_items_code=739,lo.lab_order_result,"") as TG, if(lab_items_code=740,lo.lab_order_result,"") as HDL, if(lab_items_code=868,lo.lab_order_result,"") as LDL
from lab_head lh
join lab_order lo on lo.lab_order_number=lh.lab_order_number
where lh.order_date between "2013-10-01" and "2013-10-31"
and lo.lab_items_code in (801,738,739,740,868)) as t
group by t.vn
order by t.vn

[udomchok]

ท่านเดิมถามผมต่อทิ้งท้ายไว้ในกระทู้แรก และกระทู้ที่ 2
คำถาม :
ถ้าข้อมูลเป็น string จะรวมยังไงครับ (https://www.facebook.com/groups/hosxp/permalink/1013295938690078/?comment_id=1014853581867647)

[udomchok]

จัดไป
คำตอบ :
Step 1 : ทดสอบทำแบบเดิม
===============================================
select lh.hn, lh.vn, if(lab_items_code=358,lo.lab_order_result,"") as HBsAb, if(lab_items_code=360,lo.lab_order_result,"") as HBsAg
from lab_head lh
join lab_order lo on lo.lab_order_number=lh.lab_order_number
where lh.order_date between "2013-10-01" and "2013-10-31"
and lo.lab_items_code in (358,360)
order by vn

[udomchok]

คำตอบ (ต่อ) :
Step 2 : ทดลองทำแบบเดิม (ได้ผลไม่ถูกตามที่ต้องการ)
==============================================
select t.hn, t.vn, sum(HBsAb) as HBs_Ab, sum(HBsAg) as HBs_Ag
from (
select lh.hn, lh.vn, if(lab_items_code=358,lo.lab_order_result,"") as HBsAb, if(lab_items_code=360,lo.lab_order_result,"") as HBsAg
from lab_head lh
join lab_order lo on lo.lab_order_number=lh.lab_order_number
where lh.order_date between "2013-10-01" and "2013-10-31"
and lo.lab_items_code in (358,360)
order by vn) as t
group by t.vn

[udomchok]

คำตอบ (ต่อ) :
Step 3 : เขียนใหม่แบบ Join Lab ทีละรายการ หากมีหลายรายการ ก็ Join กันไปเรื่อย ๆ
==============================================
select t1.*, t2.HBsAg from
(select lh.hn, lh.vn, lo.lab_order_result as HBsAb
from lab_head lh
join lab_order lo on lo.lab_order_number=lh.lab_order_number
where lh.order_date between "2013-10-01" and "2013-10-31"
and lo.lab_items_code=358
order by lh.vn) t1
join
(select lh.vn, lo.lab_order_result as HBsAg
from lab_head lh
join lab_order lo on lo.lab_order_number=lh.lab_order_number
where lh.order_date between "2013-10-01" and "2013-10-31"
and lo.lab_items_code=360
order by lh.vn) t2 on t1.vn=t2.vn