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

Question

我试图创建一个图表，我将从我的分数表中显示数据，该数据必须从每个月的总分数。在高级sql查询方面，我是一个新手。我已经尝试了以下一些例子，但仍然没有得到它。

下面是分数表

---------------------------------
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个月的分数和那些没有分数的月份的零。

#编辑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是否会给我一个数组，这样我就可以通过在Chart.js数据值脚本中做get_last_12_month_scores()[i]来捕获数组中的单个值。但遗憾的是，它没有

就在这时，我意识到我要么不知道自己在做什么，要么只能寻求帮助以避免浪费时间。

Answer 1

这是一个轨道:

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)

Answer 2

我的解决方案不为空月份添加值，这可以在php中呈现输出时处理，但是区分了年份。否则，要获得丢失的月份，您可以创建一个额外的表来交叉引用，参见这个线程:

SELECT YEAR(date_created), MONTH(date_created), sum( mark )
FROM scores
GROUP BY YEAR(date_created), MONTH(date_created)

或者选择具体年份:

SELECT MONTH(date_created), sum(mark)
FROM scores
WHERE YEAR(date_created) = 2020 -- target year
GROUP BY MONTH(date_created)

拨弄这里:http://sqlfiddle.com/#!9/f5f62e/1

-- create a table
CREATE TABLE scores (
id INTEGER PRIMARY KEY,
mark INTEGER NOT NULL,
date_created date NOT NULL
);
-- insert some values
INSERT INTO scores VALUES (1, '100', '2020-03-12');
INSERT INTO scores VALUES (2, '90', '2020-08-25');
INSERT INTO scores VALUES (3, '100', '2021-04-10');
INSERT INTO scores VALUES (4, '95', '2021-06-20');
INSERT INTO scores VALUES (5, '99', '2021-10-02');
INSERT INTO scores VALUES (6, '99', '2021-10-05');