MySQL: ใช้ตัวแปร variable

Byphunsanit

Calculate, MySQL, query, variable, คำนวณ, คิวรี, ตัวแปร

การใช้ตัวแปรใน 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 ให้ด้วย

MySQL: ใช้ตัวแปร variable