คำสั่งดึงข้อมูลล่าสุด

[munsat]

ขอตัวอย่างคำสั่ง SQL ดึงข้อมูลการตรวจ HbA1C ครั้งหลังสุด ของแต่ละคนที่ได้รับการตรวจในปีงบประมาณ 2561 บ้างครับ

[BlueEye]

แก้เลข 319 ให้เป็นรหัส HBA1C ของโรงพยาบาลด้วยนะครับ

โค๊ด: [Select]


SELECT q2.vstdate, q2.hn, q2.lab_order_result FROM
(SELECT q1.*
, @rnk := IF(@prevn = q1.hn, @rnk + 1, 1) AS rnk
, (@prevn := q1.hn) AS prevn
FROM(SELECT v.vstdate,h.hn,o.lab_order_result
FROM ovst AS v
INNER JOIN lab_head AS h ON v.vn = h.vn
INNER JOIN lab_order AS o ON h.lab_order_number = o.lab_order_number
, (SELECT @prevn := "", @rnk := 0) AS var1
WHERE o.lab_items_code = "319" AND
v.vstdate BETWEEN "2017-10-01" AND "2018-09-30"
ORDER BY v.hn ASC, v.vstdate DESC) AS q1) AS q2
WHERE q2.rnk = 1



[udomchok]

แก้เลข 319 ให้เป็นรหัส HBA1C ของโรงพยาบาลด้วยนะครับ

โค๊ด: [Select]


SELECT q2.vstdate, q2.hn, q2.lab_order_result FROM
(SELECT q1.*
, @rnk := IF(@prevn = q1.hn, @rnk + 1, 1) AS rnk
, (@prevn := q1.hn) AS prevn
FROM(SELECT v.vstdate,h.hn,o.lab_order_result
FROM ovst AS v
INNER JOIN lab_head AS h ON v.vn = h.vn
INNER JOIN lab_order AS o ON h.lab_order_number = o.lab_order_number
, (SELECT @prevn := "", @rnk := 0) AS var1
WHERE o.lab_items_code = "319" AND
v.vstdate BETWEEN "2017-10-01" AND "2018-09-30"
ORDER BY v.hn ASC, v.vstdate DESC) AS q1) AS q2
WHERE q2.rnk = 1



ปรับเป็น รหัส 7 หลักเลยครับ
lab_items.provis_labcode ครับ

[BlueEye]

แบบใช้ provis_labcode ครับ

โค๊ด: [Select]

SELECT q2.vstdate, q2.hn, q2.lab_order_result FROM
(SELECT q1.*
, @rnk := IF(@prevn = q1.hn, @rnk + 1, 1) AS rnk
, (@prevn := q1.hn) AS prevn
FROM(SELECT v.vstdate,h.hn,o.lab_order_result,i.provis_labcode
FROM ovst AS v
INNER JOIN lab_head AS h ON v.vn = h.vn
INNER JOIN lab_order AS o ON h.lab_order_number = o.lab_order_number
INNER JOIN lab_items AS i ON o.lab_items_code = i.lab_items_code
, (SELECT @prevn := "", @rnk := 0) AS var1
WHERE i.provis_labcode = "0531601" AND
v.vstdate BETWEEN "2017-10-01" AND "2018-09-30"
ORDER BY v.hn ASC, v.vstdate DESC) AS q1) AS q2
WHERE q2.rnk = 1


[woravet]

ลองใช้ของ opdscreen
select s.hn
,substring_index(group_concat(distinct s.vstdate order by vn desc),',',1) vstdate
,substring_index(group_concat(hba1c order by vn desc),',',1) hba1c
from opdscreen s
where s.vstdate between '2017-10-1' and '2018-9-30'
and s.hba1c>0
group by s.hn