SQLite

sqlite

select * from message where 
       flag=0 
       or 
       (
          (dealno=6 or (removeno in 
                    (select removeno from message where removedate >= '2017-12-25 09:00:00'
                    ))
          ) 
       and 
       (
           (senddate >= '2017-12-25 09:00:00'  
           or dealdate >= '2017-12-25 09:00:00' 
           or removedate >= '2017-12-25 09:00:00'
           )
       and senddate <='2017-12-25 24:00:00'
       )
       )
order by senddate

 mybatis

select * from message where flag=0 or ((dealno=#{dealno} or (removeno in (select removeno from message where removedate &gt;= #{removedate}))) 
        and ((senddate &gt;= #{senddate}  or dealdate &gt;= #{dealdate} or removedate &gt;= #{removedate}) 
        and senddate &lt;=#{senddate2}))
        order by senddate

 注意点:

  1.时间

  2.mybatis映射文书参数和字段要依次对应,可以使用赋值转变

优化了一波

 

select * from message where dealno=6 and  msgno in (
select msgno from message where senddate> '2017-12-25 09:00:00' or dealdate > '2017-12-25 09:00:00'or removedate > '2017-12-25 09:00:00')
order by senddate

select * from message where dealno=6 and flag=0 and msgno in (
select msgno from message where senddate> '2017-12-25 00:00:00' or dealdate > '2017-12-25 00:00:00'or removedate > '2017-12-25 00:00:00')
order by senddate;

 

网站地图xml地图