Tag Archive Calculate

การใช้ตัวแปรใน 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 ให้ตารางเก็บคะแนนขึ้นมาก่อนเช่น

-- 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;

จากสูดรที่ดูไม่ซับซ้อนแต่เมื่อเราต้องเขียนคิวรี่ให้ตัดเกรดออกมาพร้อมๆกันมันจะกลายเป็น

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 ผิดไปนิดเดียว อาจจะลืมพิมพ์

      WHEN
         ((accumulatedScore / 100) * 50) +
((midtermScore / 100) * 20) +
((midtermScore / 100) * 30) <= 70
      THEN
         'C'

เห็นมั๋ย? แค่ใช้คะแนน midtermScore 2 ครั้ง ไปตอนตัดเกรดซักเกรดเอง

ถ้าเปลี่ยนไปใช้ 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 ให้ด้วย