ข่วยเรื่อง การหาผลรวมข้อมูล ย้อนหลังหน่อยครับ มีDB และรูปภาพ ตัวอย่าง
Code -- phpMyAdmin SQL Dump
Tag PHP, MySQL, HTML/CSS, jQuery, CakePHP
Date :
2015-10-27 15:37:06By :
bondsantiView :
1253Reply :
15
อันนี้เลยครับ => PHP DateAdd Code (PHP) echo date('Y-m-d',strtotime("-1 week"));
Date :
2015-10-27 21:58:04By :
mr.win
หรือใช้ mysql statement คิวรี่โดยตรงเลยครับCode (PHP) $enDate = '2015-10-26';
$sql = 'select
sum( dte between DATE_SUB('$stDate', interval 6 day) and '$stDate' ) curweek,
sum( dte between DATE_SUB('$stDate', interval 13 day) and DATE_SUB('$stDate', interval 7 day)) retro1week,
sum( dte between DATE_SUB('$stDate', interval 20 day) and DATE_SUB('$stDate', interval 14 day)) retro2week,
sum( dte between DATE_SUB('$stDate', interval 27 day) and DATE_SUB('$stDate', interval 21 day)) retro3week
from table where dte >Date_Sub('$enDate', interval 28 day) ";
ประวัติการแก้ไข
Date :
2015-10-28 02:29:47By :
NewbiePHP
เด๋ี๋ยวจะลองทำดูนะครับ
Date :
2015-10-28 07:10:13By :
bondsanti
Date :
2015-10-28 10:42:39By :
mr.win
ได้แบบนี้แล้ว ข้อมูลก็ยังไม่ถูกอ่ะครับCode <?php
Code SELECT SUM(smu_cra) as sum_a, SUM(smu_crb) as sum_b, SUM(smu_crc) as sum_c, SUM(smu_crd) as sum_d, SUM(smu_crj) as sum_j FROM smu_cal WHERE date(smu_date)>='2015-10-21' AND date(smu_date)<='2015-10-27' SELECT SUM(smu_cra) as sum_a, SUM(smu_crb) as sum_b, SUM(smu_crc) as sum_c, SUM(smu_crd) as sum_d, SUM(smu_crj) as sum_j FROM smu_cal WHERE date(smu_date)>='2015-10-14' AND date(smu_date)<='2015-10-20' SELECT SUM(smu_cra) as sum_a, SUM(smu_crb) as sum_b, SUM(smu_crc) as sum_c, SUM(smu_crd) as sum_d, SUM(smu_crj) as sum_j FROM smu_cal WHERE date(smu_date)>='2015-10-07' AND date(smu_date)<='2015-10-13' SELECT SUM(smu_cra) as sum_a, SUM(smu_crb) as sum_b, SUM(smu_crc) as sum_c, SUM(smu_crd) as sum_d, SUM(smu_crj) as sum_j FROM smu_cal WHERE date(smu_date)>='2015-09-30' AND date(smu_date)<='2015-10-06'
Date :
2015-10-28 14:07:46By :
bondsanti
INSERT INTO `smu_cal` (`smu_id`, `smu_date`, `smu_cra`, `smu_crb`, `smu_crc`, `smu_crd`, `smu_crj`)Code (PHP) $enDate = '2015-10-26';
$sql = 'select
sum( dte between DATE_SUB('$stDate', interval 6 day) and '$stDate' ) curweek,
sum( dte between DATE_SUB('$stDate', interval 13 day) and DATE_SUB('$stDate', interval 7 day)) retro1week,
sum( dte between DATE_SUB('$stDate', interval 20 day) and DATE_SUB('$stDate', interval 14 day)) retro2week,
sum( dte between DATE_SUB('$stDate', interval 27 day) and DATE_SUB('$stDate', interval 21 day)) retro3week
from table where dte >Date_Sub('$enDate', interval 28 day) ";
Code (PHP) $date='2015-10-26';
$sql="
set @dte='$date';
select nm, st, en,
sum(smu_cra) cra, sum(smu_crb) crb, sum(smu_crc) crc, sum(smu_crd) crd ,sum(smu_crj) crj
from smu_cal tb1
inner join (
select 'curweek' nm, DATE_SUB(@dte, interval 6 day) st, @dte en union all
select 'retro1week', DATE_SUB(@dte, interval 13 day), DATE_SUB(@dte, interval 7 day) union all
select 'retro2week', DATE_SUB(@dte, interval 20 day), DATE_SUB(@dte, interval 14 day) union all
select 'retro3week', DATE_SUB(@dte, interval 27 day), DATE_SUB(@dte, interval 21 day)
) tb2
on tb1.smu_date between tb2.st and tb2.en
group by tb2.nm
";
Date :
2015-10-28 15:25:11By :
NewbiePHP
ลองเอาไปรัน ใน phpmyadmin ก่อน
Date :
2015-10-28 16:23:31By :
NewbiePHP
Code (SQL)
set @dte='2015-10-26';
select nm, st, en,
sum(smu_cra) cra, sum(smu_crb) crb, sum(smu_crc) crc, sum(smu_crd) crd ,sum(smu_crj) crj
from smu_cal tb1
inner join (
select 'curweek' nm, DATE_SUB(@dte, interval 6 day) st, @dte en union all
select 'retro1week', DATE_SUB(@dte, interval 13 day), DATE_SUB(@dte, interval 7 day) union all
select 'retro2week', DATE_SUB(@dte, interval 20 day), DATE_SUB(@dte, interval 14 day) union all
select 'retro3week', DATE_SUB(@dte, interval 27 day), DATE_SUB(@dte, interval 21 day)
) tb2
on tb1.smu_date between tb2.st and tb2.en
group by tb2.nm
Date :
2015-10-28 16:32:06By :
NewbiePHP
Server 02
Load balance : Server 02
