1 5 2553 2553 SELECT didname MAXdrugcost1 FROM
- Slides: 50
เฉลยขอ 1 • ราคาทน สงทสด 5 อนดบแรกของ ไตรมาสแรกของปงบประมาณ 2553 (เดอนต. ค. -ธ. ค. 2553) SELECT didname, MAX(drugcost*1) FROM drug WHERE MONTH(date_serv) =10 OR 11 OR 12 GROUP BY didname ORDER BY MAX(drugcost*1) DESC LIMIT 5; 4
เฉลยขอ 1 • ราคาขายสงทสด 5 อนดบแรกของ ไตรมาสแรกของปงบประมาณ 2553 (เดอนต. ค. -ธ. ค. 2553) SELECT didname, MAX(drugpric*1) FROM drug WHERE MONTH(date_serv) =10 OR 11 OR 12 GROUP BY didname ORDER BY MAX(drugpric*1) DESC LIMIT 5; 6
โจทยขอ • 1 ราคากำไร สงทสด 5 อนดบแรกของไตรมาสแรกของปงบประมาณ (เดอนต. ค. -ธ. ค. 2553) 2553 SELECT didname, MAX((drugpric*1)-(drugcost*1)) FROM drug WHERE MONTH(date_serv) =10 OR 11 OR 12 GROUP BY didname ORDER BY MAX((drugpric*1)-(drugcost*1)) DESC LIMIT 5; 8
Dummy Table m num_all num_correct percent_correct 200904 200905 50 100 40 65 80 65 …. …. …. … … …
เฉลย 2. 1 SELECT t 1. m, num_all, num_correct/num_all*100 AS percent_correct FROM # จำนวนขอมลทงหมดใน EPI (SELECT LEFT(date_serv, 6) AS m, COUNT(*) AS num_all FROM epi GROUP BY LEFT(date_serv, 6)) t 1 # -------------------------INNER JOIN # จำนวนขอมลทไมซำ และถกตองตามเงอนไข (SELECT LEFT(date_serv, 6) AS m, COUNT(*) AS num_correct FROM (SELECT DISTINCT pcucode, cid, date_serv, vcctype FROM epi WHERE LENGTH(cid)=13) t 1 GROUP BY LEFT(date_serv, 6)) t 2 # -------------------------ON t 1. m=t 2. m; 12
Dummy Table m num_all num_correct percent_correct 200904 200905 50 100 40 65 80 65 …. …. …. … … …
เฉลย 2. 2 SELECT t 1. m, num_all, num_correct/num_all*100 AS percent_correct FROM ##จำนวนขอมลทงหมดใน FP (SELECT LEFT(date_serv, 6) AS m, COUNT(*) AS num_all FROM fp GROUP BY LEFT(date_serv, 6)) t 1 #___________________ INNER JOIN # จำนวนขอมลทไมซำ และจำแนกรายเดอน (SELECT LEFT(date_serv, 6) AS m, COUNT(*) AS num_correct FROM # เลอกขอมลทไมซำ (SELECT DISTINCT pcucode, cid, date_serv, fptype FROM fp a INNER JOIN nhso_12. pat b ON a. cid=b. person_id WHERE (fptype IN ('1', '2', '3', '4', '7') AND sex='2' AND TIMESTAMPDIFF(YEAR, dob, date_serv) BETWEEN 9 AND 60) OR (fptype = '6'AND sex='1') )t #__________________ GROUP BY LEFT(date_serv, 6)) t 2 #___________________ ON t 1. m=t 2. m; 15
Dummy Table m num_all num_correct percent_correct 200904 200905 50 100 40 65 80 65 …. …. …. … … …
เฉลย 2. 3 SELECT t 1. m, num_all, num_correct/num_all*100 AS percent_correct FROM #จำนวนขอมลทงหมดใน ANC (SELECT LEFT(date_serv, 6) AS m, COUNT(*) AS num_all FROM anc GROUP BY LEFT(date_serv, 6)) t 1 #______________ INNER JOIN # จำนวนขอมลทไมซำ และจำแนกรายเดอน (SELECT LEFT(date_serv, 6) AS m, COUNT(*) AS num_correct FROM # เลอกขอมลทไมซำ (SELECT DISTINCT pcucode, cid, date_serv FROM anc a INNER JOIN nhso_12. pat b ON a. cid=b. person_id WHERE (sex = '2' AND TIMESTAMPDIFF(YEAR, dob, date_serv) BETWEEN 9 AND 60 AND ga BETWEEN 4 AND 45 AND ancres <>'') )t 1 #_________________ GROUP BY LEFT(date_serv, 6))t 2 #_________________ ON t 1. m=t 2. m; #_________________ 18
Dummy Table m num_all num_correct percent_correct 200904 200905 50 100 40 65 80 65 …. …. …. … … …
เฉลย 2. 4 SELECT t 1. m, num_all, num_correct/num_all*100 AS percent_correct FROM # จำนวนขอมลทงหมดใน mch จำแนกรายเดอน (SELECT LEFT(ppcare 1, 6) AS m, COUNT(*) AS num_all FROM mch GROUP BY LEFT(ppcare 1, 6)) t 1 #-------------INNER JOIN # จำนวนขอมลทไมซำ และตรงตามเงอนไข จำแนกรายเดอน (SELECT LEFT(ppcare 1, 6) AS m, COUNT(*) AS num_correct FROM # ขอมลทไมซำ และตรงตามเงอนไข (SELECT ppcare 1, ppcare 2, ppcare 3, sex, dob FROM mch a INNER JOIN nhso_12. pat b ON a. cid = b. person_id WHERE (sex = '2') AND (TIMESTAMPDIFF(YEAR, dob, PPCARE 1) BETWEEN 9 AND 60) AND (PPCARE 1 <> '') AND ((PPCARE 2 IS NULL) OR (PPCARE 2='') OR (PPCARE 2>PPCARE 1)) AND ((PPCARE 3 IS NULL) OR (PPCARE 3='') OR ((PPCARE 3>PPCARE 2) AND (PPCARE 3>PPCARE 1)))) t #-------------GROUP BY LEFT(PPCARE 1, 6)) t 2; #-------------ON t 1. m=t 2. m 21
Dummy Table m num_all num_correct percent_correct 200904 200905 50 100 40 65 80 65 …. …. …. … … …
เฉลย 2. 5 SELECT t 1. m, num_all, num_correct/num_all*100 AS percent_correct FROM #จำนวนขอมลทงหมดใน PP (SELECT LEFT(bdate, 6) AS m, COUNT(*) AS num_all FROM pp GROUP BY LEFT(bdate, 6)) t 1 #____________ INNER JOIN #ขอมลทไมซำ และจำแนกรายเดอน (SELECT LEFT(bdate, 6) AS m, COUNT(*) AS num_correct FROM #_____________ #ขอมลทไมซำ และตรงตามเงอนไข (SELECT pcucode, cid, MAX(bdate) AS bdate FROM pp WHERE (bweigth >=500) AND (bcare 1<>'') AND((bcare 2 IS NULL) OR (bcare 2='') OR (bcare 2>bcare 1)) AND((bcare 3 IS NULL) OR (bcare 3='') OR ((bcare 3>bcare 2) AND (bcare 3>bcare 1))) GROUP BY pcucode, cid) t #--------------GROUP BY LEFT(bdate, 6)) t 2 #--------------ON t 1. m=t 2. m; 24
เฉลย 3. 1 SELECT LEFT(datedx, 7) AS MONTH, COUNT(DISTINCT hn) AS c FROM odx WHERE dxtype=1 AND diag >= 'E 10' and diag < 'E 15' GROUP BY LEFT(datedx, 7); 27
เฉลย 3. 2 SELECT LEFT(datedx, 7) AS MONTH, hn, COUNT(*) AS c FROM odx WHERE dxtype=1 AND diag >= 'E 10' and diag < 'E 15' GROUP BY LEFT(datedx, 7), hn; 29
เฉลย 3. 3 • #จำแนกตามรปแบบของความถของการมารบบรการ SELECT freq, COUNT(*) AS num FROM (SELECT LEFT(datedx, 7) AS m, hn, COUNT(*) AS freq FROM odx WHERE dxtype=1 AND diag >= 'E 10' and diag < 'E 15' GROUP BY LEFT(datedx, 7), hn) t GROUP BY freq; 31
เฉลย 3. 3 • #จำแนกตามวนทมารบบรการ SELECT DAY(datedx) AS d, COUNT(*) AS freq FROM odx WHERE dxtype=1 AND diag >= 'E 10' and diag < 'E 15' GROUP BY DAY(datedx); 32
เฉลย 3. 3 • #จำแนกตามวนในสปดาหทมารบบรการ SELECT DATE_FORMAT(datedx, '%W') AS d, COUNT(*) AS freq FROM odx WHERE dxtype=1 AND diag >= 'E 10' and diag < 'E 15' GROUP BY DATE_FORMAT(datedx, '%W'); 33
เฉลย 3. 4 SELECT TIMESTAMPDIFF(MONTH, first_dx, CURDATE()) AS m, COUNT(*) AS freq FROM (SELECT hn, MIN(datedx) AS first_dx FROM odx WHERE dxtype=1 AND (diag >= 'E 11' AND diag < 'E 15') GROUP BY hn) t GROUP BY TIMESTAMPDIFF(MONTH, first_dx, CURDATE()); 35
เฉลยขอ 4. 1 • จำนวนเดกทควรไดรบ (กลมเปาหมาย ) SELECT COUNT(DISTINCT person_id) FROM (SELECT HCODE, PERSON_ID, DOB, TIMESTAMPDIFF(MONTH, dob, '2009 -06 -30') AS cnt_month FROM PAT)t WHERE cnt_month BETWEEN 6 AND 12 39
เฉลยขอ 4. 1 • จำนวนเดกทควรไดรบและไดรบแลว (กลมเปาหมายทไดรบ ( SELECT COUNT(DISTINCT cid) FROM (SELECT * FROM (SELECT HCODE, PERSON_ID, DOB, TIMESTAMPDIFF(MONTH, dob, '2009 -06 -30') AS cnt_month FROM PAT) age ## age WHERE cnt_month BETWEEN 6 AND 12) pop /*กลมเปาหมายทควรฉด */ INNER JOIN nhso_8. epi ON pop. person_id=epi. cid WHERE pcucode=vccplace AND (vcctype=033 OR vcctype=093 OR vcctype='DTP') 40
เฉลยขอ 4. 1 • #ความครอบคลม SELECT vaccine , pop, vaccine/pop*100 FROM ( SELECT (SELECT COUNT(DISTINCT cid) FROM (SELECT * FROM (SELECT HCODE, PERSON_ID, DOB, TIMESTAMPDIFF(MONTH, dob, '2009 -06 -30') AS cnt_month FROM PAT) age ## age WHERE cnt_month BETWEEN 6 AND 12) pop /*กลมเปาหมายทควรฉด */ INNER JOIN nhso_8. epi ON pop. person_id=epi. cid WHERE pcucode=vccplace AND (vcctype=033 OR vcctype=093 OR vcctype='DTP')) AS vaccine, /*กลมเปาหมายทควรฉด และไดรบวคซน */ (SELECT COUNT(DISTINCT person_id) FROM (SELECT HCODE, PERSON_ID, DOB, TIMESTAMPDIFF(MONTH, dob, '2009 -06 -30') AS cnt_month FROM PAT)t WHERE cnt_month BETWEEN 6 AND 12) AS pop )/*กลมเปาหมายทควรฉด */ t 2 #(t 2 new 41 table)
เฉลยขอ 4. 2 • ผปวยทไมเคยไดรบวคซน SELECT COUNT(*) FROM (SELECT DISTINCT notreceive. person_id FROM (SELECT pop. HCODE, pop. person_id, pop. dob, dtp. cid FROM (SELECT * FROM (SELECT HCODE, PERSON_ID, DOB, TIMESTAMPDIFF(MONTH, dob, '2009 -06 -30') AS cnt_month FROM PAT)t /*เลอกจำนวนจาก pat 1*/ WHERE cnt_month BETWEEN 6 AND 72) pop /* เลอกอาย 2*/ LEFT JOIN (SELECT * FROM nhso_8. epi)vac WHERE(vcctype=033 OR vcctype=093 OR vcctype='DTP'))dtp ON pop. person_id=dtp. cid WHERE dtp. cid IS NULL)notreceive #ไมไดรบวคซน INNER JOIN (SELECT DISTINCT odx. person_id, odx. diag, age. dob, age. cnt_month FROM (SELECT * FROM (SELECT HCODE, PERSON_ID, DOB, TIMESTAMPDIFF(MONTH, dob, '2009 -06 -30') AS cnt_month FROM PAT)t WHERE cnt_month BETWEEN 6 AND 72)age INNER JOIN odx ON age. person_id=odx. person_id 44
เฉลยขอ 4. 2 • ผทไมเคยไดรบวคซน SELECT COUNT(person_id) FROM (SELECT pop. HCODE , pop. person_id, pop. dob, dtp. cid FROM (SELECT * FROM (SELECT HCODE, PERSON_ID, DOB, TIMESTAMPDIFF(MONTH, dob, '2009 -06 -30') AS cnt_month FROM PAT)t WHERE cnt_month BETWEEN 6 AND 72) pop LEFT JOIN (SELECT * FROM nhso_8. epi)vac WHERE(vcctype=033 OR vcctype=093 OR vcctype='DTP'))dtp ON pop. person_id=dtp. cid WHERE dtp. cid IS NULL )not 1 45
เฉลยขอ 4. 2 • ผปวยและไดรบวคซน SELECT COUNT(person_id) FROM (SELECT DISTINCT * FROM (SELECT DISTINCT cid FROM (SELECT * FROM (SELECT HCODE, PERSON_ID, DOB, TIMESTAMPDIFF(MONTH, dob, '2009 -06 -30') AS cnt_month. FROM PAT)t /*เลอกจำนวน จาก pat 1*/ WHERE cnt_month BETWEEN 6 AND 72) pop /*เลอกอาย 2*/ INNER JOIN nhso_8. epi ON pop. person_id=epi. cid WHERE pcucode=vccplace AND (vcctype=033 OR vcctype=093 OR vcctype='DTP')) receive /*หาคนทไดรบวคซน 3*/ INNER JOIN (SELECT DISTINCT odx. person_id, odx. diag, age. dob, age. cnt_month FROM (SELECT * FROM (SELECT HCODE, PERSON_ID, DOB, TIMESTAMPDIFF(MONTH, dob, '2009 -06 -30') AS cnt_month. FROM PAT)t WHERE cnt_month BETWEEN 6 AND 72)age INNER JOIN odx ON age. person_id=odx. person_id WHERE odx. diag LIKE 'A 36%') sick /*หาคนปวยจาก 46 เปา */ON receive. cid=sick. person_id)t 2
เฉลยขอ 4. 2 • ผไดรบวคซน SELECT COUNT(cid)FROM(SELECT DISTINCT cid FROM (SELECT * FROM (SELECT HCODE, PERSON_ID, DOB, TIMESTAMPDIFF(MONTH, dob, '2009 -06 -30') AS cnt_month FROM PAT)t WHERE cnt_month BETWEEN 6 AND 72) pop # คำนวณอาย INNER JOIN nhso_8. epi ON pop. person_id=epi. cid WHERE pcucode=vccplace AND (vcctype=033 OR vcctype=093 OR vcctype='DTP'))rec 1 47
เฉลย 4. 2 • #ประสทธผล SELECT sicknotreceive, sickreceive, ((sicknotreceive/notreceive)-(sickreceive/receive))*100/(sicknotreceive/notreceive) AS effectiveness FROM) )SELECT (SELECT COUNT(*) FROM (SELECT DISTINCT notreceive. person_id FROM (SELECT pop. HCODE, pop. person_id, pop. dob, dtp. cid FROM (SELECT * FROM (SELECT HCODE, PERSON_ID, DOB, TIMESTAMPDIFF(MONTH, dob, '2009 -06 -30') AS cnt_month FROM PAT)t /*เลอกจำนวนจาก pat 1*/ WHERE cnt_month BETWEEN 6 AND 72) pop /*เลอกอาย 2*/ LEFT JOIN (SELECT * FROM nhso_8. epi)vac WHERE(vcctype=033 OR vcctype=093 OR vcctype='DTP'))dtp ON pop. person_id=dtp. cid WHERE dtp. cid IS NULL)notreceive #ไมไดรบวคซน INNER JOIN (SELECT DISTINCT odx. person_id, odx. diag, age. dob, age. cnt_month FROM (SELECT * FROM (SELECT HCODE, PERSON_ID, DOB, TIMESTAMPDIFF(MONTH, dob, '2009 -06 -30') AS cnt_month FROM PAT)t WHERE cnt_month BETWEEN 6 AND 72)age INNER JOIN odx ON age. person_id=odx. person_id WHERE odx. diag LIKE 'A 36%') sick ON notreceive. person_id=sick. person_id)t)sicknotreceive, #ไมไดรบวคซนและปวย ##สมการ นบจำนวน ตวแปรท 1 48
เฉลย 4. 2 • ประสทธผล (ตอ ( (SELECT COUNT(person_id) FROM (SELECT pop. HCODE, pop. person_id, pop. dob, dtp. cid FROM (SELECT * FROM (SELECT HCODE, PERSON_ID, DOB, TIMESTAMPDIFF(MONTH, dob, '2009 -06 -30') AS cnt_month FROM PAT)t WHERE cnt_month BETWEEN 6 AND 72) pop LEFT JOIN (SELECT * FROM nhso_8. epi)vac WHERE(vcctype=033 OR vcctype=093 OR vcctype='DTP'))dtp ON pop. person_id=dtp. cid WHERE dtp. cid IS NULL )not 1)notreceive, #ไมไดรบวคซน ##สมการ นบจำนวน ตวแปรท 2 (SELECT COUNT(person_id) FROM (SELECT DISTINCT * FROM (SELECT DISTINCT cid FROM (SELECT * FROM (SELECT HCODE, PERSON_ID, DOB, TIMESTAMPDIFF(MONTH, dob, '2009 -06 -30') AS cnt_month FROM PAT)t /*เลอกจำนวนจาก pat 1*/ WHERE cnt_month BETWEEN 6 AND 72) pop /*เลอกอาย 2*/ INNER JOIN nhso_8. epi ON pop. person_id=epi. cid WHERE pcucode=vccplace AND (vcctype=033 OR vcctype=093 OR vcctype='DTP'))receive /*หาคนทไดรบวคซน 3*/ INNER JOIN (SELECT DISTINCT odx. person_id, odx. diag, age. dob, age. cnt_month FROM (SELECT * FROM (SELECT HCODE, PERSON_ID, DOB, TIMESTAMPDIFF(MONTH, dob, '2009 -06 -30') AS cnt_month FROM PAT)t WHERE cnt_month BETWEEN 6 AND 72)age INNER JOIN odx ON age. person_id=odx. person_id WHERE odx. diag LIKE 'A 36%') sick /*หาคนปวยจากเปา */ON receive. cid=sick. person_id)t 2)sickreceive, #ไดรบวคซนและปวย ##สมการ นบจำนวน ตวแปรท 3 49
เฉลย 4. 2 • ประสทธผล (ตอ ( (SELECT COUNT(cid)FROM(SELECT DISTINCT cid FROM (SELECT * FROM (SELECT HCODE, PERSON_ID, DOB, TIMESTAMPDIFF(MONTH, dob, '2009 -06 -30') AS cnt_month FROM PAT)t WHERE cnt_month BETWEEN 6 AND 72) pop INNER JOIN nhso_8. epi ON pop. person_id=epi. cid WHERE pcucode=vccplace AND (vcctype=033 OR vcctype=093 OR vcctype='DTP'))rec 1)receive/*#ไดรบวคซน สมการ นบจำนวน ตวแปรท 4*/))alldata 50
Nemuadmin
Select * from select
Select * from select
Select * from select
Metsta
Seam welding symbol
Visual basic select
Select group
Python select case
Which sentence is punctuated correctly?
Down select process
Select * from orders
Mosfet package types
Investment banking hours
Centrifugal pump is power absorbing device
Sql select anidados
Avaya aura contact center overview ppt
Select a title for your poster
Characteristics of sporozoa
Prime choice select
Select actiris
Shrpe ratio
To make place decisions marketers select the right
Chapter 5
Select committee training
What channel is disney on spectrum
Gulf power energy select program
Select * from tab
Select * from emp;
"select" is a transaction control language
How does rentals affect filipino entrepreneurs
Deptno.
Nhibernate select distinct
Select case if
Avaya contact center select business continuity
Order processing clerk salary
Block diagram of 8251
Choose the correct one ne demek
Carry select adder verilog
Select an appropriate display
Algorithms for select and join operations
Select a title for your poster
Heap select
Select a title for your poster
Select ename min(sal) from emp
Htpps cris txdot gov public purchase
Dml sql
Orcad schematic symbols
Sally wants to select an entire paragraph
Select distinct sql
Hibbeler statics solutions
