sql - group by 之后获取其它要的字段并根据where选定

具体业务是这样的

一个库房中的库存是根据许多单据的生成来做更改的

如 到货单保存后,进行了库存的++, 出库单保存后,该物料相应减少-- 等.

那么在展示列中,如果有一个期初库存的字段,用来展示选定时间点时,当时库存有多少余量,并根据时间日历插件进行区间搜索.

而主要业务中的数值字段还有:

期初库存 / 到货量 / 出库量 / 退库量 / 结余数量

再解释一下,

期初库存是查取时间段后,它的上一个时间节点的结余数量

到货量即 新到的货物数量

出库量即 派送出的货物数量

退库量即 回退的货物数量

结余数量 = 期初+到货-出库+退库(量)

首先如果没有时间的区间检索,完全可以记录到一条数据元组上.

如:

在执行货品"柠檬水"的到货时,可以判断当时库存中是否有这个货品,没有执行INSERT,有则执行UPDATE,

INSERT时,将到货量记录到到货量字段,其它字段为0,(在后续的出库等操作时,进行UPDATE),

当库存本来就有"柠檬水"库存时,执行到货量字段的UPDATE,如

到货量 = 新到货量+到货量 (其它字段同样,执行累加计算,*出库量同样是累加)

如果马上要看到结余量,那么进行调用第二个方法,计算结余数量,进行结余数量字段的UPDATE.

 

现在假设有区间搜索,在搜索时,想要查看的数据时这个区间时间内的到货,出库,退库,及结余量.

 

上图展示了每条进行仓库操作时的记录数据,在区间搜索时假设时间是从22到28的,最终想要得到的结果

只有两条,即"篮球"和"甲板",并将到货量(shourushuliang),出库量(fachushuliang),退库量(tuihuishuliang)进行sum计算

那么还需要展示时间区间搜索的期初库存量,即这个区间时间开始前,库存还有多少量.

因为这个获取回来用到了mysql的GROUP BY(货品名称),在获取期初库存时需要获取到时间区间开始时间最贴近的ASC的那第一条的数值

而结余数量即时间区间结束时最贴近结束时间的DESC的那一条中的结余数量(jieyushuliang)

拿ID 26到28来说,在这个区间内,应该以 15作为返回的期初库存, 以9作为返回的结余数量.

 

结果:

插入详细数据时,需要动态获取到前一个时间点该物料的结余数量

#新增物料库存详细,并填充期初库存为它上一个日期节点的结余量
#需要insert其它字段,如rukukufang,dalei,jiagongyigong,
insert into wuliaokucunrizhi(mingchengguige,qichukucun,shourushuliang,fachushuliang,tuihuishuliang,jieyushuliang) 
values('甲板',IFNULL((select jie.jieyushuliang from (select jieyushuliang from wuliaokucunrizhi where createtime < (select now())
and mingchengguige='甲板' and rukukufang='A'and dalei='甲类'
order by createtime desc limit 1) jie),0),0,0,0,17);

这里的最后的数字17应该是动态的这里只是测试

 

获取到最终按时间区间搜索所要呈现的结果集合

#3.将上两条融合 有了搜索时间区间和其它条件的sum的集合
select 
DISTINCT(result1.mingchengguige),
substring_index(group_concat(result1.qichukucun order by createtime),',',1),
sum(shourushuliang),sum(fachushuliang),sum(tuihuishuliang),
substring_index(group_concat(result1.jieyushuliang order by createtime desc),',',1)
from(select * from wuliaokucunrizhi 
WHERE createtime >= '2019-12-27 22:02:00' 
AND createtime <= '2019-12-29 24:00:00'  AND rukukufang = 'A' AND dalei = '甲类')result1
GROUP BY mingchengguige

这里还应该将返回字段进行AS别名好映射实体类

 

最终结合mybatis 

   <select id="listKucunWithRiZhi" resultMap="BaseResultMap">
        select DISTINCT(kucunchaxun.mingchengguige)as mingchengguige,
        substring_index(group_concat(kucunchaxun.xiangmuweihu order by kucunchaxun.createtime desc),',',1)as
        xiangmuweihu,
        substring_index(group_concat(kucunchaxun.rukukufang order by kucunchaxun.createtime desc),',',1)as rukukufang,
        substring_index(group_concat(kucunchaxun.dalei order by kucunchaxun.createtime desc),',',1)as dalei,
        substring_index(group_concat(kucunrizhi.qichukucun order by kucunrizhi.createtime),',',1)qichukucun,
        sum(kucunrizhi.shourushuliang)shourushuliang,sum(kucunrizhi.fachushuliang)fachushuliang,sum(kucunrizhi.tuihuishuliang)tuihuishuliang,
        substring_index(group_concat(kucunrizhi.jieyushuliang order by kucunrizhi.createtime desc),',',1)jieyushuliang,
        substring_index(group_concat(kucunchaxun.jiagongyigong order by kucunchaxun.createtime desc),',',1)as
        jiagongyigong,
        substring_index(group_concat(kucunrizhi.createtime order by kucunrizhi.createtime desc),',',1)as createtime
        from wuliaokucunchaxun as kucunchaxun LEFT JOIN wuliaokucunrizhi as kucunrizhi ON kucunchaxun.mingchengguige =
        kucunrizhi.mingchengguige
        <where>
            <if test="mingchengguige != null and mingchengguige != '' ">
                <bind name="mingchengguigePattern" value="'%'+mingchengguige+'%'"/>
                and kucunchaxun.mingchengguige like #{mingchengguigePattern}
                and kucunrizhi.mingchengguige like #{mingchengguigePattern}
            </if>
            <if test="jiagongyigong != null and jiagongyigong != '' ">
                and kucunchaxun.jiagongyigong = #{jiagongyigong}
                and kucunrizhi.jiagongyigong = #{jiagongyigong}
            </if>
            <if test="rukukufang != null and rukukufang != '' ">
                and kucunchaxun.rukukufang = #{rukukufang}
                and kucunrizhi.rukukufang = #{rukukufang}
            </if>
            <if test="dalei != null and dalei != '' ">
                and kucunchaxun.dalei = #{dalei}
                and kucunrizhi.dalei = #{dalei}
            </if>
            <if test="createtime != null">
                and kucunrizhi.createtime = #{createtime}
            </if>
            <if test="updatetime != null">
                and kucunrizhi.updatetime = #{updatetime}
            </if>
            <if test="s_btime != null">
                and createtime &gt;= #{s_btime}
            </if>
            <if test="s_etime != null">
                and createtime &lt;= date_add(#{s_etime}, interval 1 day)
            </if>
        </where>
        GROUP BY kucunchaxun.mingchengguige
        <if test="sort == null">
            ORDER BY createtime DESC
        </if>
        <if test="sort != null and sort != ''">
            order by ${sort} ${order}
        </if>
    </select>

 

谢谢查看!