1. IT分享知识网首页

有期初库存的库存查询实战

老牧童 • • 未分类 • 阅读 74

有期初库存的库存查询实战期初库存为搜索时间开始前最近的该物品的结余数量图1暂未搜索时当勾选搜索,多出一列为期初库存点击查询,对应期初数量发生变更,如上所述使用mybatis进行查询起先,建立了数据库表,其记录为单次的收入,发出变更在进行查询时,根据sum(收入数量),sum(发出数量)来展示,并根据名称

大家好，欢迎来到IT知识分享网。

期初库存 为 搜索时间开始前最近的该物品的结余数量

图1 暂未搜索时

有期初库存的库存查询实战

 

 当勾选搜索,多出一列为期初库存

有期初库存的库存查询实战

 

 点击查询,对应期初数量发生变更,如上所述

 

使用mybatis进行查询 

起先,建立了数据库表,其记录为单次的收入,发出变更

在进行查询时,根据sum(收入数量),sum(发出数量)来展示,并根据名称 group by

 

而这里的难点,当搜索时间区间内没有该物品的变更,要获取其期初库存为最近的结余数量

#获取离搜索开始时间最近的那条数据的期初库存,以及获取离搜索结束时间最近的那条数据的库存余量.
#'2019-12-27 22:02:01' 为假设开始时间
select qichu.qichukucun from (select qichukucun from wuliaokucunrizhi where createtime > '2019-12-27 22:02:01' ORDER BY 
createtime limit 1) qichu

如果要sum收入和发出数量,首先获取到该搜索时间区间的数据元组,再进行

假如数据元组没有包含有些物品,则不满足要求,必须要进行所有库存物品的展示,以及展示其期初库存

 

首先能想到的是自表关联,一个表根据时间区间进行sum,另一个则group by名称,就可以进行全部物品名称,以及部分时间区间内的sum的值计算

 <select id="listKucunWithRiZhi" resultMap="BaseResultMap">
        select DISTINCT rizhi2.mingchengguige as mingchengguige,
        ifnull(rizhi1.qichukucun1,0) qichukucun,
        ifnull(rizhi1.shourushuliang1,0) shourushuliang,
        ifnull(rizhi1.fachushuliang1,0) fachushuliang,
        ifnull(rizhi1.tuihuishuliang1,0) tuihuishuliang,
        ifnull(rizhi1.jieyushuliang1,0) jieyushuliang 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.createtime order by kucunrizhi.createtime desc),',',1)as createtime
        from wuliaokucunrizhi as kucunrizhi
        <where>
            <if test="mingchengguige != null and mingchengguige != '' ">
                <bind name="mingchengguigePattern" value="'%'+mingchengguige+'%'"/>
                and kucunrizhi.mingchengguige like #{mingchengguigePattern}
            </if>
            <if test="jiagongyigong != null and jiagongyigong != '' ">
                and kucunrizhi.jiagongyigong = #{jiagongyigong}
            </if>
            <if test="rukukufang != null and rukukufang != '' ">
                and kucunrizhi.rukukufang = #{rukukufang}
            </if>
            <if test="xiangmuweihu != null and xiangmuweihu != ''">
                and kucunrizhi.xiangmuweihu = #{xiangmuweihu}
            </if>
            <if test="dalei != null and dalei != '' ">
                and kucunrizhi.dalei = #{dalei}
            </if>
            <if test="s_btime != null">
                and kucunrizhi.createtime &gt;= #{s_btime}
            </if>
            <if test="s_etime != null">
                and kucunrizhi.createtime &lt;= date_add(#{s_etime}, interval 1 day)
            </if>
            <if test="createtime != null">
                and kucunrizhi.createtime = #{createtime}
            </if>
            <if test="updatetime != null">
                and kucunrizhi.updatetime = #{updatetime}
            </if>
        </where>
        GROUP BY kucunrizhi.mingchengguige
        <if test="sort == null">
            ORDER BY createtime DESC
        </if>
        <if test="sort != null and sort != ''">
            order by ${sort} ${order}
        </if>)rizhi1 right join wuliaokucunrizhi as rizhi2 on
        rizhi1.mingchengguige = rizhi2.mingchengguige
    </select>

有期初库存的库存查询实战

 

 然而这里并无法获取到正常的期初库存值,原因是其值获取是根据时间区间内来拿到的,如果不在时间区间,则无法返回

测试解决sql (*尚未发现错误地方) 这个用mybatis plus写的按说更麻烦

<select id="listKucunWithRiZhi" resultMap="BaseResultMap">
        select
        DISTINCT rizhi2.mingchengguige as mingchengguige,
        ifnull(substring_index(group_concat((rizhi1.qichukucun1)),',',1),
        (substring_index(group_concat(rizhi2.jieyushuliang
        <if test="s_btime != null and s_etime != null">
            and rizhi2.createtime &gt;= #{s_btime}
            and rizhi2.createtime &gt;= date_add(#{s_etime}, interval 1 day)
        </if>
        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.jieyushuliang
        <if test="s_btime != null and s_etime != null">
            and rizhi2.createtime &gt;= #{s_btime}
            and rizhi2.createtime &gt;= date_add(#{s_etime}, interval 1 day)
        </if>
        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,
        <if test="rukukufang != null and rukukufang != '' ">
            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,
        </if>
        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
        <where>
            <if test="mingchengguige != null and mingchengguige != '' ">
                <bind name="mingchengguigePattern" value="'%'+mingchengguige+'%'"/>
                and kucunrizhi.mingchengguige like #{mingchengguigePattern}
            </if>
            <if test="jiagongyigong != null and jiagongyigong != '' ">
                and kucunrizhi.jiagongyigong = #{jiagongyigong}
            </if>
            <if test="rukukufang != null and rukukufang != '' ">
                and kucunrizhi.rukukufang = #{rukukufang}
            </if>
            <if test="xiangmuweihu != null and xiangmuweihu != ''">
                and kucunrizhi.xiangmuweihu = #{xiangmuweihu}
            </if>
            <if test="dalei != null and dalei != '' ">
                and kucunrizhi.dalei = #{dalei}
            </if>
            <if test="s_btime != null">
                and kucunrizhi.createtime &gt;= #{s_btime}
            </if>
            <if test="s_etime != null">
                and kucunrizhi.createtime &lt;= date_add(#{s_etime}, interval 1 day)
            </if>
            <if test="createtime != null">
                and kucunrizhi.createtime = #{createtime}
            </if>
            <if test="updatetime != null">
                and kucunrizhi.updatetime = #{updatetime}
            </if>
        </where>
        GROUP BY kucunrizhi.mingchengguige
        ORDER BY createtime DESC
        )rizhi1 right outer join wuliaokucunrizhi as rizhi2 on
        rizhi1.mingchengguige = rizhi2.mingchengguige
        <where>
            <if test="mingchengguige != null and mingchengguige != '' ">
                <bind name="mingchengguigePattern" value="'%'+mingchengguige+'%'"/>
                and rizhi2.mingchengguige like #{mingchengguigePattern}
            </if>
            <if test="jiagongyigong != null and jiagongyigong != '' ">
                and rizhi2.jiagongyigong = #{jiagongyigong}
            </if>
            <if test="rukukufang != null and rukukufang != '' ">
                and rizhi2.rukukufang = #{rukukufang}
            </if>
            <if test="xiangmuweihu != null and xiangmuweihu != ''">
                and rizhi2.xiangmuweihu = #{xiangmuweihu}
            </if>
            <if test="dalei != null and dalei != '' ">
                and rizhi2.dalei = #{dalei}
            </if>
            <if test="createtime != null">
                and rizhi2.createtime = #{createtime}
            </if>
            <if test="updatetime != null">
                and rizhi2.updatetime = #{updatetime}
            </if>
        </where>
        group by rizhi2.mingchengguige
        <if test="show5Zero == null">
            having
            (qichukucun != 0)
            or (shourushuliang != 0)
            or (fachushuliang != 0)
            or (tuihuishuliang != 0)
            or (jieyushuliang != 0)
        </if>
        ORDER BY createtime
    </select>

免责声明：本站所有文章内容,图片，视频等均是来源于用户投稿和互联网及文摘转载整编而成，不代表本站观点，不承担相关法律责任。其著作权各归其原作者或其出版社所有。如发现本站有涉嫌抄袭侵权/违法违规的内容,侵犯到您的权益，请在线联系站长,一经查实,本站将立刻删除。 本文来自网络,若有侵权，请联系删除，如若转载，请注明出处：https://yundeesoft.com/34837.html

赞 (0)
老牧童老牧童
0

相关推荐

发表回复

您的电子邮箱地址不会被公开。 必填项已用 * 标注

关注微信