文章目录
1 查询前7天的日期
select a.click_date
from (
SELECT date_sub(curdate(), interval 1 day) as click_date
union all
SELECT date_sub(curdate(), interval 2 day) as click_date
union all
SELECT date_sub(curdate(), interval 3 day) as click_date
union all
SELECT date_sub(curdate(), interval 4 day) as click_date
union all
SELECT date_sub(curdate(), interval 5 day) as click_date
union all
SELECT date_sub(curdate(), interval 6 day) as click_date
union all
SELECT date_sub(curdate(), interval 7 day) as click_date
) a
查询结果
2 查询订单表出现日期，成交额总和
没有出现的日期，不会被查出来
select DATE_FORMAT(o_id,"%Y-%m-%d") as date,sum(o_turnover)*0.15 u_commission from orders
where o_id between "2021-05-26" and "2021-06-02" and uid = 2
group by date;
3 根据日期统计数据
select DATE_FORMAT(date,"%Y-%m-%d") day,count(*) num from log group by day;
参考：
https://blog.csdn.net/weixin_38414968/article/details/108164479
<p><h3>文章目录</h3><ul><ul><li><a href="#1_7_2">1 查询前7天的日期</a></li><li><a href="#2__26">2 查询订单表出现日期，成交额总和</a></li><li><a href="#3__37">3 根据日期统计数据</a></li></ul></ul></p>
<h2><a id="1_7_2"></a>1 查询前7天的日期</h2>
<pre><div class="hljs"><code class="lang-sql"><span class="hljs-keyword">select</span> a.click_date
<span class="hljs-keyword">from</span> (
<span class="hljs-keyword">SELECT</span> date_sub(curdate(), <span class="hljs-type">interval</span> <span class="hljs-number">1</span> <span class="hljs-keyword">day</span>) <span class="hljs-keyword">as</span> click_date
<span class="hljs-keyword">union</span> <span class="hljs-keyword">all</span>
<span class="hljs-keyword">SELECT</span> date_sub(curdate(), <span class="hljs-type">interval</span> <span class="hljs-number">2</span> <span class="hljs-keyword">day</span>) <span class="hljs-keyword">as</span> click_date
<span class="hljs-keyword">union</span> <span class="hljs-keyword">all</span>
<span class="hljs-keyword">SELECT</span> date_sub(curdate(), <span class="hljs-type">interval</span> <span class="hljs-number">3</span> <span class="hljs-keyword">day</span>) <span class="hljs-keyword">as</span> click_date
<span class="hljs-keyword">union</span> <span class="hljs-keyword">all</span>
<span class="hljs-keyword">SELECT</span> date_sub(curdate(), <span class="hljs-type">interval</span> <span class="hljs-number">4</span> <span class="hljs-keyword">day</span>) <span class="hljs-keyword">as</span> click_date
<span class="hljs-keyword">union</span> <span class="hljs-keyword">all</span>
<span class="hljs-keyword">SELECT</span> date_sub(curdate(), <span class="hljs-type">interval</span> <span class="hljs-number">5</span> <span class="hljs-keyword">day</span>) <span class="hljs-keyword">as</span> click_date
<span class="hljs-keyword">union</span> <span class="hljs-keyword">all</span>
<span class="hljs-keyword">SELECT</span> date_sub(curdate(), <span class="hljs-type">interval</span> <span class="hljs-number">6</span> <span class="hljs-keyword">day</span>) <span class="hljs-keyword">as</span> click_date
<span class="hljs-keyword">union</span> <span class="hljs-keyword">all</span>
<span class="hljs-keyword">SELECT</span> date_sub(curdate(), <span class="hljs-type">interval</span> <span class="hljs-number">7</span> <span class="hljs-keyword">day</span>) <span class="hljs-keyword">as</span> click_date
) a
</code></div></pre>
<p>查询结果</p>
<p><img src="https://static.couragesteak.com/article/f3aa0608932021c540d392cb37aecd18.png" alt="image.png" /></p>
<h2><a id="2__26"></a>2 查询订单表出现日期，成交额总和</h2>
<p>没有出现的日期，不会被查出来</p>
<pre><div class="hljs"><code class="lang-sql"><span class="hljs-keyword">select</span> DATE_FORMAT(o_id,"%Y-%m-%d") <span class="hljs-keyword">as</span> <span class="hljs-type">date</span>,<span class="hljs-built_in">sum</span>(o_turnover)<span class="hljs-operator">*</span><span class="hljs-number">0.15</span> u_commission <span class="hljs-keyword">from</span> orders
<span class="hljs-keyword">where</span> o_id <span class="hljs-keyword">between</span> "2021-05-26" <span class="hljs-keyword">and</span> "2021-06-02" <span class="hljs-keyword">and</span> uid <span class="hljs-operator">=</span> <span class="hljs-number">2</span>
<span class="hljs-keyword">group</span> <span class="hljs-keyword">by</span> <span class="hljs-type">date</span>;
</code></div></pre>
<p><img src="https://static.couragesteak.com/article/7a61d59841417aa1b7c15c1bf32f2cf4.png" alt="image.png" /></p>
<h2><a id="3__37"></a>3 根据日期统计数据</h2>
<pre><div class="hljs"><code class="lang-powershell"><span class="hljs-built_in">select</span> DATE_FORMAT(date,<span class="hljs-string">"%Y-%m-%d"</span>) day,count(*) num from log <span class="hljs-built_in">group</span> by day;
</code></div></pre>
<p><img src="https://static.couragesteak.com/article/daebd95b7f57de84021b9db0c75c4387.png" alt="image.png" /></p>
<p>参考：<br />
https://blog.csdn.net/weixin_38414968/article/details/108164479</p>
留言