สร้างข้อมูลสำหรับสร้างกราฟรายปี Database for Chart

<DATABASE>-----------------------------------------------------------------------------------------------
  ตารางข้อมูลแสดงข้อมูลทุกปี

<CODE MYSQL >--------------------------------------------------------------------------------------------
 ต้องการแสดงผลข้อมูลของแต่ล่ะปี เพื่อนำไป Plot Graph โดยจะต้องแยกออกเป็นปี ๆ ที่ต้องการ

SELECT now_for.r_date, now_for.fore24 as now_year, l1_for.l1_fore24 as l1_year, l2_for.l2_fore24 as l2_year, l3_for.l3_fore24 as l3_year,
lmax_fore24.lmax_fore24 as max18_year, lmin_fore24.lmin_fore24 as min35_year, l_curve.uppers, l_curve.lowers
FROM
(
  SELECT
   sk24_forebay5y.r_date,
   sk24_forebay5y.fore24
  FROM
   sk24_forebay5y
  WHERE
      YEAR(sk24_forebay5y.r_date) = YEAR(CURDATE())) as now_for
RIGHT JOIN(
  (SELECT
    sk24_forebay5y.r_date,
    sk24_forebay5y.fore24 as l1_fore24
  FROM
    sk24_forebay5y
  WHERE
   YEAR(sk24_forebay5y.r_date) = YEAR(CURDATE())-1) as l1_for
 )ON(
  DayOfYear(now_for.r_date)= DayOfYear(l1_for.r_date)
 )
RIGHT JOIN(
  (SELECT
    sk24_forebay5y.r_date,
    sk24_forebay5y.fore24 as l2_fore24
  FROM
    sk24_forebay5y
  WHERE
   YEAR(sk24_forebay5y.r_date) = YEAR(CURDATE()) - 2) as l2_for
 )ON(
  DayOfYear(l1_for.r_date)= DayOfYear(l2_for.r_date)
 )
RIGHT JOIN(
  (SELECT
    sk24_forebay5y.r_date,
    sk24_forebay5y.fore24 as l3_fore24
  FROM
    sk24_forebay5y
  WHERE
   YEAR(sk24_forebay5y.r_date) = YEAR(CURDATE()) - 3) as l3_for
 )ON(
  DayOfYear(l2_for.r_date)= DayOfYear(l3_for.r_date)
 )
RIGHT JOIN(
  (SELECT
    sk24_forebay5y.r_date,
    sk24_forebay5y.fore24 as lmax_fore24
  FROM
    sk24_forebay5y
  WHERE
   YEAR(sk24_forebay5y.r_date) = 1975) as lmax_fore24
 )ON(
  DayOfYear(l3_for.r_date)= DayOfYear(lmax_fore24.r_date)
 )
RIGHT JOIN(
  (SELECT
    sk24_forebay5y.r_date,
    sk24_forebay5y.fore24 as lmin_fore24
  FROM
    sk24_forebay5y
  WHERE
   YEAR(sk24_forebay5y.r_date) = 1992) as lmin_fore24
 )ON(
  DayOfYear(l3_for.r_date)= DayOfYear(lmin_fore24.r_date)
 )
JOIN(
 SELECT  rulecurve.d_year,
         rulecurve.upper as uppers,
         rulecurve.lower as lowers
 FROM rulecurve ) as l_curve
    ON (l_curve.d_year =  DayOfYear(l3_for.r_date))
ORDER BY
  l3_for.r_date ASC


<RESULT MYSQL>---------------------------------------------------------------------------------------