mysql根据时间查询

//1、根据开始时间结束时间查询
SELECT *
FROM
record
WHERE 1=1
<if test="dateEnd != null and dateEnd != ''"> AND column_time<![CDATA[ < ]]> str_to_date(#{dateEnd},'%Y-%m-%d %H:%i:%s')</if>
<if test="dateStart != null and dateStart != ''"> AND column_time<![CDATA[ >= ]]> str_to_date(#{dateStart},'%Y-%m-%d %H:%i:%s')</if>
ORDER BY id DESC
 
//2、查询当天数据
SELECT *
FROM
record
WHERE 1=1
AND DATE_FORMAT( column_time, '%Y%m%d' ) = DATE_FORMAT( CURDATE() , '%Y%m%d' )
ORDER BY id DESC

查询一周：

select * 
from table 
where 1=1
DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(column_time);
 
 
//3、查询当月数据
SELECT *
FROM
record
WHERE 1=1
AND DATE_FORMAT( column_time, '%Y%m' ) = DATE_FORMAT( CURDATE() , '%Y%m' )
ORDER BY id DESC
 
//4、查询上个月数据
SELECT *
FROM
record
WHERE 1=1
AND PERIOD_DIFF( DATE_FORMAT( NOW( ) , '%Y%m' ) , DATE_FORMAT( column_time, '%Y%m' ) ) =1
ORDER BY id DESC

//查询距离当前现在6个月的数据
select name,submittime from enterprise where submittime between date_sub(now(),interval 6 month) and now();

//查询本年数据
select * from 表名  where YEAR(create_date)=YEAR(NOW());

//查询上年数据
select * from 表名 where year(create_date)=year(date_sub(now(),interval 1 year));

//查询本季度数据
select * from 表名 where QUARTER(create_date)=QUARTER(now());

//查询上季度数据
select * from 表名 where QUARTER(create_date)=QUARTER(DATE_SUB(now(),interval 1 QUARTER));

//查询上周的数据
SELECT * FROM 表名 WHERE YEARWEEK(date_format(submittime,'%Y-%m-%d')) = YEARWEEK(now())-1;

其中where 1=1，由于1=1永远是成立的返回true,条件为真言下之意就是：加上1=1可以应付多变的查询条件，仅仅只是为了满足多条件查询页面中不确定的各种因素而采用的一种构造一条正确能运行的动态 SQL语句的一种方法，并没有其他影响

mybatis 框架可以通过where标签来代替1=1的作用

<select id="findById" resultType="UserEntity">
		SELECT name,password
		FROM user
		<where>
			<if test=" name!='' and name!= null ">
	         name = #{name}
			</if>
			<if test=" password!='' and password!= null ">
	         AND password= #{passwordx}
			</if>
		</where>
</select>