หมวดหมู่: Database

กลับแถวตาราง (pivot/crosstab query )กลับแถวตาราง (pivot/crosstab query )

2011-07-232011-07-23| phunsanitphunsanit| 0 Comment | 00:00

เวลาทำรายงานให้แสดงรายรับ รายจ่าย รายปี โดยแสดงข้อมูลของแต่ละเดือนให้อยู่ในแถวเดียวกัน แบบนี้

cyear month1 month2 month3 month4 month5 month6 month7 month8 month9 month10 month11 month12
2554 0 5 3 0 34 0 32 32 0 0 0 0
2555 29 765 97 99 0 0 98 9 98 0 0 22

แต่ในตารางเก็บแยกข้อมูลเป็นเดือนละแถว

cyear cmonth expense income
2554 2 5 77
2554 3 3 15
2554 5 34 5
2554 7 32 64
2554 8 32 64
2554 9 0 0
2555 1 29 38
2555 2 765 32
2555 3 97 24
2555 4 99 8
2555 7 98 76
2555 8 9 89
2555 9 98 8
2555 11 0 98
2555 12 22 0

ถ้าคิวรี่ตามปกติ แถวหนึ่งก็ต้องคิวรี่ครั้งหนึ่ง แสดง 1 ปีก็คิวรี่ 12 ครั้ง ถ้าต้องการคิวรี่แค่ครั้งเดียวสามารถใช้วิธี crosstab ช่วยได้ ก่อนอื่นสร้างข้อมูลตัวอย่างก่อน

CREATE TABLE IF NOT EXISTS `account` (`cyear` int (4) NOT NULL,
 `cmonth` int (11) NOT NULL,
 `expense` decimal (10,0) NOT NULL,
 `income` decimal (10,0) NOT NULL,
 PRIMARY KEY (`cyear`,`cmonth`)) ENGINE=InnoDB;

INSERT INTO `account` (`cyear`, `cmonth`, `expense`, `income`) VALUES (2554, 2, 5, 77) , (2554, 3, 3, 15) , (2554, 5, 34, 5) , (2554, 7, 32, 64) , (2554, 8, 32, 64) , (2554, 9, 0, 0) , (2555, 1, 29, 38) , (2555, 2, 765, 32) , (2555, 3, 97, 24) , (2555, 4, 99, 8) , (2555, 7, 98, 76) , (2555, 8, 9, 89) , (2555, 9, 98, 8) , (2555, 11, 0, 98) , (2555, 12, 22, 0) ;

จากนั้นก็คิวรี่โดยใช้

SELECT `cyear`
,SUM (IF (`cmonth` = 1 ,expense ,0)) AS month1
,SUM (IF (`cmonth` = 2 ,expense ,0)) AS month2
,SUM (IF (`cmonth` = 3 ,expense ,0)) AS month3
,SUM (IF (`cmonth` = 4 ,expense ,0)) AS month4
,SUM (IF (`cmonth` = 5 ,expense ,0)) AS month5
,SUM (IF (`cmonth` = 6 ,expense ,0)) AS month6
,SUM (IF (`cmonth` = 7 ,expense ,0)) AS month7
,SUM (IF (`cmonth` = 8 ,expense ,0)) AS month8
,SUM (IF (`cmonth` = 9 ,expense ,0)) AS month9
,SUM (IF (`cmonth` = 10 ,expense ,0)) AS month10
,SUM (IF (`cmonth` = 11 ,expense ,0)) AS month11
,SUM (IF (`cmonth` = 12 ,expense ,0)) AS month12
FROM `account`
GROUP BY `cyear`;

จุดสังเกตคือ

  1. ใช้ GROUP BY เพื่อรวมข้อมูลแต่ละที่สนใจให้อยู่แถวเดียวกัน ในที่นี้คือ ปี
  2. การแสดงผลหลัง select จะใช้รูปแบบ ,SUM (IF (ฟิลย์ที่เป็นเงื่อนไข (เดือน) = ค่าที่สนใจ (ลำดับของเดือน) , ฟิลย์ที่ต้องการแสดง (expense) ,ค่าเริ่มต้นถ้าไม่มีข้อมูล)) AS ชื่อใหม่
Read MoreRead More