หลายครั้งที่เราได้โจทย์ หรือปัญหามาแก้โดยใช้วิธีการ Query จากฐานข้อมูลมา ซึ่งมีรูปแบบการ Query หลายแบบ ได้แก่ การเขียน JOIN ตาราง, UNION หรือ ทำเป็น Sub Query เป็นต้น ในวันนี้ผมขอนำเสนอวิธีการเขียน SELECT IN SELECT ซึ่งเป็นวิธีการเขียน Query แบบหนึ่งที่ช่วยแก้ปัญหาได้มากมาย และลดการเขียน Coding ที่ไม่จำเป็นได้อีก
ตัวอย่างปัญหา เราต้องการดูข้อมูลการจ่ายดอกเบี้ยทั้งหมด เพื่อนำมาออกรายงาน โดยมีเงื่อนไขดังนี้
- ข้อมูลการจ่ายดอกเบี้ยในเดือนเดือนกัน ให้นำมารวมกัน
- ข้อมูลการจ่ายดอกเบี้ยช่วงระหว่างเดือน ให้แยกกลุ่มออกมา เช่น จ่ายดอกเบี้ยระหว่างวันที่ 2014-04-21 ถึง 2014-05-06 ระบบต้องตีความเป็นการจ่ายดอกเบี้ยระหว่างเดือน เมษายน ถึง เดือนพฤษภาคม
ข้อมูลที่ใช้กับตัวอย่างนี้
[sourcecode language="plain"]============================================================================================
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[/sourcecode]
ขั้นตอนการแก้ปัญหา (ถ้ามีการเขียน SQL ผมขอใช้ SYNTAX ของ SQL Server นะครับ)
- Query ข้อมูลการตัดจ่ายดอกเบี้ย โดยต้องกำหนดเงื่อนไขของ DATE FROM กับ DATE TO ว่าอยู่ในเดือนเดียวกัน หรือระหว่างเดือน ซึ่งจากข้อมูลที่ให้มาผลลัพธ์ที่ได้ ควรออกมา ดังนี้
[sourcecode language="plain"]===============================================================================
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[/sourcecode]
โดยใช้ Query ที่มีการตรวจสอบเงื่อนไขของ DATE FROM กับ DATE TO ว่าเป็นเดือนเดียวกัน หรือต่างเดือนกัน ดังนี้
[sql]
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'
[/sql]
- Query ข้อมูลที่ได้จากข้อที่แล้วมา Query ซ้อนอีกครั้ง เพื่อหาผลรวมในแต่และเดือน และระหว่างเดือนมีการจ่ายดอกเบี้ยออกไปเท่าไหร่ ซึ่งจากข้อมูลในข้อที่แล้วผลลัพธ์ที่ได้ควรออกมา ดังนี้
[sourcecode language="plain"]==============================================================
TX TYPE | TX CLASS | TX SUBCLASS | COSTAMOUNT | MONTH RANGE
==============================================================
WITHDRAW | CASH | INTEREST | 146,200.00 | APR
WITHDRAW | CASH | INTEREST | 32,300.00 | APR - MAY[/sourcecode]
โดยจะ SELECT ซ้อนครอบอีกขั้นนึง เพื่อที่เอาผลลัพธ์ที่ได้จาก Query ก้อนด้านในมา SUM หาผลรวมอีกที ดังนี้
[sql]
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
[/sql]
หมายเหตุ: โจทย์นี้อาจจะมีวิธีการอื่นๆในการเขียน Query เพื่อให้ได้ผลลัพธ์ที่เหมือนกัน แต่ที่ผมยกตัวอย่างนี้มา เพื่อที่จะสร้าง Idea และแนวทางให้การปรับประยุกต์ใช้ต่อไปนะครับ
