การใช้ตัวแปรใน mysql จะช่วยให้เขียน sql query ได้สั้นและเข้าใจได้ง่ายขึ้น อย่างในกรณีที่นำค่าจากการคำนวณมาค่าหนึ่ง แล้วต้องนำไปใช้แสดงผลใน column อื่นๆ อีกครั้ง เช่น การนำผลที่ได้มาแสดงเป็นช่วงแบบตามขั้นบันได แบ่งช่วงวันที่ออกเป็นกลุ่มๆ หรือการตัดเกรด
สมมุติว่า ต้องตัดเกรดให้เด็กตามช่วงคะแนน โดย
- accumulatedScore
- คะแนนเก็บ เต็ม 100 คะแนนจะมีน้ำหนักเป็น 50%
- midtermScore
- คะแนนกลางภาค เต็ม 100 คะแนนจะมีน้ำหนักเป็น 20%
- finalScore
- คะแนนสอบปลายภาค เต็ม 100 คะแนนจะมีน้ำหนัก 30%
- จะเขียนเป็น query ได้เป็น
1 2 3 | ((accumulatedScore / 100) * 50) +((midtermScore / 100) * 20) +((finalScore / 100) * 30) AS score |
เพื่อที่ทดลอง query ให้ตารางเก็บคะแนนขึ้นมาก่อน เช่น
schoolReport table
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 | -- phpMyAdmin SQL Dump-- version 4.7.0---- Host: 127.0.0.1-- Generation Time: Oct 12, 2017 at 07:46 PM-- Server version: 10.1.25-MariaDB-- PHP Version: 7.1.7 SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";SET AUTOCOMMIT = 0;START TRANSACTION;SET time_zone = "+00:00"; ---- Database: `snippets`-- -- -------------------------------------------------------- ---- Table structure for table `schoolReport`-- CREATE TABLE `schoolreport` ( `studen_id` int(11) NOT NULL, `course_id` int(11) NOT NULL, `accumulatedScore` int(11) NOT NULL, `midtermScore` int(11) NOT NULL, `finalScore` int(11) NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8; ---- Dumping data for table `schoolReport`-- INSERT INTO `schoolReport` (`studen_id`, `course_id`, `accumulatedScore`, `midtermScore`, `finalScore`) VALUES(1, 1, 55, 76, 74),(1, 2, 74, 74, 74),(1, 3, 43, 76, 75),(1, 4, 47, 45, 57),(1, 5, 71, 45, 72),(2, 1, 45, 85, 74),(2, 2, 65, 47, 47),(2, 3, 56, 85, 20),(2, 4, 37, 75, 42),(2, 5, 65, 35, 74); ---- Indexes for dumped tables-- ---- Indexes for table `schoolReport`--ALTER TABLE `schoolReport` ADD UNIQUE KEY `studen_id` (`studen_id`,`course_id`);COMMIT; |
จากสูดรที่ดูไม่ซับซ้อนแต่เมื่อเราต้องเขียนคิวรี่ให้ตัดเกรดออกมาพร้อม ๆ กันมันจะกลายเป็น
query score
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 | SELECT studen_id, course_id, accumulatedScore, midtermScore, finalScore, ((accumulatedScore / 100) * 50) +((midtermScore / 100) * 20) +((finalScore / 100) * 30) AS score, CASE WHEN ((accumulatedScore / 100) * 50) +((midtermScore / 100) * 20) +((finalScore / 100) * 30) < 50 THEN 'F' WHEN ((accumulatedScore / 100) * 50) +((midtermScore / 100) * 20) +((finalScore / 100) * 30) <= 60 THEN 'D' WHEN ((accumulatedScore / 100) * 50) +((midtermScore / 100) * 20) +((finalScore / 100) * 30) <= 70 THEN 'C' WHEN ((accumulatedScore / 100) * 50) +((midtermScore / 100) * 20) +((finalScore / 100) * 30) <= 80 THEN 'B' else 'A' END AS gradeFROM schoolReport |
จะเกิดอะไรขึ้นถ้าเขียน SQL ผิดไปนิดเดียว อาจจะลืมพิมพ์
query calculate grade
1 2 3 4 5 6 | WHEN ((accumulatedScore / 100) * 50) +((midtermScore / 100) * 20) +((midtermScore / 100) * 30) <= 70 THEN 'C' |
เห็นมั๋ย? แค่ใช้คะแนน midtermScore 2 ครั้ง ไปตอนตัดเกรดซักเกรดเอง
ถ้าเปลี่ยนไปใช้ query แบบใช้ตัวแปร
mysql variable query
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 | SELECT studen_id, course_id, accumulatedScore, midtermScore, finalScore, @score := ((accumulatedScore / 100) * 50) +((midtermScore / 100) * 20) +((finalScore / 100) * 30) AS score, CASE WHEN @score < 50 THEN 'F' WHEN @score <= 60 THEN 'D' WHEN @score <= 70 THEN 'C' WHEN @score <= 80 THEN 'B' else 'A' END AS gradeFROM schoolReport |
ดูเข้าใจง่ายขึ้นเยอะ โอกาสพลาดก็น้อยลง เขียนผิดก็ตัดเกรดผิดทุกเกรด (เห็นง่ายกว่า) แก้สูตรก็แก้จุดเดียว ชีวิตง่ายขึ้นเยอะ ^_^
สรุปการสร้างตัวแปร @ชื่อตัวแปร := (เขียน : ติดกับ = เสมอ) สูตร เพื่อความสวยงาม และใช้ง่ายอย่าลืมใส่ AS alias name ให้ด้วย