擅长:python、mysql、java
<p>这很难用纯SQL高效地完成,但是您可以使用MySQL特定的用户定义变量。您只需要计算每天的计数,并累积每行的计数。你知道吗</p>
<p>主要的技巧是强制MySQL按正确的顺序更新变量。你知道吗</p>
<p>查询应如下所示(不确定是否100%正确):</p>
<pre><code>SELECT m_date, daily_count, @count:=(daily_count + @count) as member_totals
FROM
(SELECT DATE(Created_At) as m_date, COUNT(*) as daily_count
FROM members_joined
WHERE DATE(Created_At) >= CURDATE() - INTERVAL 30 DAY AND has_verified = 1
GROUP BY m_date
) as days,
(SELECT @count:=COUNT(*) as cnt0
FROM members_joined
WHERE DATE(Created_At) < CURDATE() - INTERVAL 30 DAY
AND has_verified = 1) as init
ORDER BY m_date;
</code></pre>