mysql 查询当天、本周，本月，上一个月的数据

朱雀 2021-12-19 14:41 404阅读 0赞

今天 select\*from表名whereto\_days(时间字段名)=to\_days(now()); 昨天 SELECT\*FROM表名WHERETO\_DAYS(NOW())-TO\_DAYS(时间字段名)=1 近7天 SELECT\*FROM表名whereDATE\_SUB(CURDATE(),INTERVAL7DAY)=date(时间字段名) 近30天 SELEC...

<table> 
 <tbody> 
  <tr> 
   <td> <p><code>select</code>&nbsp;<code>*&nbsp;</code><code>from</code>&nbsp;<code>表名&nbsp;</code><code>where</code>&nbsp;<code>to_days(时间字段名)&nbsp;=&nbsp;to_days(now());</code></p> </td> 
  </tr> 
 </tbody> 
</table>

昨天

<table> 
 <tbody> 
  <tr> 
   <td> <p><code>SELECT</code>&nbsp;<code>*&nbsp;</code><code>FROM</code>&nbsp;<code>表名&nbsp;</code><code>WHERE</code>&nbsp;<code>TO_DAYS(&nbsp;NOW(&nbsp;)&nbsp;)&nbsp;-&nbsp;TO_DAYS(&nbsp;时间字段名)&nbsp;&lt;=&nbsp;1</code></p> </td> 
  </tr> 
 </tbody> 
</table>

近7天

<table> 
 <tbody> 
  <tr> 
   <td> <p><code>SELECT</code>&nbsp;<code>*&nbsp;</code><code>FROM</code>&nbsp;<code>表名&nbsp;</code><code>where</code>&nbsp;<code>DATE_SUB(CURDATE(),&nbsp;INTERVAL&nbsp;7&nbsp;</code><code>DAY</code><code>)&nbsp;&lt;=&nbsp;</code><code>date</code><code>(时间字段名)</code></p> </td> 
  </tr> 
 </tbody> 
</table>

近30天

<table> 
 <tbody> 
  <tr> 
   <td> <p><code>SELECT</code>&nbsp;<code>*&nbsp;</code><code>FROM</code>&nbsp;<code>表名&nbsp;</code><code>where</code>&nbsp;<code>DATE_SUB(CURDATE(),&nbsp;INTERVAL&nbsp;30&nbsp;</code><code>DAY</code><code>)&nbsp;&lt;=&nbsp;</code><code>date</code><code>(时间字段名)</code></p> </td> 
  </tr> 
 </tbody> 
</table>

本月

<table> 
 <tbody> 
  <tr> 
   <td> <p><code>SELECT</code>&nbsp;<code>*&nbsp;</code><code>FROM</code>&nbsp;<code>表名&nbsp;</code><code>WHERE</code>&nbsp;<code>DATE_FORMAT(&nbsp;时间字段名,&nbsp;</code><code>'%Y%m'</code>&nbsp;<code>)&nbsp;=&nbsp;DATE_FORMAT(&nbsp;CURDATE(&nbsp;)&nbsp;,&nbsp;</code><code>'%Y%m'</code>&nbsp;<code>)</code></p> </td> 
  </tr> 
 </tbody> 
</table>

上一月

<table> 
 <tbody> 
  <tr> 
   <td> <p><code>LECT&nbsp;*&nbsp;</code><code>FROM</code>&nbsp;<code>表名&nbsp;</code><code>WHERE</code>&nbsp;<code>PERIOD_DIFF(&nbsp;date_format(&nbsp;now(&nbsp;)&nbsp;,&nbsp;</code><code>'%Y%m'</code>&nbsp;<code>)&nbsp;,&nbsp;date_format(&nbsp;时间字段名,&nbsp;</code><code>'%Y%m'</code>&nbsp;<code>)&nbsp;)&nbsp;=1</code></p> </td> 
  </tr> 
 </tbody> 
</table>

查询本季度数据

<table> 
 <tbody> 
  <tr> 
   <td> <p><code>select</code>&nbsp;<code>*&nbsp;</code><code>from</code>&nbsp;<code>`ht_invoice_information`&nbsp;</code><code>where</code>&nbsp;<code>QUARTER(create_date)=QUARTER(now());</code></p> </td> 
  </tr> 
 </tbody> 
</table>

查询上季度数据

<table> 
 <tbody> 
  <tr> 
   <td> <p><code>select</code>&nbsp;<code>*&nbsp;</code><code>from</code>&nbsp;<code>`ht_invoice_information`&nbsp;</code><code>where</code>&nbsp;<code>QUARTER(create_date)=QUARTER(DATE_SUB(now(),interval&nbsp;1&nbsp;QUARTER));</code></p> </td> 
  </tr> 
 </tbody> 
</table>

查询本年数据

<table> 
 <tbody> 
  <tr> 
   <td> <p><code>select</code>&nbsp;<code>*&nbsp;</code><code>from</code>&nbsp;<code>`ht_invoice_information`&nbsp;</code><code>where</code>&nbsp;<code>YEAR</code><code>(create_date)=</code><code>YEAR</code><code>(NOW());</code></p> </td> 
  </tr> 
 </tbody> 
</table>

查询上年数据

<table> 
 <tbody> 
  <tr> 
   <td> <p><code>select</code>&nbsp;<code>*&nbsp;</code><code>from</code>&nbsp;<code>`ht_invoice_information`&nbsp;</code><code>where</code>&nbsp;<code>year</code><code>(create_date)=</code><code>year</code><code>(date_sub(now(),interval&nbsp;1&nbsp;</code><code>year</code><code>));</code></p> </td> 
  </tr> 
 </tbody> 
</table>

查询当前这周的数据

<table> 
 <tbody> 
  <tr> 
   <td> <p><code>SELECT</code>&nbsp;<code>name</code><code>,submittime&nbsp;</code><code>FROM</code>&nbsp;<code>enterprise&nbsp;</code><code>WHERE</code>&nbsp;<code>YEARWEEK(date_format(submittime,</code><code>'%Y-%m-%d'</code><code>))&nbsp;=&nbsp;YEARWEEK(now());</code></p> </td> 
  </tr> 
 </tbody> 
</table>

查询上周的数据

查询上个月的数据

<table> 
 <tbody> 
  <tr> 
   <td> <p><code>select&nbsp;name,submittime&nbsp;from&nbsp;enterprise&nbsp;where&nbsp;date</code><code>_</code><code>format(submittime,</code><code>'%Y-%m'</code><code>)</code><code>=</code><code>date</code><code>_</code><code>format(DATE</code><code>_</code><code>SUB(curdate(),&nbsp;INTERVAL&nbsp;</code><code>1</code>&nbsp;<code>MONTH),</code><code>'%Y-%m'</code><code>)select&nbsp;*&nbsp;from&nbsp;user&nbsp;where&nbsp;DATE</code><code>_</code><code>FORMAT(pudate,</code><code>'%Y%m'</code><code>)&nbsp;</code><code>=</code>&nbsp;<code>DATE</code><code>_</code><code>FORMAT(CURDATE(),</code><code>'%Y%m'</code><code>)&nbsp;;</code></p> </td> 
  </tr> 
 </tbody> 
</table>

<table> 
 <tbody> 
  <tr> 
   <td> <p><code>select</code>&nbsp;<code>*&nbsp;</code><code>from</code>&nbsp;<code>user</code>&nbsp;<code>where</code>&nbsp;<code>WEEKOFYEAR(FROM_UNIXTIME(pudate,</code><code>'%y-%m-%d'</code><code>))&nbsp;=&nbsp;WEEKOFYEAR(now())</code></p> </td> 
  </tr> 
 </tbody> 
</table>

<table> 
 <tbody> 
  <tr> 
   <td> <p><code>select</code>&nbsp;<code>*&nbsp;</code><code>from</code>&nbsp;<code>user</code>&nbsp;<code>where</code>&nbsp;<code>MONTH</code><code>(FROM_UNIXTIME(pudate,</code><code>'%y-%m-%d'</code><code>))&nbsp;=&nbsp;</code><code>MONTH</code><code>(now())</code></p> </td> 
  </tr> 
 </tbody> 
</table>

<table> 
 <tbody> 
  <tr> 
   <td> <p><code>select</code>&nbsp;<code>*&nbsp;</code><code>from</code>&nbsp;<code>user</code>&nbsp;<code>where</code>&nbsp;<code>YEAR</code><code>(FROM_UNIXTIME(pudate,</code><code>'%y-%m-%d'</code><code>))&nbsp;=&nbsp;</code><code>YEAR</code><code>(now())&nbsp;</code><code>and</code>&nbsp;<code>MONTH</code><code>(FROM_UNIXTIME(pudate,</code><code>'%y-%m-%d'</code><code>))&nbsp;=&nbsp;</code><code>MONTH</code><code>(now())</code></p> </td> 
  </tr> 
 </tbody> 
</table>

<table> 
 <tbody> 
  <tr> 
   <td> <p><code>select</code>&nbsp;<code>*&nbsp;</code><code>from</code>&nbsp;<code>user</code>&nbsp;<code>where</code>&nbsp;<code>pudate&nbsp;</code><code>between</code></p> </td> 
  </tr> 
 </tbody> 
</table>

上月最后一天  and 下月第一天

查询当前月份的数据

<table> 
 <tbody> 
  <tr> 
   <td> <p><code>select</code>&nbsp;<code>name</code><code>,submittime&nbsp;</code><code>from</code>&nbsp;<code>enterprise&nbsp;&nbsp;&nbsp;</code><code>where</code>&nbsp;<code>date_format(submittime,</code><code>'%Y-%m'</code><code>)=date_format(now(),</code><code>'%Y-%m'</code><code>)</code></p> </td> 
  </tr> 
 </tbody> 
</table>

查询距离当前现在6个月的数据

`select` `name``,submittime ``from` `enterprise ``where` `submittime ``between` `date_sub(now(),interval 6 ``month``) ``and` `now();`