เขียน SQL ใน MYSQL ให้แยกวัน-เดือน-ปี แบบไทย

เขียน SQL ใน MYSQL ให้แยกวัน-เดือน-ปี แบบไทย
 
r_date มีค่าเป็น 2017-06-01

SQL ดังนี้

select `plantreal24`.`r_date` AS `r_date`,
       dayofmonth(`plantreal24`.`r_date`) AS `thaiday`,
       (case
  when (month (`plantreal24`.`r_date`) = 1) then _utf8 'ม.ค.'
  when (month (`plantreal24`.`r_date`) = 2) then _utf8 'ก.พ.'
  when (month (`plantreal24`.`r_date`) = 3) then _utf8 'มี.ค.'
  when (month (`plantreal24`.`r_date`) = 4) then _utf8 'เม.ย.'
  when (month (`plantreal24`.`r_date`) = 5) then _utf8 'พ.ค.'
  when (month (`plantreal24`.`r_date`) = 6) then _utf8 'มิ.ย.'
  when (month (`plantreal24`.`r_date`) = 7) then _utf8 'ก.ค.'
  when (month (`plantreal24`.`r_date`) = 8) then _utf8 'ส.ค.'
  when (month (`plantreal24`.`r_date`) = 9) then _utf8 'ก.ค.'
  when (month (`plantreal24`.`r_date`) = 10) then _utf8 'ต.ค.'
  when (month (`plantreal24`.`r_date`) = 11) then _utf8 'พ.ย.'
  when (month (`plantreal24`.`r_date`) = 12) then _utf8 'ธ.ค.'
end) AS `thaimonth`,
       (year (`plantreal24`.`r_date`) + 543) AS `thaiyear`,
       `plantreal24`.`fore24` AS `fore24`,
       `plantreal24`.`no` AS `no`
from `plantreal24`
order by `plantreal24`.`no`

การใช้ RIGHT JOIN เพื่อดึงข้อมูลออกในแต่ละปี

ตัวอย่างข้อมูลใน TABLE

เขียน SQL  RIGHT JOIN ดังนี้

SELECT TB2.thaimonth, TB2.thaiyear, TB2.sum0 as Datas, TB1.thaiyear, TB1.sum1 as Datas2
FROM
(SELECT 
  sk24_all_month.r_date,
  sk24_all_month.thaimonth,
  sk24_all_month.thaiyear,
  sk24_all_month.sum_inflow24 as sum1
FROM
  sk24_all_month
WHERE
  sk24_all_month.thaiyear = 2560 ) as TB1
RIGHT JOIN(
(SELECT 
  sk24_all_month.r_date,
  sk24_all_month.thaimonth,
  sk24_all_month.thaiyear,
  sk24_all_month.sum_inflow24 as sum0
FROM
  sk24_all_month
WHERE
  sk24_all_month.thaiyear = 2559 ) as TB2
)ON(
TB2.thaimonth = TB1.thaimonth
)
  







//----------------------Sample---------------------------------------

$SQL1 = "SELECT l_curve.d_year, 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, n_fore.n_fore, n_fore.r_date as n_date
FROM
(
SELECT 
all_sk_forebay5y.r_date,
all_sk_forebay5y.fore24
FROM
all_sk_forebay5y
WHERE
   YEAR(all_sk_forebay5y.r_date) = YEAR(CURDATE())) as now_for
RIGHT JOIN(
(SELECT
 all_sk_forebay5y.r_date,
 all_sk_forebay5y.fore24 as l1_fore24
FROM
 all_sk_forebay5y
WHERE
YEAR(all_sk_forebay5y.r_date) = YEAR(CURDATE())-1) as l1_for
)ON(
DayOfYear(now_for.r_date)= DayOfYear(l1_for.r_date)
)
RIGHT JOIN(
(SELECT
 all_sk_forebay5y.r_date,
 all_sk_forebay5y.fore24 as l2_fore24
FROM
 all_sk_forebay5y
WHERE
YEAR(all_sk_forebay5y.r_date) = YEAR(CURDATE()) - 2) as l2_for
)ON(
DayOfYear(l1_for.r_date)= DayOfYear(l2_for.r_date)
)
RIGHT JOIN(
(SELECT
 all_sk_forebay5y.r_date,
 all_sk_forebay5y.fore24 as l3_fore24
FROM
 all_sk_forebay5y
WHERE
YEAR(all_sk_forebay5y.r_date) = YEAR(CURDATE()) - 3) as l3_for
)ON(
DayOfYear(l1_for.r_date)= DayOfYear(l3_for.r_date)
)
RIGHT JOIN(
(SELECT
 all_sk_forebay5y.r_date,
 all_sk_forebay5y.fore24 as lmax_fore24
FROM
 all_sk_forebay5y
WHERE
YEAR(all_sk_forebay5y.r_date) = 1975) as lmax_fore24
)ON(
DayOfYear(l1_for.r_date)= DayOfYear(lmax_fore24.r_date)
)
RIGHT JOIN(
(SELECT
 all_sk_forebay5y.r_date,
 all_sk_forebay5y.fore24 as lmin_fore24
FROM
 all_sk_forebay5y
WHERE
YEAR(all_sk_forebay5y.r_date) = 1992) as lmin_fore24
)ON(
DayOfYear(l1_for.r_date)= DayOfYear(lmin_fore24.r_date)
)
JOIN(
SELECT ruledata_2555.d_year,
        ruledata_2555.upper as uppers,
        ruledata_2555.lower as lowers
FROM ruledata_2555
    WHERE ruledata_2555.dam = 'SK' ) as l_curve
    ON (l_curve.d_year =  DayOfYear(l1_for.r_date)) 
JOIN(
SELECT 
  all_sk_forebay5y.r_date,
  all_sk_forebay5y.fore24 as n_fore
FROM
  all_sk_forebay5y
ORDER BY
  all_sk_forebay5y.r_date DESC
LIMIT 1) as n_fore
ORDER BY l3_for.r_date ASC";
mysql_connect("localhost", "root", "adminxxx");
mysql_select_db("xxx");
$result1 = mysql_query($SQL1);
$a =0;
while ($row1= mysql_fetch_row($result1)) { //ดึงข้อมูลไปใช้งาน
$mydate[]  = $row1[0];
if(!$row1[1]==null){$fore24[] = $row1[1];}
//$fore24[]  = if($row1[1] == null){$row1[1] =0} else {$row1[1] =$row1[1]};
$l1_fore24[]  = $row1[2];
$l2_fore24[]  = $row1[3];
$l3_fore24[]  = $row1[4];
$max18_year[]  = $row1[5];
$min35_year[]  = $row1[6];
$uppers[]  = $row1[7];
$lowers[]  = $row1[8];
$m18 = max($max18_year); //หาข้อมูลใน Array ที่มีค่ามากที่สุด
$m35 = min($min35_year);//หาข้อมูลใน Array ที่มีค่าน้อยที่สุด
$n_fore = $row1[9];
$n_date = $row1[10];
}

การหาผลรวม Total ของข้อมูลใน Array ที่รับมาจาก Database

$SQL1 = "Select * FROM xxx";
mysql_connect("localhost", "root", "admin");
mysql_select_db("xxx");
$result1 = mysql_query($SQL1);
$a =0;
while ($row1= mysql_fetch_row($result1)) {
$mydate[]  = $row1[0];
if(!$row1[1]==null){$fore24[] = $row1[1];} //เช็ค Record ว่างหรือไม่
$l1_fore24[]  = $row1[2];
$l2_fore24[]  = $row1[3];
$l3_fore24[]  = $row1[4];
$l4_fore24[]  = $row1[5];
}


   $myc = count($l1_fore24);  // หาผลรวมของข้อมูลว่ามีจำนวนกี่ Record

   for($m=0;$m<=$myc;$m++)  
    {
$fore24_0 = ($fore24[$m])+$fore24_0;   //หาผลรวมของ Column แรก
$fore24_1 = ($l1_fore24[$m])+$fore24_1;
$fore24_2 = ($l2_fore24[$m])+$fore24_2;
$fore24_3 = ($l3_fore24[$m])+$fore24_3;
$fore24_4 = ($l4_fore24[$m])+$fore24_4;
}

วัน เวลา ใน PHP

กำหนดให้แสดงปีแบบไทย ใน PHP
$y_now = ROUND(date("y", mktime(date("Y"))));
$nows = $y_now + 43;  //แสดง 60
$nows1 = $y_now + 42; //แสดง 59
$nows2 = $y_now + 41; //แสดง 58
$nows3 = $y_now + 40;
$nows4 = $y_now + 39;
$nows5 = $y_now + 38;


เขียน Function แสดงวันเดือนปี แบบไทย

$thai_month_short=array(  
    "0"=>"",  
    "1"=>"ม.ค.",  
    "2"=>"ก.พ.",  
    "3"=>"มี.ค.",  
    "4"=>"เม.ย.",  
    "5"=>"พ.ค.",  
    "6"=>"มิ.ย.",  
    "7"=>"ก.ค.",  
    "8"=>"ส.ค.",  
    "9"=>"ก.ย.",  
    "10"=>"ต.ค.",  
    "11"=>"พ.ย.",  
    "12"=>"ธ.ค."                  
);  
$thai_month=array(
    "0"=>"",
    "1"=>"มกราคม",
    "2"=>"กุมภาพันธ์",
    "3"=>"มีนาคม",
    "4"=>"เมษายน",
    "5"=>"พฤษภาคม",
    "6"=>"มิถุนายน",
    "7"=>"กรกฎาคม",
    "8"=>"สิงหาคม",
    "9"=>"กันยายน",
    "10"=>"ตุลาคม",
    "11"=>"พฤศจิกายน",
    "12"=>"ธันวาคม"                
);
function thai_date($time){   //แสดง 25 มิ.ย. 2560
    global $thai_month_short;  
    $thai_date_return = date("j",$time);   // - 1  เพื่อให้แสดงข้อมูลเมื่อวาน
    $thai_date_return.=" ".$thai_month_short[date("n",$time)];  
    $thai_date_return.= " ".(date("Y",$time)+543);  
    return $thai_date_return;  
}

function thai_fdate($time){    //แสดงแบบ 25 มิถุนายน 2560
    global $thai_month;  
    $thai_date_return = date("j",$time);    // - 1  เพื่อให้แสดงข้อมูลเมื่อวาน
    $thai_date_return.=" ".$thai_month[date("n",$time)];  
    $thai_date_return.= " ".(date("Y",$time)+543);  
    return $thai_date_return;  
}

$mydt = time();

นำไปใช้งาน

echo thai_date($mydt)  //แสดง 25 มิ.ย. 2560

echo thai_fdate($mydt) //แสดงแบบ 25 มิถุนายน 2560

สร้างข้อมูลสำหรับสร้างกราฟรายปี 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>---------------------------------------------------------------------------------------