... ลองใช้คำสั่งข้างล่าง RUN ใน HOSxP ดูนะครับ ...
... กลุ่มอายุจะใช้วันเกิดมาคำนวณ ไม่ได้ใช้ age_y จากตาราง person ก็จะทำให้ดึงข้อมูลมาดูเมื่อไรก็ได้ โดยไม่ต้องไปสั่ง Update Age ก่อน...
...ถ้าต้องการเปลี่ยนแปลงเงื่อนไขที่ต้องการดู ก็แก้ไขเพิ่มเติมตรง where ได้เลยครับ ....
....
select p.village_id, v.village_moo as "หมู่ที่",v.village_name as "ชื่อหมู่บ้าน",
count(pt.person_id) as "รวมทั้งหมด",
count(pm.person_id) as "รวม(ช.)", count(pf.person_id) as "รวม(ญ.)",
count(p1.person_id) as "ต่ำกว่า 1 ปี(ช.)", count(p2.person_id) as "ต่ำกว่า 1 ปี(ญ.)",
count(p3.person_id) as "1-4 ปี(ช.)", count(p4.person_id) as "1-4 ปี(ญ.)",
count(p5.person_id) as "5-9 ปี(ช.)", count(p6.person_id) as "5-9 ปี(ญ.)",
count(p7.person_id) as "10-14 ปี(ช.)", count(p8.person_id) as "10-14 ปี(ญ.)",
count(p9.person_id) as "15-19 ปี(ช.)", count(p10.person_id) as "15-19 ปี(ญ.)",
count(p11.person_id) as "20-24 ปี(ช.)", count(p12.person_id) as "20-24 ปี(ญ.)",
count(p13.person_id) as "25-29 ปี(ช.)", count(p14.person_id) as "25-29 ปี(ญ.)",
count(p15.person_id) as "30-34 ปี(ช.)", count(p16.person_id) as "30-34 ปี(ญ.)",
count(p17.person_id) as "35-39 ปี(ช.)", count(p18.person_id) as "35-39 ปี(ญ.)",
count(p19.person_id) as "40-44 ปี(ช.)", count(p20.person_id) as "40-44 ปี(ญ.)",
count(p21.person_id) as "45-49 ปี(ช.)", count(p22.person_id) as "45-49 ปี(ญ.)",
count(p23.person_id) as "50-54 ปี(ช.)", count(p24.person_id) as "50-54 ปี(ญ.)",
count(p25.person_id) as "55-59 ปี(ช.)", count(p26.person_id) as "55-59 ปี(ญ.)",
count(p27.person_id) as "60-64 ปี(ช.)", count(p28.person_id) as "60-64 ปี(ญ.)",
count(p29.person_id) as "65-69 ปี(ช.)", count(p30.person_id) as "65-69 ปี(ญ.)",
count(p31.person_id) as "70-74 ปี(ช.)", count(p32.person_id) as "70-74 ปี(ญ.)",
count(p33.person_id) as "75-79 ปี(ช.)", count(p34.person_id) as "75-79 ปี(ญ.)",
count(p35.person_id) as "80-84 ปี(ช.)", count(p36.person_id) as "80-84 ปี(ญ.)",
count(p37.person_id) as "85 ปีขึ้นไป(ช.)", count(p38.person_id) as "85 ปีขึ้นไป(ญ.)",
count(p39.person_id) as "ไม่ทราบอายุ(ช.)", count(p40.person_id) as "ไม่ทราบอายุ(ญ.)"
from person p
left outer join village v on v.village_id = p.village_id
left outer join person pt on pt.person_id = p.person_id
left outer join person pm on pm.person_id = p.person_id and p.sex = '1'
left outer join person pf on pf.person_id = p.person_id and p.sex = '2'
left outer join person p1 on p1.person_id = p.person_id
and timestampdiff(year,p.birthdate,curdate()) < '1' and p.sex = '1'
left outer join person p2 on p2.person_id = p.person_id
and timestampdiff(year,p.birthdate,curdate()) < '1' and p.sex = '2'
left outer join person p3 on p3.person_id = p.person_id
and timestampdiff(year,p.birthdate,curdate()) between '1' and '4' and p.sex = '1'
left outer join person p4 on p4.person_id = p.person_id
and timestampdiff(year,p.birthdate,curdate()) between '1' and '4' and p.sex = '2'
left outer join person p5 on p5.person_id = p.person_id
and timestampdiff(year,p.birthdate,curdate()) between '5' and '9' and p.sex = '1'
left outer join person p6 on p6.person_id = p.person_id
and timestampdiff(year,p.birthdate,curdate()) between '5' and '9' and p.sex = '2'
left outer join person p7 on p7.person_id = p.person_id
and timestampdiff(year,p.birthdate,curdate()) between '10' and '14' and p.sex = '1'
left outer join person p8 on p8.person_id = p.person_id
and timestampdiff(year,p.birthdate,curdate()) between '10' and '14' and p.sex = '2'
left outer join person p9 on p9.person_id = p.person_id
and timestampdiff(year,p.birthdate,curdate()) between '15' and '19' and p.sex = '1'
left outer join person p10 on p10.person_id = p.person_id
and timestampdiff(year,p.birthdate,curdate()) between '15' and '19' and p.sex = '2'
left outer join person p11 on p11.person_id = p.person_id
and timestampdiff(year,p.birthdate,curdate()) between '20' and '24' and p.sex = '1'
left outer join person p12 on p12.person_id = p.person_id
and timestampdiff(year,p.birthdate,curdate()) between '20' and '24' and p.sex = '2'
left outer join person p13 on p13.person_id = p.person_id
and timestampdiff(year,p.birthdate,curdate()) between '25' and '29' and p.sex = '1'
left outer join person p14 on p14.person_id = p.person_id
and timestampdiff(year,p.birthdate,curdate()) between '25' and '29' and p.sex = '2'
left outer join person p15 on p15.person_id = p.person_id
and timestampdiff(year,p.birthdate,curdate()) between '30' and '34' and p.sex = '1'
left outer join person p16 on p16.person_id = p.person_id
and timestampdiff(year,p.birthdate,curdate()) between '30' and '34' and p.sex = '2'
left outer join person p17 on p17.person_id = p.person_id
and timestampdiff(year,p.birthdate,curdate()) between '35' and '39' and p.sex = '1'
left outer join person p18 on p18.person_id = p.person_id
and timestampdiff(year,p.birthdate,curdate()) between '35' and '39' and p.sex = '2'
left outer join person p19 on p19.person_id = p.person_id
and timestampdiff(year,p.birthdate,curdate()) between '40' and '44' and p.sex = '1'
left outer join person p20 on p20.person_id = p.person_id
and timestampdiff(year,p.birthdate,curdate()) between '40' and '44' and p.sex = '2'
left outer join person p21 on p21.person_id = p.person_id
and timestampdiff(year,p.birthdate,curdate()) between '45' and '49' and p.sex = '1'
left outer join person p22 on p22.person_id = p.person_id
and timestampdiff(year,p.birthdate,curdate()) between '45' and '49' and p.sex = '2'
left outer join person p23 on p23.person_id = p.person_id
and timestampdiff(year,p.birthdate,curdate()) between '50' and '54' and p.sex = '1'
left outer join person p24 on p24.person_id = p.person_id
and timestampdiff(year,p.birthdate,curdate()) between '50' and '54' and p.sex = '2'
left outer join person p25 on p25.person_id = p.person_id
and timestampdiff(year,p.birthdate,curdate()) between '55' and '59' and p.sex = '1'
left outer join person p26 on p26.person_id = p.person_id
and timestampdiff(year,p.birthdate,curdate()) between '55' and '59' and p.sex = '2'
left outer join person p27 on p27.person_id = p.person_id
and timestampdiff(year,p.birthdate,curdate()) between '60' and '64' and p.sex = '1'
left outer join person p28 on p28.person_id = p.person_id
and timestampdiff(year,p.birthdate,curdate()) between '60' and '64' and p.sex = '2'
left outer join person p29 on p29.person_id = p.person_id
and timestampdiff(year,p.birthdate,curdate()) between '65' and '69' and p.sex = '1'
left outer join person p30 on p30.person_id = p.person_id
and timestampdiff(year,p.birthdate,curdate()) between '65' and '69' and p.sex = '2'
left outer join person p31 on p31.person_id = p.person_id
and timestampdiff(year,p.birthdate,curdate()) between '70' and '74' and p.sex = '1'
left outer join person p32 on p32.person_id = p.person_id
and timestampdiff(year,p.birthdate,curdate()) between '70' and '74' and p.sex = '2'
left outer join person p33 on p33.person_id = p.person_id
and timestampdiff(year,p.birthdate,curdate()) between '75' and '79' and p.sex = '1'
left outer join person p34 on p34.person_id = p.person_id
and timestampdiff(year,p.birthdate,curdate()) between '75' and '79' and p.sex = '2'
left outer join person p35 on p35.person_id = p.person_id
and timestampdiff(year,p.birthdate,curdate()) between '80' and '84' and p.sex = '1'
left outer join person p36 on p36.person_id = p.person_id
and timestampdiff(year,p.birthdate,curdate()) between '81' and '84' and p.sex = '2'
left outer join person p37 on p37.person_id = p.person_id
and timestampdiff(year,p.birthdate,curdate()) > '84' and p.sex = '1'
left outer join person p38 on p38.person_id = p.person_id
and timestampdiff(year,p.birthdate,curdate()) > '84' and p.sex = '2'
left outer join person p39 on p39.person_id = p.person_id
and (p.birthdate is null or p.birthdate = '') and p.sex = '1'
left outer join person p40 on p40.person_id = p.person_id
and (p.birthdate is null or p.birthdate = '') and p.sex = '2'
where p.village_id <> '1' and p.citizenship = '99'
group by p.village_id
order by p.village_id
หัวข้อ: ฝากให้พี่ขวดช่วยทดสอบครับ (อ่าน 5055 ครั้ง)