mysql统计期初库存_有期初库存的库存查询实战select
DISTINCT rizhi2.mingchengguige as mingchengguige,
ifnull(substring_index(group_concat((rizhi1.qichukucun1)),',',1),
(substring_index(group_concat(rizhi2.atetime>= #{s_btime}
atetime>= date_add(#{s_etime}, interval 1 day)order atetime 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.atetime>= #{s_btime}
atetime>= date_add(#{s_etime}, interval 1 day)order atetime desc),',',0))
) as jieyushuliang,
ifnull(substring_index(group_atetime order atetime desc),',',1),
substring_index(group_atetime order atetime desc),',',1)
)as createtime,
ifnull(substring_index(group_concat(rizhi1.jiagongyigong order atetime desc),',',1),
substring_index(group_concat(rizhi2.jiagongyigong order by rizhi2.jiagongyigong desc),',',1)
)as jiagongyigong,
ifnull(substring_index(group_concat(rizhi1.danwei order atetime desc),',',1),
substring_index(group_concat(rizhi2.danwei order by rizhi2.danwei desc),',',1)
)as danwei,
ifnull(substring_index(group_concat(rizhi1.wuliaobianma order atetime desc),',',1),
substring_index(group_concat(rizhi2.wuliaobianma order by rizhi2.wuliaobianma desc),',',1)
)as wuliaobianma,
ifnull(substring_index(group_concat(rizhi1.id order atetime desc),',',1),
substring_index(group_concat(rizhi2.id order by rizhi2.id desc),',',1)
)as id,
ifnull(substring_index(group_concat(rizhi1.xiangmuweihu order atetime desc),',',1),
substring_index(group_concat(rizhi2.xiangmuweihu order by rizhi2.xiangmuweihu desc),',',1)
)as xiangmuweihu,ifnull(substring_index(group_concat(rizhi1.rukukufang order atetime desc),',',1),
substring_index(group_concat(rizhi2.rukukufang order by rizhi2.rukukufang desc),',',1)
)as rukukufang,ifnull(substring_index(group_concat(rizhi1.dalei order atetime 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 atetime desc),',',1) as id,
substring_index(group_concat(kucunrizhi.xiangmuweihu order atetime desc),',',1)as xiangmuweihu,
substring_index(group_concat(kucunrizhi.rukukufang order atetime desc),',',1)as rukukufang, substring_index(group_concat(kucunrizhi.dalei order atetime desc),',',1)as dalei,
substring_index(group_concat(kucunrizhi.qichukucun order atetime),',',1)qichukucun1,
sum(kucunrizhi.shourushuliang)as shourushuliang1,
sum(kucunrizhi.fachushuliang)as fachushuliang1,
sum(kucunrizhi.tuihuishuliang)as tuihuishuliang1,
substring_index(group_concat(kucunrizhi.jieyushuliang order atetime desc),',',1)jieyushuliang1, substring_index(group_concat(kucunrizhi.jiagongyigong order atetime desc),',',1)as jiagongyigong,
substring_index(group_concat(kucunrizhi.danwei order atetime desc),',',1)as danwei,
substring_index(group_concat(kucunrizhi.wuliaobianma order atetime desc),',',1)as wuliaobianma, substring_index(group_atetime order atetime 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}
atetime>= #{s_btime}
atetime<= date_add(#{s_etime}, interval 1 day)
atetime = #{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}
atetime = #{createtime}
and rizhi2.updatetime = #{updatetime}
group by rizhi2.mingchengguigehaving
(qichukucun != 0)
or (shourushuliang != 0)mysql下载后的初次使用
or (fachushuliang != 0)
or (tuihuishuliang != 0)
or (jieyushuliang != 0)ORDER BY createtime

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。