大家好，欢迎来到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 >= #{s_btime} </if> <if test="s_etime != null"> and kucunrizhi.createtime <= 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 >= #{s_btime} and rizhi2.createtime >= 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 >= #{s_btime} and rizhi2.createtime >= 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 >= #{s_btime} </if> <if test="s_etime != null"> and kucunrizhi.createtime <= 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
