กลับแถวตาราง (Crosstab)


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

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 ชื่อใหม่

About plusmagic

PHP lover in thailand

Posted on 2012/04/11, in MySql and tagged , , , , , . Bookmark the permalink. ใส่ความเห็น.

ใส่ความเห็น

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / เปลี่ยนแปลง )

Twitter picture

You are commenting using your Twitter account. Log Out / เปลี่ยนแปลง )

Facebook photo

You are commenting using your Facebook account. Log Out / เปลี่ยนแปลง )

Google+ photo

You are commenting using your Google+ account. Log Out / เปลี่ยนแปลง )

Connecting to %s

%d bloggers like this: