Mysql 查询今天，本月，上月数据的以及按日期查询的sql语句

  1. //1、根据开始时间结束时间查询  SELECT *FROM  record  WHERE 1=1

  2. <if test="dateEnd != null and dateEnd != ''"> AND operation_time <![CDATA[ < ]]> str_to_date(#{dateEnd},'%Y-%m-%d %H:%i:%s')</if>

  3. <if test="dateStart != null and dateStart != ''"> AND operation_time <![CDATA[ >= ]]> str_to_date(#{dateStart},'%Y-%m-%d %H:%i:%s')</if>

  4. ORDER BY id DESC

  5. //2、查询当天数据  SELECT *  FROM  record  WHERE 1=1  AND DATE_FORMAT( operation_time, '%Y%m%d' ) = DATE_FORMAT( CURDATE() , '%Y%m%d' )   ORDER BY id DESC

  6. //3、查询当月数据

  7. SELECT * FROM record  WHERE 1=1   AND DATE_FORMAT( operation_time, '%Y%m' ) = DATE_FORMAT( CURDATE() , '%Y%m' )   ORDER BY id DESC

  8. //4、查询上个月数据 SELECT *  FROM  record  WHERE 1=1   AND PERIOD_DIFF( DATE_FORMAT( NOW( ) , '%Y%m' ) , DATE_FORMAT( operation_time, '%Y%m' ) ) =1   ORDER BY id DESC

版权声明：本文为weixin_47385625原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接和本声明。