当前位置:Gxlcms > 数据库问题 > mySql 查询当天、本周、最近7天、本月、最近30天的语句

mySql 查询当天、本周、最近7天、本月、最近30天的语句

时间:2021-07-01 10:21:17 帮助过:14人阅读

2017年04月13日 16:40:38

-- 当天

  1. <span style="color: #0000ff"></span><span style="font-family: Microsoft YaHei; font-size: 14px"><span style="background-color: rgb(192,192,192)"><span style="background-color: rgb(255,255,255)"><span style="color: #3366FF">SELECT</span> * FROM  表名 WHERE</span></span> <span style="color: #0000ff">where</span> to_days(时间字段名) <span style="color: #808080">=</span> to_days(now());</span>
-- 本周

SELECT * FROM  表名 WHERE YEARWEEK(date_format(时间字段,‘%Y-%m-%d‘)) = YEARWEEK(now());

-- 最近7天

SELECT * FROM  表名  where date_sub(curdate(), INTERVAL 7 DAY) <= date (时间字段);

-- 本月

SELECT * FROM  表名 where date_format(时间字段, ‘%Y%m‘) = date_format(curdate() , ‘%Y%m‘);

-- 最近30天

SELECT * FROM  表名 WHERE date_sub(curdate(), INTERVAL 30 DAY) <= date(时间字段);

-- 上一月


  1. <span style="font-family: Microsoft YaHei; font-size: 14px; color: #0000ff">SELECT</span><span style="font-family: Microsoft YaHei; font-size: 14px"> <span style="color: #808080">*</span> <span style="color: #0000ff">FROM</span> 表名 <span style="color: #0000ff">WHERE</span> PERIOD_DIFF( date_format( now( ) , <span style="color: #ff0000">‘</span><span style="color: #ff0000">%Y%m</span><span style="color: #ff0000">‘</span> ) , date_format( 时间字段名, <span style="color: #ff0000">‘</span><span style="color: #ff0000">%Y%m</span><span style="color: #ff0000">‘</span> ) ) <span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">1</span></span><span style="font-family: Microsoft YaHei; font-size: 14px"><span style="color: #800000; font-weight: bold"></span></span>

  1. <span style="font-family: Microsoft YaHei; font-size: 14px"><span style="color: #800000; font-weight: bold">-- 本季度</span></span><span style="font-family: Microsoft YaHei; font-size: 14px"><span style="color: #800000; font-weight: bold"></span></span><pre class="layui-box layui-code-view layui-code-notepad"><ol class="layui-code-ol"><li><pre class="layui-box layui-code-view layui-code-notepad"><ol class="layui-code-ol"><li><span style="font-size: 14px"><span style="font-family: Microsoft YaHei; color: #0000ff">SELECT</span><span style="font-family: Microsoft YaHei"> <span style="color: #808080">*</span> <span style="color: #0000ff">FROM</span> 表名 <span style="color: #0000ff"><span style="color: #800000">WHERE</span></span> QUARTER(create_date)<span style="color: #808080">=</span>QUARTER(now());</span></span></li></ol></pre></li></ol></pre>

查询上季度数据

  1. <span style="font-family: Microsoft YaHei; font-size: 14px; color: #0000ff"></span><span style="font-family: Microsoft YaHei; font-size: 14px"><span style="font-size: 14px"><span style="font-size: 14px"><span style="background-color: rgb(192,192,192)"><span style="background-color: rgb(255,255,255)"><span style="font-size: 14px"><span style="background-color: rgb(192,192,192)"><span style="background-color: rgb(255,255,255)"><span style="color: #3366FF">SELECT</span> * FROM  表名 WHERE</span></span></span></span></span></span></span> QUARTER(create_date)<span style="color: #808080">=</span>QUARTER(DATE_SUB(now(),interval <span style="color: #800000; font-weight: bold">1</span> QUARTER));</span>

查询本年数据

  1. <span style="font-family: Microsoft YaHei; font-size: 14px; color: #0000ff"></span><span style="font-family: Microsoft YaHei; font-size: 14px"><span style="font-size: 14px"><span style="font-size: 14px"><span style="background-color: rgb(192,192,192)"><span style="background-color: rgb(255,255,255)"><span style="font-size: 14px"><span style="background-color: rgb(192,192,192)"><span style="background-color: rgb(255,255,255)"><span style="color: #3366FF">SELECT</span> * FROM  表名 WHERE</span></span></span></span></span></span></span><span style="color: #0000ff"></span> <span style="color: #ff00ff">YEAR</span>(create_date)<span style="color: #808080">=</span><span style="color: #ff00ff">YEAR</span>(NOW());</span>

查询上年数据

  1. <span style="font-family: Microsoft YaHei; font-size: 14px; color: #0000ff"></span><span style="font-family: Microsoft YaHei; font-size: 14px"><span style="font-size: 14px"><span style="font-size: 14px"><span style="background-color: rgb(192,192,192)"><span style="background-color: rgb(255,255,255)"><span style="font-size: 14px"><span style="background-color: rgb(192,192,192)"><span style="background-color: rgb(255,255,255)"><span style="color: #3366FF">SELECT</span> * FROM  表名 WHERE</span></span></span></span></span></span></span><span style="color: #0000ff"></span> <span style="color: #ff00ff">year</span>(create_date)<span style="color: #808080">=</span><span style="color: #ff00ff">year</span>(date_sub(now(),interval <span style="color: #800000; font-weight: bold">1</span> <span style="color: #ff00ff">year</span>));</span>

查询上周的数据

  1. <span style="font-family: Microsoft YaHei; font-size: 14px; color: #0000ff"></span><span style="font-family: Microsoft YaHei; font-size: 14px"><span style="font-size: 14px"><span style="font-size: 14px"><span style="background-color: rgb(192,192,192)"><span style="background-color: rgb(255,255,255)"><span style="font-size: 14px"><span style="background-color: rgb(192,192,192)"><span style="background-color: rgb(255,255,255)"><span style="color: #3366FF">SELECT</span> * FROM  表名 WHERE</span></span></span></span></span></span></span><span style="color: #0000ff"></span> YEARWEEK(date_format(submittime,<span style="color: #ff0000">‘</span><span style="color: #ff0000">%Y-%m-%d</span><span style="color: #ff0000">‘</span>)) <span style="color: #808080">=</span> YEARWEEK(now())<span style="color: #808080">-</span><span style="color: #800000; font-weight: bold">1</span>;</span>

查询上个月的数据

技术分享图片
  1. <span style="font-family: Microsoft YaHei; font-size: 14px; color: #0000ff"></span><span style="font-family: Microsoft YaHei; font-size: 14px"><span style="font-size: 14px"><span style="font-size: 14px"><span style="background-color: rgb(192,192,192)"><span style="background-color: rgb(255,255,255)"><span style="font-size: 14px"><span style="background-color: rgb(192,192,192)"><span style="background-color: rgb(255,255,255)"><span style="color: #3366FF">SELECT</span> * FROM  表名 WHERE</span></span></span></span></span></span></span><span style="color: #0000ff"></span> date_format(submittime,<span style="color: #ff0000">‘</span><span style="color: #ff0000">%Y-%m</span><span style="color: #ff0000">‘</span>)<span style="color: #808080">=</span>date_format(DATE_SUB(curdate(), INTERVAL <span style="color: #800000; font-weight: bold">1</span> <span style="color: #ff00ff">MONTH</span>),<span style="color: #ff0000">‘</span><span style="color: #ff0000">%Y-%m</span><span style="color: #ff0000">‘</span><span style="color: #000000">)</span><span style="color: #0000ff"></span><span style="font-size: 14px"><span style="font-size: 14px"><span style="background-color: rgb(192,192,192)"><span style="background-color: rgb(255,255,255)"><span style="font-size: 14px"><span style="background-color: rgb(192,192,192)"><span style="background-color: rgb(255,255,255)"><span style="color: #3366FF">SELECT</span> * FROM  表名 WHERE</span></span></span></span></span></span></span><span style="color: #0000ff"></span> DATE_FORMAT(pudate,<span style="color: #ff0000">‘</span><span style="color: #ff0000">%Y%m</span><span style="color: #ff0000">‘</span>) <span style="color: #808080">=</span> DATE_FORMAT(CURDATE(),<span style="color: #ff0000">‘</span><span style="color: #ff0000">%Y%m</span><span style="color: #ff0000">‘</span><span style="color: #000000">) ; </span><span style="font-size: 14px"><span style="font-size: 14px"><span style="background-color: rgb(192,192,192)"><span style="background-color: rgb(255,255,255)"><span style="font-size: 14px"><span style="background-color: rgb(192,192,192)"><span style="background-color: rgb(255,255,255)"><span style="color: #3366FF">SELECT</span> * FROM  表名 WHERE</span></span></span></span></span></span></span><span style="color: #0000ff"></span> WEEKOFYEAR(FROM_UNIXTIME(pudate,<span style="color: #ff0000">‘</span><span style="color: #ff0000">%y-%m-%d</span><span style="color: #ff0000">‘</span>)) <span style="color: #808080">=</span><span style="color: #000000"> WEEKOFYEAR(now()) </span><span style="color: #0000ff"></span><span style="font-size: 14px"><span style="font-size: 14px"><span style="background-color: rgb(192,192,192)"><span style="background-color: rgb(255,255,255)"><span style="font-size: 14px"><span style="background-color: rgb(192,192,192)"><span style="background-color: rgb(255,255,255)"><span style="color: #3366FF">SELECT</span> * FROM  表名 WHERE</span></span></span></span></span></span></span> <span style="color: #ff00ff">MONTH</span>(FROM_UNIXTIME(pudate,<span style="color: #ff0000">‘</span><span style="color: #ff0000">%y-%m-%d</span><span style="color: #ff0000">‘</span>)) <span style="color: #808080">=</span> <span style="color: #ff00ff">MONTH</span><span style="color: #000000">(now()) </span><span style="color: #0000ff"></span><span style="font-size: 14px"><span style="font-size: 14px"><span style="background-color: rgb(192,192,192)"><span style="background-color: rgb(255,255,255)"><span style="font-size: 14px"><span style="background-color: rgb(192,192,192)"><span style="background-color: rgb(255,255,255)"><span style="color: #3366FF">SELECT</span> * FROM  表名 WHERE</span></span></span></span></span></span></span><span style="color: #0000ff"></span> <span style="color: #ff00ff">YEAR</span>(FROM_UNIXTIME(pudate,<span style="color: #ff0000">‘</span><span style="color: #ff0000">%y-%m-%d</span><span style="color: #ff0000">‘</span>)) <span style="color: #808080">=</span> <span style="color: #ff00ff">YEAR</span>(now()) <span style="color: #808080">and</span> <span style="color: #ff00ff">MONTH</span>(FROM_UNIXTIME(pudate,<span style="color: #ff0000">‘</span><span style="color: #ff0000">%y-%m-%d</span><span style="color: #ff0000">‘</span>)) <span style="color: #808080">=</span> <span style="color: #ff00ff">MONTH</span><span style="color: #000000">(now()) </span><span style="color: #0000ff"></span><span style="font-size: 14px"><span style="font-size: 14px"><span style="background-color: rgb(192,192,192)"><span style="background-color: rgb(255,255,255)"><span style="font-size: 14px"><span style="background-color: rgb(192,192,192)"><span style="background-color: rgb(255,255,255)"><span style="color: #3366FF">SELECT</span> * FROM  表名 WHERE</span></span></span></span></span></span></span> pudate <span style="color: #808080">between</span> 上月最后一天 <span style="color: #808080">and</span> 下月第一天 </span>
技术分享图片

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

  1. <span style="font-family: Microsoft YaHei; font-size: 14px; color: #0000ff"></span><span style="font-family: Microsoft YaHei; font-size: 14px"><span style="font-size: 14px"><span style="font-size: 14px"><span style="background-color: rgb(192,192,192)"><span style="background-color: rgb(255,255,255)"><span style="font-size: 14px"><span style="background-color: rgb(192,192,192)"><span style="background-color: rgb(255,255,255)"><span style="color: #3366FF">SELECT</span> * FROM  表名 WHERE</span></span></span></span></span></span></span><span style="color: #0000ff"></span> submittime <span style="color: #808080">between</span> date_sub(now(),interval <span style="color: #800000; font-weight: bold">6</span> <span style="color: #ff00ff">month</span>) <span style="color: #808080">and</span> now();</span>





mySql 查询当天、本周、最近7天、本月、最近30天的语句

标签:16px   .net   creat   tags   字段   ros   div   mil   cal   

人气教程排行