การใช้ตัวแปรใน mysql จะช่วยให้เขียน sql query ได้สั้นและเข้าใจได้ง่ายขึ้น อย่างในกรณีที่นำค่าจากการคำนวณมาค่าหนึ่ง แล้วต้องนำไปใช้แสดงผลใน column อื่น ๆ อีกครั้ง เช่น การนำผลที่ได้มาแสดงเป็นช่วงแบบตามขั้นบันได แบ่งช่วงวันที่ออกเป็นกลุ่ม ๆ หรือการตัดเกรด
สมมุติว่า ต้องตัดเกรดให้เด็กตามช่วงคะแนน โดย
- accumulatedScore
- คะแนนเก็บ เต็ม 100 คะแนนจะมีน้ำหนักเป็น 50%
- midtermScore
- คะแนนกลางภาค เต็ม 100 คะแนนจะมีน้ำหนักเป็น 20%
- finalScore
- คะแนนสอบปลายภาค เต็ม 100 คะแนนจะมีน้ำหนัก 30%
- จะเขียนเป็น query ได้เป็น
( (accumulatedScore / 100) * 50) + ( (midtermScore / 100) * 20) + ( (finalScore / 100) * 30) AS score
เพื่อที่ทดลอง query ให้ตารางเก็บคะแนนขึ้นมาก่อน เช่น
schoolReport table
-- phpMyAdmin SQL Dump
-- version 4.7.0
-- https://www.phpmyadmin.net/
--
-- 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
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 grade
FROM schoolReport
จะเกิดอะไรขึ้นถ้าเขียน SQL ผิดไปนิดเดียว อาจจะลืมพิมพ์
query calculate grade
WHEN ( (accumulatedScore / 100) * 50) + ( (midtermScore / 100) * 20) + ( (midtermScore / 100) * 30) <= 70 THEN 'C'
เห็นมั๋ย? แค่ใช้คะแนน midtermScore 2 ครั้ง ไปตอนตัดเกรดซักเกรดเอง
ถ้าเปลี่ยนไปใช้ query แบบใช้ตัวแปร
mysql variable query
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 grade
FROM schoolReport
ดูเข้าใจง่ายขึ้นเยอะ โอกาสพลาดก็น้อยลง เขียนผิดก็ตัดเกรดผิดทุกเกรด (เห็นง่ายกว่า) แก้สูตรก็แก้จุดเดียว ชีวิตง่ายขึ้นเยอะ ^_^
สรุปการสร้างตัวแปร @ชื่อตัวแปร := (เขียน : ติดกับ = เสมอ) สูตร เพื่อความสวยงาม และใช้ง่ายอย่าลืมใส่ AS alias name ให้ด้วย