mybatis java8_java8 mybatis-plus

select

DISTINCT rizhi2.mingchengguige asmingchengguige,

ifnull(substring_index(group_concat((rizhi1.qichukucun1)order by rizhi2.createtime desc),',',1),

(substring_index(group_concat((rizhi2.jieyushuliang)order by rizhi2.createtime desc),',',0))

)asqichukucun,

substring_index(group_concat(ifnull(rizhi1.shourushuliang1,0)order by rizhi2.createtime desc ),',',1) asshourushuliang,

substring_index(group_concat(ifnull(rizhi1.fachushuliang1,0)order by rizhi2.createtime desc ),',',1) asfachushuliang,

substring_index(group_concat(ifnull(rizhi1.tuihuishuliang1,0)order by rizhi2.createtime desc ),',',1) astuihuishuliang,

substring_index(group_concat(ifnull(rizhi1.posunshuliang1,0)order by rizhi2.createtime desc ),',',1) asposunshuliang,

substring_index(group_concat(ifnull(rizhi1.weixiushuliang1,0)order by rizhi2.createtime desc ),',',1) asweixiushuliang,

ifnull(substring_index(group_concat((rizhi1.jieyushuliang1)order by rizhi2.createtime desc),',',1),

(substring_index(group_concat((rizhi2.jieyushuliang)order by rizhi2.createtime desc),',',0))

)asjieyushuliang,

ifnull(substring_index(group_concat(rizhi1.createtimeorder by rizhi1.createtime desc),',',1),

substring_index(group_concat(rizhi2.createtimeorder by rizhi2.createtime desc),',',1)

)ascreatetime,

ifnull(substring_index(group_concat(rizhi1.jiagongyigongorder by rizhi1.createtime desc),',',1),

substring_index(group_concat(rizhi2.jiagongyigongorder by rizhi2.jiagongyigong desc),',',1)

)asjiagongyigong,

ifnull(substring_index(group_concat(rizhi1.danweiorder by rizhi1.createtime desc),',',1),

substring_index(group_concat(rizhi2.danweiorder by rizhi2.danwei desc),',',1)

)asdanwei,

ifnull(substring_index(group_concat(rizhi1.wuliaobianmaorder by rizhi1.createtime desc),',',1),

substring_index(group_concat(rizhi2.wuliaobianmaorder by rizhi2.wuliaobianma desc),',',1)

)aswuliaobianma,

ifnull(substring_index(group_concat(rizhi1.idorder by rizhi1.createtime desc),',',1),

substring_index(group_concat(rizhi2.idorder by rizhi2.id desc),',',1)

)asid,

ifnull(substring_index(group_concat(rizhi1.xiangmuweihuorder by rizhi1.createtime desc),',',1),

substring_index(group_concat(rizhi2.xiangmuweihuorder by rizhi2.xiangmuweihu desc),',',1)

)asxiangmuweihu,ifnull(substring_index(group_concat(rizhi1.rukukufangorder by rizhi1.createtime desc),',',1),

substring_index(group_concat(rizhi2.rukukufangorder by rizhi2.rukukufang desc),',',1)

)asrukukufang,ifnull(substring_index(group_concat(rizhi1.daleiorder by rizhi1.createtime desc),',',1),

substring_index(group_concat(rizhi2.daleiorder by rizhi2.dalei desc),',',1)

)asdaleifrom(select

DISTINCT (kucunrizhi.mingchengguige) asmingchengguige,

substring_index(group_concat(kucunrizhi.idorder by kucunrizhi.createtime desc),',',1) asid,

substring_index(group_concat(kucunrizhi.xiangmuweihuorder by kucunrizhi.createtime desc),',',1)asxiangmuweihu,

substring_index(group_concat(kucunrizhi.rukukufangorder by kucunrizhi.createtime desc),',',1)asrukukufang,

substring_index(group_concat(kucunrizhi.daleiorder by kucunrizhi.createtime desc),',',1)asdalei,

substring_index(group_concat(kucunrizhi.qichukucunorder by kucunrizhi.createtime),',',1)qichukucun1,sum(kucunrizhi.shourushuliang)asshourushuliang1,sum(kucunrizhi.fachushuliang)asfachushuliang1,sum(kucunrizhi.tuihuishuliang)astuihuishuliang1,sum(kucunrizhi.posunshuliang)asposunshuliang1,sum(kucunrizhi.weixiushuliang)asweixiushuliang1,

(substring_index(group_concat(

kucunrizhi.qichukucunorder bykucunrizhi.createtime),',',1)+sum(kucunrizhi.shourushuliang)-sum(kucunrizhi.fachushuliang)+sum(kucunrizhi.tuihuishuliang)-sum(kucunrizhi.posunshuliang)-sum(kucunrizhi.weixiushuliang)

)asjieyushuliang1,

substring_index(group_concat(kucunrizhi.jiagongyigongorder by kucunrizhi.createtime desc),',',1)asjiagongyigong,

substring_index(group_concat(kucunrizhi.danweiorder by kucunrizhi.createtime desc),',',1)asdanwei,

substring_index(group_concat(kucunrizhi.wuliaobianmaorder by kucunrizhi.createtime desc),',',1)aswuliaobianma,

substring_index(group_concat(kucunrizhi.createtimeorder by kucunrizhi.createtime desc),',',1)ascreatetimefrom wuliaokucunrizhi askucunrizhi

and kucunrizhi.mingchengguige like#{mingchengguigePattern}

and kucunrizhi.jiagongyigong =#{jiagongyigong}

and kucunrizhi.rukukufang =#{rukukufang}

and kucunrizhi.xiangmuweihu =#{xiangmuweihu}

and kucunrizhi.dalei =#{dalei}

and kucunrizhi.createtime >=#{s_btime}

and kucunrizhi.createtime <= date_add(#{s_etime}, interval 1 day)

and kucunrizhi.createtime =#{createtime}

and kucunrizhi.updatetime =#{updatetime}

GROUP BYkucunrizhi.mingchengguigeORDER BY createtime DESC)rizhi1right outer join wuliaokucunrizhi as rizhi2 onrizhi1.mingchengguige=rizhi2.mingchengguige

and rizhi2.mingchengguige like#{mingchengguigePattern}

and rizhi2.jiagongyigong =#{jiagongyigong}

and rizhi2.rukukufang =#{rukukufang}

and rizhi2.xiangmuweihu =#{xiangmuweihu}

and rizhi2.dalei =#{dalei}

and rizhi2.createtime =#{createtime}

and rizhi2.updatetime =#{updatetime}

group byrizhi2.mingchengguige

having(qichukucun!= 0)or (shourushuliang != 0)or (fachushuliang != 0)or (tuihuishuliang != 0)or (posunshuliang != 0)or (weixiushuliang != 0)or (jieyushuliang != 0)

order by ${sort} ${order}

order by createtime desc