SELECT ซ้อน SELECT

หลายครั้งที่เราได้โจทย์ หรือปัญหามาแก้โดยใช้วิธีการ Query จากฐานข้อมูลมา ซึ่งมีรูปแบบการ Query หลายแบบ ได้แก่ การเขียน JOIN ตาราง, UNION หรือ ทำเป็น Sub Query เป็นต้น ในวันนี้ผมขอนำเสนอวิธีการเขียน SELECT IN SELECT ซึ่งเป็นวิธีการเขียน Query แบบหนึ่งที่ช่วยแก้ปัญหาได้มากมาย และลดการเขียน Coding ที่ไม่จำเป็นได้อีก

ตัวอย่างปัญหา เราต้องการดูข้อมูลการจ่ายดอกเบี้ยทั้งหมด เพื่อนำมาออกรายงาน โดยมีเงื่อนไขดังนี้

• ข้อมูลการจ่ายดอกเบี้ยในเดือนเดือนกัน ให้นำมารวมกัน
• ข้อมูลการจ่ายดอกเบี้ยช่วงระหว่างเดือน ให้แยกกลุ่มออกมา เช่น จ่ายดอกเบี้ยระหว่างวันที่ 2014-04-21 ถึง 2014-05-06 ระบบต้องตีความเป็นการจ่ายดอกเบี้ยระหว่างเดือน เมษายน ถึง เดือนพฤษภาคม

ข้อมูลที่ใช้กับตัวอย่างนี้

============================================================================================
TX ID | TX TYPE | TX CLASS | TX SUBCLASS | CUSTOMER | COSTAMOUNT | DATE FROM | DATE TO
============================================================================================
1001 | WITHDRAW | CASH | INTEREST | CUS01 | 50,000.00 | 2014-04-01 | 2014-04-12
1002 | DEPOSIT | CASH | RENT | CUS01 | 20,000.00 | 2014-04-01 | 2014-04-04
1003 | WITHDRAW | CASH | INTEREST | CUS02 | 34,200.00 | 2014-04-01 | 2014-04-08
1004 | WITHDRAW | CASH | INTEREST | CUS01 | 12,000.00 | 2014-04-15 | 2014-04-23
1005 | WITHDRAW | CASH | INTEREST | CUS02 | 50,000.00 | 2014-04-09 | 2014-04-18
1006 | WITHDRAW | CASH | INTEREST | CUS01 | 20,000.00 | 2014-04-15 | 2014-05-16
1007 | WITHDRAW | CASH | INTEREST | CUS02 | 12,300.00 | 2014-04-21 | 2014-05-16

ขั้นตอนการแก้ปัญหา (ถ้ามีการเขียน SQL ผมขอใช้ SYNTAX ของ SQL Server นะครับ)

• Query ข้อมูลการตัดจ่ายดอกเบี้ย โดยต้องกำหนดเงื่อนไขของ DATE FROM กับ DATE TO ว่าอยู่ในเดือนเดียวกัน หรือระหว่างเดือน ซึ่งจากข้อมูลที่ให้มาผลลัพธ์ที่ได้ ควรออกมา ดังนี้

===============================================================================
TX ID | TX TYPE | TX CLASS | TX SUBCLASS | CUSTOMER | COSTAMOUNT |MONTH RANGE
===============================================================================
1001 | WITHDRAW | CASH | INTEREST | CUS01 | 50,000.00 | APR
1003 | WITHDRAW | CASH | INTEREST | CUS02 | 34,200.00 | APR
1004 | WITHDRAW | CASH | INTEREST | CUS01 | 12,000.00 | APR
1005 | WITHDRAW | CASH | INTEREST | CUS02 | 50,000.00 | APR
1006 | WITHDRAW | CASH | INTEREST | CUS01 | 20,000.00 | APR - MAY
1007 | WITHDRAW | CASH | INTEREST | CUS02 | 12,300.00 | APR - MAY

โดยใช้ Query ที่มีการตรวจสอบเงื่อนไขของ DATE FROM กับ DATE TO ว่าเป็นเดือนเดียวกัน หรือต่างเดือนกัน ดังนี้

SELECT txid,
       txtype,
       txclass,
       txsubclass,
       costamount,
       CASE
         WHEN Month(datefrom) = Month(dateto)
              AND Year(datefrom) = Year(dateto) THEN CONVERT(VARCHAR(3),
                                                     Datename(month, datefrom))
         ELSE CONVERT(VARCHAR(3), Datename(month, datefrom))
              + ' - '
              + CONVERT(VARCHAR(3), Datename(month, dateto))
       END AS MONTHRANGE
FROM   txtest CTX
WHERE  CTX.txtype = 'WITHDRAW'
       AND CTX.txclass = 'CASH'
       AND CTX.txsubclass = 'INTEREST' 

• Query ข้อมูลที่ได้จากข้อที่แล้วมา Query ซ้อนอีกครั้ง เพื่อหาผลรวมในแต่และเดือน และระหว่างเดือนมีการจ่ายดอกเบี้ยออกไปเท่าไหร่ ซึ่งจากข้อมูลในข้อที่แล้วผลลัพธ์ที่ได้ควรออกมา ดังนี้

==============================================================
TX TYPE | TX CLASS | TX SUBCLASS | COSTAMOUNT | MONTH RANGE
==============================================================
WITHDRAW | CASH | INTEREST | 146,200.00 | APR
WITHDRAW | CASH | INTEREST | 32,300.00 | APR - MAY

โดยจะ SELECT ซ้อนครอบอีกขั้นนึง เพื่อที่เอาผลลัพธ์ที่ได้จาก Query ก้อนด้านในมา SUM หาผลรวมอีกที ดังนี้

SELECT TMP.txtype,
       TMP.txclass,
       TMP.txsubclass,
       Sum(TMP.costamount),
       TMP.monthrange
FROM   (SELECT txid,
               txtype,
               txclass,
               txsubclass,
               costamount,
               CASE
                 WHEN Month(datefrom) = Month(dateto)
                      AND Year(datefrom) = Year(dateto) THEN CONVERT(VARCHAR(3),
                 Datename(month, datefrom))
                 ELSE CONVERT(VARCHAR(3), Datename(month, datefrom))
                      + ' - '
                      + CONVERT(VARCHAR(3), Datename(month, dateto))
               END AS MONTHRANGE
        FROM   txtest CTX
        WHERE  CTX.txtype = 'WITHDRAW'
               AND CTX.txclass = 'CASH'
               AND CTX.txsubclass = 'INTEREST') TMP
GROUP  BY TMP.txtype,
          TMP.txclass,
          TMP.txsubclass,
          TMP.monthrange 

หมายเหตุ: โจทย์นี้อาจจะมีวิธีการอื่นๆในการเขียน Query เพื่อให้ได้ผลลัพธ์ที่เหมือนกัน แต่ที่ผมยกตัวอย่างนี้มา เพื่อที่จะสร้าง Idea และแนวทางให้การปรับประยุกต์ใช้ต่อไปนะครับ