 |
|
ตัวอย่างนะครับ
ตารางนี้ จะมี 3 field
equipment_code(var) // date(datetime) ///time(int)
A // 2016-08-01 00:00:00 // 45
A // 2016-03-01 00:00:00 // 60
B // 2016-01-01 00:00:00 // 80
B // 2016-07-01 00:00:00 // 70
C // 2016-06-01 00:00:00 // 30
ผมต้องการรายงานของผลรวมเวลาทั้งปี ดูในแถวเดียวกัน
Code (PHP)
$sql = "SELECT
equipment_code,
BINARY SUM((case DATE_FORMAT(date,'%m') when '01' time time else NULL end)) AS month_01,
BINARY SUM((case DATE_FORMAT(date,'%m') when '02' then time else NULL end)) AS month_02,
BINARY SUM((case DATE_FORMAT(date,'%m') when '03' then time else NULL end)) AS month_03,
BINARY SUM((case DATE_FORMAT(date,'%m') when '04' then time else NULL end)) AS month_04,
BINARY SUM((case DATE_FORMAT(date,'%m') when '05' then time else NULL end)) AS month_05,
BINARY SUM((case DATE_FORMAT(date,'%m') when '06' then time else NULL end)) AS month_06,
BINARY SUM((case DATE_FORMAT(date,'%m') when '07' then time else NULL end)) AS month_07,
BINARY SUM((case DATE_FORMAT(date,'%m') when '08' then time else NULL end)) AS month_08,
BINARY SUM((case DATE_FORMAT(date,'%m') when '09' then time else NULL end)) AS month_09,
BINARY SUM((case DATE_FORMAT(date,'%m') when '10' then time else NULL end)) AS month_10,
BINARY SUM((case DATE_FORMAT(date,'%m') when '11' then time else NULL end)) AS month_11,
BINARY SUM((case DATE_FORMAT(date,'%m') when '12' then time else NULL end)) AS month_12
FROM time_machine GROUP BY equipment_code";
จาก Query นี้ ผมก็จะได้
equipment_code // month_01// month_02//month_03//month_04//month_05//month_06//month_07//month_08//month_09//month_10//month_11//month_12
A // Null // Null // 60 // Null // Null // Null // Null // 45 // Null // Null // Null // Null
B // 80 // Null // Null // Null // Null // Null // 70 // Null // Null // Null // Null // Null
C // Null // Null // Null // Null // Null // 60 // Null // Null // Null // Null // Null // Null
Tag : PHP, MySQL
|
|
|
 |
 |
 |
 |
| Date :
2016-09-15 14:43:24 |
By :
thesin18598 |
View :
796 |
Reply :
2 |
|
 |
 |
 |
 |
|
|
|
|
 |
Load balance : Server 01
