时间戳

首页 > 解决方案 > SQL 查询计算并显示过去 12 个月的每个月的总和数据

mysql - SQL 查询计算并显示过去 12 个月的每个月的总和数据

问题描述

我正在尝试创建一个图表，我将在其中显示我的分数表中的数据，并且该数据必须是每个月的总分数。在高级 sql 查询方面，我是新手。我已经尝试过一些关于 SO 的示例，但还没有得到它。

下面是成绩表

---------------------------------
id     | marks  | date_created   
---------------------------------
1      |   100  |  2020-03-12
2      |   90   |  2020-08-25
3      |   100  |  2021-04-10
4      |   95   |  2021-06-20
5      |   99   |  2021-10-02
6      |   97   |  2021-10-02

我想要做的是确保当我显示图表时，它将显示过去 12 个月的分数以及那些月份没有分数的零。

#Edit 3 下表只是我想要实现的可视化。我还没有在数据库中。我只有分数表

---------------------------------
month        |   sum(marks)
---------------------------------
October      |   0
September    |   0
August       |   0
July         |   0
June         |   95
May          |   0
April        |   100
March        |   100
February     |   0
January      |   0
December     |   0
November     |   0

正如您在表格中看到的那样，我希望最近一个月排在最后我如何实现这一点？谢谢

Edit #1

不同分数的分数是各个月份总天数的总和。正如您在分数表上看到的那样，一天有 2 个分数条目。在我的总数中，我希望这些分数都在 10 月份计算。

Edit #2


function get_last_12_month_scores(){
    $db = Database::getInstance();
    $mysqli = $db->getConnection();
    $user_id = $_SESSION['user_id'];

    $query_count = "SELECT SUM(`marks`) as `total` FROM `scores` WHERE `user_id`='$user_id';

    $month_12_query_count =
        "SELECT
            SUM(IF(month = 'Jan', total, 0)) as 'Jan',
            SUM(IF(month = 'Feb', total, 0)) as 'Feb',
            SUM(IF(month = 'Mar', total, 0)) as 'Mar',
            SUM(IF(month = 'Apr', total, 0)) as 'Apr',
            SUM(IF(month = 'May', total, 0)) as 'May',
            SUM(IF(month = 'Jun', total, 0)) as 'Jun',
            SUM(IF(month = 'Jul', total, 0)) as 'Jul',
            SUM(IF(month = 'Aug', total, 0)) as 'Aug',
            SUM(IF(month = 'Sep', total, 0)) as 'Sep',
            SUM(IF(month = 'Oct', total, 0)) as 'Oct',
            SUM(IF(month = 'Nov', total, 0)) as 'Nov',
            SUM(IF(month = 'Dec', total, 0)) as 'Dec',
            SUM(total) as total_yearly
        FROM (
            SELECT DATE_FORMAT(date_added, '%b') as month, SUM($query_count as total
            FROM scores
            WHERE date_added <= now() and date >= Date_add(now(),interval - 12 month)
            GROUP BY DATE_FORMAT(date_added, '%m-%Y'))) as sub";

    $query_result = $mysqli->query($month_12_query_count);

    echo $query_result;
}

随着echo $query_result;我回显，看看是否$query_result会给我一个数组，这样我就可以通过get_last_12_month_scores()[i]在 Chart.js 数据值脚本中执行来捕获数组中的各个值。但不幸的是并没有

正是在这一点上，我意识到我要么不知道自己在做什么，要么不得不寻求帮助以避免浪费时间。

标签： mysqlsql

解决方案

这是一个轨道：

SELECT
 sum(case when month(date_created) = 10 then marks else 0 end) October,
 sum(case when month(date_created) = 9 then marks else 0 end) September,
 sum(case when month(date_created) = 8 then marks else 0 end) August,
 sum(case when month(date_created) = 7 then marks else 0 end) July,
 sum(case when month(date_created) = 6 then marks else 0 end) June,
 sum(case when month(date_created) = 5 then marks else 0 end) May,
 sum(case when month(date_created) = 4 then marks else 0 end) April,
 sum(case when month(date_created) = 3 then marks else 0 end) March,
 sum(case when month(date_created) = 2 then marks else 0 end) February,
 sum(case when month(date_created) = 1 then marks else 0 end) January,
 sum(case when month(date_created) = 12 then marks else 0 end) December,
 sum(case when month(date_created) = 11 then marks else 0 end) November
FROM scores 
GROUP BY month(date_created)

推荐阅读