mybatis使用中的记录

时间:2023-01-29 20:06:31

一: 常用sql语句:

查询某段时间内的数据:   https://www.cnblogs.com/benefitworld/p/5832897.html

  1.查询当天的数据:                SELECT * from 表名 where to_days(时间字段名) = to_days(now());

  2.查询昨天的数据:                SELECT * FROM 表名 WHERE TO_DAYS( NOW( ) ) - TO_DAYS( 时间字段名) <= 1 and TO_DAYS( NOW( ) ) - TO_DAYS( 时间字段名) > 0;

  3.查询近7天的数据:              SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(时间字段名);

  4.查询本周的数据:               SELECT name,submittime FROM enterprise WHERE YEARWEEK(date_format(submittime,'%Y-%m-%d')) = YEARWEEK(now());

  5.查询上周的数据:               SELECT name,submittime FROM enterprise WHERE YEARWEEK(date_format(submittime,'%Y-%m-%d')) = YEARWEEK(now())-1;

  6.查询本月的数据:                SELECT * FROM 表名 WHERE DATE_FORMAT( 时间字段名, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' );

  7.查询上个月的数据:            SELECT * FROM 表名 WHERE PERIOD_DIFF( date_format( now( ) , '%Y%m' ) , date_format( 时间字段名, '%Y%m' ) ) =1;

  8.查询本季度的数据:            SELECT * from `ht_invoice_information` where QUARTER(create_date)=QUARTER(now());

  9.查询上季度的数据:            SELECT * from `ht_invoice_information` where QUARTER(create_date)=QUARTER(DATE_SUB(now(),interval 1 QUARTER));

  10.查询本年的数据:               SELECT * from `ht_invoice_information` where YEAR(create_date)=YEAR(NOW());

  11.查询上年的数据:                SELECT * from `ht_invoice_information` where year(create_date)=year(date_sub(now(),interval 1 year));

  12.查询距离现在6个月的数据: SELECT name,submittime from enterprise where submittime between date_sub(now(),interval 6 month) and now();

 

  1.查询每天的数据量:                SELECT DATE_FORMAT(create_time,'%Y-%m-%d') create_time, count(1) num from core_user GROUP BY DATE_FORMAT(create_time,'%Y-%m-%d');

  2.查询近7天内每天的数据量:   SELECT DATE_FORMAT(create_time,'%Y-%m-%d') create_time, count(1) num from core_user where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(create_time)  GROUP BY DATE_FORMAT(create_time,'%Y-%m-%d');


 

 二:遇到的bug

  1. mybatis中对于特殊字符的处理,如“<”, 会报错,此时可以用  <![CDATA[<]]>  对特殊字符进行处理

  2. case when  ... then  ... when  .. then  ..  end  as  ..

        <if test="applyTime1 != null and applyTime1 != ''">
                AND cbi.apply_time <![CDATA[>=]]> '${applyTime1} 00:00:00'
           </if>

  3. 批量更新foreach

        <if test="ids != null and ids != ''">
              AND id in
              <foreach collection="ids" item="id" open="(" separator="," close=")">
                  #{id}
              </foreach>
          </if>

  4. 在用mybatis时没有用实体作为返回(用的是Map) 因此出现了 在返回参数为null的时候 不会返回字段  https://blog.csdn.net/zkd12344/article/details/53261253

    解决方法:   在mybatis-config.xml中配置  

        <settings>  
              <setting name="cacheEnabled" value="true"/>   
               <setting name="callSettersOnNulls" value="true"/>  
          </settings>  

    mybatis使用中的记录