สารบัญ

/*แก้ไข สถานะการจำหน่าย กลับเป็น ยังอยู่โดยใช้คำสั่ง*/

update person as p

set p.person_discharge_id = '9',p.last_update = now()

where p.person_discharge_id ='2'

AND (p.discharge_date IS NULL

OR p.discharge_date = '');

-- เรียกดูซ้ำ

SELECT p.cid,p.pname

, CONCAT(p.fname, SPACE(1),p.lname) AS 'person_name'

,p.person_discharge_id

,p.discharge_date

,p.house_regist_type_id AS 'type_area'

FROM person p

WHERE p.person_discharge_id ='2' AND (p.discharge_date IS NULL

OR p.discharge_date = '')

#========================

/*ตรวจสอบ cid ด้วย mod 11 แล้วผิดพลาด*/

SELECT

concat(p.fname,space(1),p.lname)as person_name,

p.cid ,

if(11-(mod((SUBSTRING(p.cid, 1, 1) * 13)+

(SUBSTRING(p.cid, 2, 1) * 12)+

(     SUBSTRING(p.cid, 3, 1) * 11)+

(SUBSTRING(p.cid, 4, 1) * 10)+

(SUBSTRING(p.cid, 5, 1) * 9)+

(SUBSTRING(p.cid, 6, 1) * 8)+

(SUBSTRING(p.cid, 7, 1) * 7)+

(SUBSTRING(p.cid, 8, 1) * 6)+

(SUBSTRING(p.cid, 9, 1) * 5)+

(SUBSTRING(p.cid, 10, 1) * 4)+

(SUBSTRING(p.cid, 11, 1) * 3)+

(SUBSTRING(p.cid, 12, 1) * 2)+

(SUBSTRING(p.cid, 13, 1) * 1),11))=10 or 11-(mod((SUBSTRING(p.cid, 1, 1) * 13)+

(SUBSTRING(p.cid, 2, 1) * 12)+

(     SUBSTRING(p.cid, 3, 1) * 11)+

(SUBSTRING(p.cid, 4, 1) * 10)+

(SUBSTRING(p.cid, 5, 1) * 9)+

(SUBSTRING(p.cid, 6, 1) * 8)+

(SUBSTRING(p.cid, 7, 1) * 7)+

(SUBSTRING(p.cid, 8, 1) * 6)+

(SUBSTRING(p.cid, 9, 1) * 5)+

(SUBSTRING(p.cid, 10, 1) * 4)+

(SUBSTRING(p.cid, 11, 1) * 3)+

(SUBSTRING(p.cid, 12, 1) * 2)+

(SUBSTRING(p.cid, 13, 1) * 1),11)) =11,'ถูก','ผิด') as 'mod-11cid'

FROM

person p

where if(11-(mod((SUBSTRING(p.cid, 1, 1) * 13)+

(SUBSTRING(p.cid, 2, 1) * 12)+

(     SUBSTRING(p.cid, 3, 1) * 11)+

(SUBSTRING(p.cid, 4, 1) * 10)+

(SUBSTRING(p.cid, 5, 1) * 9)+

(SUBSTRING(p.cid, 6, 1) * 8)+

(SUBSTRING(p.cid, 7, 1) * 7)+

(SUBSTRING(p.cid, 8, 1) * 6)+

(SUBSTRING(p.cid, 9, 1) * 5)+

(SUBSTRING(p.cid, 10, 1) * 4)+

(SUBSTRING(p.cid, 11, 1) * 3)+

(SUBSTRING(p.cid, 12, 1) * 2)+

(SUBSTRING(p.cid, 13, 1) * 1),11))=10 or 11-(mod((SUBSTRING(p.cid, 1, 1) * 13)+

(SUBSTRING(p.cid, 2, 1) * 12)+

(     SUBSTRING(p.cid, 3, 1) * 11)+

(SUBSTRING(p.cid, 4, 1) * 10)+

(SUBSTRING(p.cid, 5, 1) * 9)+

(SUBSTRING(p.cid, 6, 1) * 8)+

(SUBSTRING(p.cid, 7, 1) * 7)+

(SUBSTRING(p.cid, 8, 1) * 6)+

(SUBSTRING(p.cid, 9, 1) * 5)+

(SUBSTRING(p.cid, 10, 1) * 4)+

(SUBSTRING(p.cid, 11, 1) * 3)+

(SUBSTRING(p.cid, 12, 1) * 2)+

(SUBSTRING(p.cid, 13, 1) * 1),11)) =11,'true','false') ='false'

   #---------------------

ตัวอย่าง

person_name cid mod-11cid
เต๊กยิว xxx 0000000086966 ผิด
อองกา xxx 0000000087192 ผิด
มู xxx 0000000087222 ผิด
ดา xxx 0000000088353 ผิด
สมชาย xxx 1720500008807 ผิด

/*person_disharge_as "สาบสูญ"*/

select p.person_id,p.cid,concat(p.fname,space(1),p.lname) as 'person_name',TIMESTAMPDIFF(year,p.birthdate,p.last_update)AS'age_y',p.house_regist_type_id as'Type_area',p.person_discharge_id,di.person_discharge_name

from person AS p

inner join person_discharge as di on di.person_discharge_id = p.person_discharge_id

where p.person_discharge_id ='3'

#--------------------

ตัวอย่าง

person_id cid person_name age_y Type_area person_discharge_id person_discharge_name
7819 8888888888888 เฉลิมวุฒิ XXX 28 1 3 สาบสูญ
12948 8888888888888 เอ XXX 33 4 3 สาบสูญ
55305 8888888888888 เจนนิสา XXX 17 4 3 สาบสูญ
55368 8888888888888 เมธาพร XXX 0 4 3 สาบสูญ
56932 8888888888888 สุกานดา XXX 22 4 3 สาบสูญ