php - 选择要进行 sql 和 Sum 的日期

Question

美好的一天。我正在选择 MYSql 的日期，我一个一个地选择它。我想选择所有一周。我过去常常单独选择它。

这是我的代码:

<?php
$chart6 = $controller->runQuery("SELECT SUM(amount) FROM tbl_paid WHERE pay_date >= :today - INTERVAL 6 DAY"); 
$chart6->execute(array(":today"=>$today));
$chartData6 = $chart6->fetchColumn();

$chart5 = $controller->runQuery("SELECT SUM(amount) FROM tbl_paid WHERE pay_date >= :today - INTERVAL 5 DAY"); 
$chart5->execute(array(":today"=>$today));
$chartData5 = $chart5->fetchColumn();

$chart4 = $controller->runQuery("SELECT SUM(amount) FROM tbl_paid WHERE pay_date >= :today - INTERVAL 4 DAY"); 
$chart4->execute(array(":today"=>$today));
$chartData4 = $chart4->fetchColumn();

$chart3 = $controller->runQuery("SELECT SUM(amount) FROM tbl_paid WHERE pay_date >= :today - INTERVAL 3 DAY"); 
$chart3->execute(array(":today"=>$today));
$chartData3 = $chart3->fetchColumn();

$chart2 = $controller->runQuery("SELECT SUM(amount) FROM tbl_paid WHERE pay_date >= :today - INTERVAL 2 DAY"); 
$chart2->execute(array(":today"=>$today));
$chartData2 = $chart2->fetchColumn();

$chart1 = $controller->runQuery("SELECT SUM(amount) FROM tbl_paid WHERE pay_date >= :today - INTERVAL 1 DAY"); 
$chart1->execute(array(":today"=>$today));
$chartData1 = $chart1->fetchColumn();

$chart0 = $controller->runQuery("SELECT SUM(amount) FROM tbl_paid WHERE pay_date >= :today"); 
$chart0->execute(array(":today"=>$today));
$chartData0 = $chart0->fetchColumn();
?>

是否有查询可以选择一周中整天的总和？还是我应该保留此代码？

Answer 1

试试 sql 使用 group

SELECT Day(pay_date) pay_day,SUM(amount) amount 
FROM tbl_paid WHERE pay_date >= NOW() - INTERVAL 6 DAY 
GROUP BY Day(pay_date)

结果:

| pay_day | amount |
|---------|--------|
|       1 |    246 |
|       2 |    200 |
|      28 |   1702 |
|      29 |   1462 |
|      30 |    864 |
|      31 |   1092 |

---

测试数据:

| amount |             pay_date |
|--------|----------------------|
|    100 | 2018-06-02T00:00:00Z |
|    123 | 2018-06-01T00:00:00Z |
|    546 | 2018-05-31T00:00:00Z |
|    432 | 2018-05-30T00:00:00Z |
|    731 | 2018-05-29T00:00:00Z |
|    851 | 2018-05-28T00:00:00Z |
|    100 | 2018-06-02T00:00:00Z |
|    123 | 2018-06-01T00:00:00Z |
|    546 | 2018-05-31T00:00:00Z |
|    432 | 2018-05-30T00:00:00Z |
|    731 | 2018-05-29T00:00:00Z |
|    851 | 2018-05-28T00:00:00Z |
|    436 | 2018-05-27T00:00:00Z |

SQL Fiddle Demo Link

---

如果你想了解更多的话

您可以使用 weekday() 和 DAYNAME()

SELECT WEEKDAY(pay_date) WEEKDAY,DAYNAME(pay_date) DAYNAME,SUM(amount) amount 
FROM tbl_paid WHERE pay_date >= NOW() - INTERVAL 7 DAY 
GROUP BY WEEKDAY(pay_date),DAYNAME(pay_date)

---

结果:

| WEEKDAY |   DAYNAME | amount |
|---------|-----------|--------|
|       0 |    Monday |   1702 |
|       1 |   Tuesday |   1462 |
|       2 | Wednesday |    864 |
|       3 |  Thursday |   1092 |
|       4 |    Friday |    246 |
|       5 |  Saturday |    200 |
|       6 |    Sunday |   1287 |

SQL Fiddle Demo Link