คลังเก็บบล็อก

หาอินเด็กซ์ที่หายไป


ถ้าต้องการหาอินเด็กซ์  ที่หายไปในตารางที่มีอินเด็กซ์แบบ auto_increment ไว้หรือคีย์มีค่าแบบเรียงลำดับ 1,2,3,4…

index Alpha
1 a
2 b
4 c
5 e
6 f

ทำได้โดยเอาตารางมาจอยกับตัวเองโดยกำหนดให้ index น้อยกว่าอีกฝั่งอยู่ 1 ค่าแล้วหาว่าฝั่งขวามีข้อมูลรึเปล่า

SELECT *
FROM testTable  AS a
  LEFT JOIN testTable  AS b
    ON a.id = b.key_column - 1
WHERE b.key_column IS NULL;

กลับแถวตาราง (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 ชื่อใหม่
%d bloggers like this: