ตัวอย่างข้อมูลใน 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];
}
ไม่มีความคิดเห็น:
แสดงความคิดเห็น