mybatis中大于、小于、大于等于、小于等于示例&&mysql查询上一天/星期/月数据......

mybatis中大于、小于、大于等于、小于等于示例

<!-- 查询前N天数据 -->
	<select id="getLastDayFreeeze" resultType="DayFreeze">
	  SELECT 
         <include refid="freezeColumns"/>
      FROM dayfreeze a
      WHERE a.DeviceID = #{deviceId}
        AND TO_DAYS(NOW()) - TO_DAYS(a.sysTime) &lt;= #{dayNum}
        AND TO_DAYS(NOW()) - TO_DAYS(a.sysTime) &gt;= 1
	</select>
	
	<!-- 查询前N个月数据 -->
	<select id="getLastMonthFreeeze" resultType="DayFreeze">
	  SELECT 
         <include refid="freezeColumns"/>
      FROM dayfreeze a
      WHERE a.DeviceID = #{deviceId}
        AND PERIOD_DIFF(DATE_FORMAT(NOW(),'%Y%m'),DATE_FORMAT(a.sysTime,'%Y%m')) &lt;= #{monthNum}
        AND PERIOD_DIFF(DATE_FORMAT(NOW(),'%Y%m'),DATE_FORMAT(a.sysTime,'%Y%m')) &gt;= 1
	</select>

mysql查询上一天/星期/月数据…

1，查询当天（今天）的数据

SELECT * FROM order WHERE TO_DAYS(order_time) = TO_DAYS(NOW())

2，查询昨天的数据

SELECT * FROM order WHERE TO_DAYS(NOW())

• TO_DAYS(order_time) = 1

3，查询最近7天的数据（包括今天一共7天）

SELECT * FROM order where DATE_SUB(CURDATE(),
INTERVAL 7 DAY)
< date(order_time)

4，查询最近30天的数据（包括今天一共30天）

SELECT * FROM order where DATE_SUB(CURDATE(),
INTERVAL 30 DAY)
< date(order_time)

5，查询当月（本月）的数据

SELECT * FROM order WHERE DATE_FORMAT(order_time, ‘%Y%m’)
= DATE_FORMAT(CURDATE(), ‘%Y%m’)

6，查询上个月的数据

SELECT * FROM order WHERE PERIOD_DIFF(DATE_FORMAT(NOW(),’%Y%m’),
DATE_FORMAT(order_time,’%Y%m’))
=1

7，查询本季度的数据

SELECT * FROM order WHERE QUARTER(order_time)=QUARTER(NOW())

8，查询上季度的数据

SELECT * FROM order WHERE QUARTER(order_time)=QUARTER(DATE_SUB(NOW(),INTERVAL
1 QUARTER))

9，查询当年（今年）的数据

SELECT * FROM order WHERE YEAR(order_time)=YEAR(NOW())

10，查询去年的数据

SELECT * FROM order WHERE YEAR(order_time)=YEAR(DATE_SUB(NOW(),INTERVAL
1 YEAR))