mysql统计期初库存_有期初库存的库存查询实战

select

DISTINCT rizhi2.mingchengguige as mingchengguige,

ifnull(substring_index(group_concat((rizhi1.qichukucun1)),',',1),

(substring_index(group_concat(rizhi2.jieyushuliangand rizhi2.createtime>= #{s_btime}

and rizhi2.createtime>= date_add(#{s_etime}, interval 1 day)order by rizhi2.createtime desc),',',0))

)as qichukucun,

substring_index(group_concat(ifnull(rizhi1.shourushuliang1,0) ),',',1) as shourushuliang,

substring_index(group_concat(ifnull(rizhi1.fachushuliang1,0) ),',',1) as fachushuliang,

substring_index(group_concat(ifnull(rizhi1.tuihuishuliang1,0) ),',',1) as tuihuishuliang,

ifnull(substring_index(group_concat((rizhi1.jieyushuliang1)),',',1),

(substring_index(group_concat(rizhi2.jieyushuliangand rizhi2.createtime>= #{s_btime}

and rizhi2.createtime>= date_add(#{s_etime}, interval 1 day)order by rizhi2.createtime desc),',',0))

) as jieyushuliang,

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

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

)as createtime,

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

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

)as jiagongyigong,

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

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

)as danwei,

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

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

)as wuliaobianma,

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

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

)as id,

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

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

)as xiangmuweihu,ifnull(substring_index(group_concat(rizhi1.rukukufang order by rizhi1.createtime desc),',',1),

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

)as rukukufang,ifnull(substring_index(group_concat(rizhi1.dalei order by rizhi1.createtime desc),',',1),

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

)as dalei

from (

select

DISTINCT (kucunrizhi.mingchengguige) as mingchengguige,

substring_index(group_concat(kucunrizhi.id order by kucunrizhi.createtime desc),',',1) as id,

substring_index(group_concat(kucunrizhi.xiangmuweihu order by kucunrizhi.createtime desc),',',1)as

xiangmuweihu,

substring_index(group_concat(kucunrizhi.rukukufang order by kucunrizhi.createtime desc),',',1)as rukukufang,

substring_index(group_concat(kucunrizhi.dalei order by kucunrizhi.createtime desc),',',1)as dalei,

substring_index(group_concat(kucunrizhi.qichukucun order by kucunrizhi.createtime),',',1)qichukucun1,

sum(kucunrizhi.shourushuliang)as shourushuliang1,

sum(kucunrizhi.fachushuliang)as fachushuliang1,

sum(kucunrizhi.tuihuishuliang)as tuihuishuliang1,

substring_index(group_concat(kucunrizhi.jieyushuliang order by kucunrizhi.createtime desc),',',1)jieyushuliang1,

substring_index(group_concat(kucunrizhi.jiagongyigong order by kucunrizhi.createtime desc),',',1)as

jiagongyigong,

substring_index(group_concat(kucunrizhi.danwei order by kucunrizhi.createtime desc),',',1)as danwei,

substring_index(group_concat(kucunrizhi.wuliaobianma order by kucunrizhi.createtime desc),',',1)as wuliaobianma,

substring_index(group_concat(kucunrizhi.createtime order by kucunrizhi.createtime desc),',',1)as createtime

from wuliaokucunrizhi as kucunrizhi

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 BY kucunrizhi.mingchengguige

ORDER BY createtime DESC

)rizhi1 right outer join wuliaokucunrizhi as rizhi2 on

rizhi1.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 by rizhi2.mingchengguigehaving

(qichukucun != 0)

or (shourushuliang != 0)

or (fachushuliang != 0)

or (tuihuishuliang != 0)

or (jieyushuliang != 0)ORDER BY createtime