Back
SS090901 - การค้นหาเลขที่หายไป

Modified on 2009/09/10 14:43 by ninefyi Categorized as SQL Server
ในการทำงานเกี่ยวกับใบเสร็จรับเงิน ตามหลักการบัญชี เลขที่ใบเสร็จจะไม่สามารถที่จะให้แสดงแบบกระโดดข้ามเลข
และถ้าสมมติว่าเราต้องการที่จะตรวจสอบว่า เลขที่ใดบ้างที่หายไป วันนี้เรามีคำตอบมาลองนำเสนอจากหลายๆ คำตอบนะครับ

{{{{WITH TableInvoice(InvNo) AS ( 
     SELECT 'INV0096'
     UNION ALL
     SELECT 'INV0097'
     UNION ALL
     SELECT 'INV0099'
     UNION ALL
     SELECT 'INV0100'
     UNION ALL
     SELECT 'INV0102'
     UNION ALL
     SELECT 'INV0103'
    ),
    CTE AS ( 
     SELECT CAST(SUBSTRING(MIN(InvNo), 4, 4) AS INT) MinInvNo,
            CAST(SUBSTRING(MAX(InvNo), 4, 4) AS INT) MaxInvNo
     FROM TableInvoice
     UNION ALL
     SELECT MinInvNo + 1
        , MaxInvNo
     FROM CTE
     WHERE MinInvNo < MaxInvNo
    )
             
SELECT Formatted
FROM CTE
    CROSS APPLY ( SELECT 'INV' + RIGHT('0000'+ CAST(MinInvNo AS VARCHAR(4)),4) ) TableCommon ( Formatted )
WHERE NOT EXISTS( SELECT InvNo FROM TableInvoice WHERE InvNo = Formatted )
OPTION  ( MAXRECURSION 0 ) ;}}}}

จากตัวอย่างด้านบน เราได้ใช้ ความสามารถของ SQL Server 2005 ในเรื่องการสร้าง Common Table Expression (CTE) สร้างขึ้นมาสอง table คือ TableInvoice อันนี้เป็นตารางที่ใช้สำหรับสร้างข้อมูล ตย. ในการทดลองให้ดู อีกอัน คือ CTE เป็นตารางที่เก็บค่า Min ของ InvNo ที่จะมีการเพิ่มทีละหนึ่งไปเรื่อยๆ จนเท่ากับ MaxInv ดังนั้นจำนวน Record ของ Table CTE ก็จะเท่ากับเลข Max - Min นะครับ หลังจากนั้นแล้วก็ใช่คำสั่งในการตรวจสอบว่าเลขใด ที่หายไป โดยในที่นี้เป็น ตย. การใช้ CROSS APPLY เข้ามาช่วยนะครับ สุดท้ายคือการใช้ OPTION MAXRECURSION เพื่อป้องการการวนรอบใน CTE ที่จะอาจจะเกินจนเป็น self recursive ที่ทำให้ระบบมีปัญหาได้ ซึ่งปกติตัว OPTION นี้จะตั้งไว้เริ่มต้นที่ 100 วนรอบ ดังนั้นในที่นี่เพื่อป้องกัน การวนซ้ำ จึงใช้แค่ 0 คือไม่มีการเรียกตัวเองนะครับ

ลองเอาไปใช้กันดูนะครับ
Meta Keywords:
Meta Description:
Change Comment:
ThaiSQL.COM Wiki and Forum

SS090901 - การค้นหาเลขที่หายไป
