問題描述
我有一個包含 2 列、日期和分數的表格.它最多有 30 個條目，對于過去 30 天的每一天.
I have a table with 2 columns, date and score. It has at most 30 entries, for each of the last 30 days one.
date score
-----------------
1.8.2010 19
2.8.2010 21
4.8.2010 14
7.8.2010 10
10.8.2010 14
我的問題是缺少某些日期 - 我想看看:
My problem is that some dates are missing - I want to see:
date score
-----------------
1.8.2010 19
2.8.2010 21
3.8.2010 0
4.8.2010 14
5.8.2010 0
6.8.2010 0
7.8.2010 10
...
我需要從單個查詢中得到:19,21,9,14,0,0,10,0,0,14...這意味著缺少的日期用 0 填充.
What I need from the single query is to get: 19,21,9,14,0,0,10,0,0,14... That means that the missing dates are filled with 0.
我知道如何獲取所有值并使用服務器端語言迭代日期并遺漏空格.但這是否可以在 mysql 中進行，以便我按日期對結果進行排序并獲取丟失的部分.
I know how to get all the values and in server side language iterating through dates and missing the blanks. But is this possible to do in mysql, so that I sort the result by date and get the missing pieces.
在這個表中還有一個名為 UserID 的列，所以我有 30.000 個用戶，其中一些在這個表中有分數.如果日期<，我每天都會刪除日期30 天前，因為我需要每個用戶最近 30 天的分數.原因是我正在制作過去 30 天的用戶活動圖表，并繪制圖表我需要用逗號分隔的 30 個值.所以我可以說在查詢中讓我獲得 USERID=10203 活動，查詢將獲得 30 個分數，過去 30 天的每一天都有一個分數.我希望我現在更清楚了.
In this table there is another column named UserID, so I have 30.000 users and some of them have the score in this table. I delete the dates every day if date < 30 days ago because I need last 30 days score for each user. The reason is I am making a graph of the user activity over the last 30 days and to plot a chart I need the 30 values separated by comma. So I can say in query get me the USERID=10203 activity and the query would get me the 30 scores, one for each of the last 30 days. I hope I am more clear now.
推薦答案
MySQL 沒有遞歸功能，因此您只能使用 NUMBERS 表技巧 -
MySQL doesn't have recursive functionality, so you're left with using the NUMBERS table trick -
創建一個只保存遞增數字的表 - 使用 auto_increment 很容易做到:
Create a table that only holds incrementing numbers - easy to do using an auto_increment:
DROP TABLE IF EXISTS `example`.`numbers`;
CREATE TABLE `example`.`numbers` (
`id` int(10) unsigned NOT NULL auto_increment,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
使用以下方法填充表格:
Populate the table using:
INSERT INTO `example`.`numbers`
( `id` )
VALUES
( NULL )
...根據您的需要獲取盡可能多的值.
...for as many values as you need.
使用DATE_ADD 構建日期列表，根據 NUMBERS.id 值增加天數.用您各自的開始和結束日期替換2010-06-06"和2010-06-14"(但使用相同的格式，YYYY-MM-DD)-
Use DATE_ADD to construct a list of dates, increasing the days based on the NUMBERS.id value. Replace "2010-06-06" and "2010-06-14" with your respective start and end dates (but use the same format, YYYY-MM-DD) -
SELECT `x`.*
FROM (SELECT DATE_ADD('2010-06-06', INTERVAL `n`.`id` - 1 DAY)
FROM `numbers` `n`
WHERE DATE_ADD('2010-06-06', INTERVAL `n`.`id` -1 DAY) <= '2010-06-14' ) x
LEFT JOIN 根據時間部分加入您的數據表:
LEFT JOIN onto your table of data based on the time portion:
SELECT `x`.`ts` AS `timestamp`,
COALESCE(`y`.`score`, 0) AS `cnt`
FROM (SELECT DATE_FORMAT(DATE_ADD('2010-06-06', INTERVAL `n`.`id` - 1 DAY), '%m/%d/%Y') AS `ts`
FROM `numbers` `n`
WHERE DATE_ADD('2010-06-06', INTERVAL `n`.`id` - 1 DAY) <= '2010-06-14') x
LEFT JOIN TABLE `y` ON STR_TO_DATE(`y`.`date`, '%d.%m.%Y') = `x`.`ts`
如果要保持日期格式，請使用 DATE_FORMAT 函數:
If you want to maintain the date format, use the DATE_FORMAT function:
DATE_FORMAT(`x`.`ts`, '%d.%m.%Y') AS `timestamp`
這篇關于MySQL如何填充范圍內缺少的日期?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！