选择sql和sum的日期

4bbkushb 于 2021-06-20 发布在 Mysql

关注(0)|答案(2)|浏览(330)

很好的一天。我选择的日期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();
?>

有没有查询可以选择一周中全天的总和？或者我应该保留这个密码？

sql mysql php pdo

来源：https://stackoverflow.com/questions/50654427/selecting-dates-to-sql-and-sum

2条答案

按热度按时间

ie3xauqp1#

如果您必须通过日期，请执行以下操作：

SELECT DATE(pay_date) `DATE`, SUM(amount) daily_amount
FROM tbl_paid 
WHERE DATE(pay_date) BETWEEN DATE(:today) - INTERVAL 1 WEEK AND DATE(:today)
GROUP BY DATE(pay_date);

否则使用数据库的当前日期。

SELECT DATE(pay_date) `DATE`, SUM(amount) daily_amount
FROM tbl_paid 
WHERE DATE(pay_date)>=CURRENT_DATE - INTERVAL 1 WEEK
GROUP BY DATE(pay_date);

您不需要运行查询七次。记得；不要重复（干的）。

赞(0）回复(0）举报 2021-06-20

f2uvfpb92#

试试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演示链接
如果你想知道更多的信息
你可以用 `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演示链接

赞(0）回复(0）举报 2021-06-20

我来回答

选择sql和sum的日期

2条答案

相关问题

热门标签

最新问答