我不喜歡其他答案，要求創(chuàng)建表格等等。這個(gè)查詢在沒有助手表的情況下有效地完成了它。
SELECT
IF(score IS NULL, 0, score) AS score,
b.Days AS date
FROM
(SELECT a.Days
FROM (
SELECT curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY AS Days
FROM (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS a
CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS b
CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS c
) a
WHERE a.Days >= curdate() - INTERVAL 30 DAY) b
LEFT JOIN your_table
ON date = b.Days
ORDER BY b.Days;
所以讓我們解剖一下這個(gè)。
SELECT
IF(score IS NULL, 0, score) AS score,
b.Days AS date
if將檢測沒有得分的天數(shù)，并將其設(shè)置為0。日期是您從當(dāng)前日期(最多1000天)選擇的所配置的天數(shù)。
(SELECT a.Days
FROM (
SELECT curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY AS Days
FROM (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS a
CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS b
CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS c
) a
WHERE a.Days >= curdate() - INTERVAL 30 DAY) b
這個(gè)子查詢是我在堆棧溢出上看到的。它有效地從當(dāng)前日期生成過去1000天的列表。末尾WHERE子句中的間隔(當(dāng)前為30)確定返回的日期；最大值為1000。此查詢可以很容易地修改為返回值為1000年的日期，但對于大多數(shù)情況來說，1000應(yīng)該是好的。
LEFT JOIN your_table
ON date = b.Days
ORDER BY b.Days;
這是把你的表中包含分?jǐn)?shù)的部分。將所選日期范圍與從日期生成器查詢到的選定日期范圍進(jìn)行比較，以便能夠在需要時(shí)填寫0(得分將設(shè)置為NULL最初，因?yàn)樗荓EFT JOIN這在SELECT語句中是固定的)。我也是按日期點(diǎn)的，只是因?yàn)?。這是偏好，你也可以按分?jǐn)?shù)訂購。
在ORDER BY您可以很容易地加入您的表格，有關(guān)您提到的用戶信息與您的編輯，以添加最后的要求。
我希望這個(gè)版本的查詢能幫助到一些人。謝謝你的閱讀。
