背景

商品表 核心字段

product
(
drug_id  text -- 商品编码
amt numeric -- 销售额
product_type text -- 商品类别
)

大致遇到的需求是这样的，目前有一个商品表，需要查出不同商品相关统计信息比如：商品销售额

select sum(sales) from statistic_store_product_month group by type

商品销售额会可能通过前端传改变的值过来计算

处理方式

目前可选两种处理方式

1. 查询出数据库集合在java内存中替换改变值然后在内存中计算
2. 通过传入参数构建一个虚表关联 produce 表，用case when 处理

接口实现核心逻辑:

public CategoryStructureVO getStatisticStoreMonthStructure(CategoryPriceTargetDTO categoryPriceTargetDTO) {
        
        StringBuilder sql = new StringBuilder(" values('', 0, 0, 0, '') ");
        Map<String, StatisticStoreProductMonth> productMonthMap = categoryPriceTargetDTO.getStatisticStoreProductMonthMap();
        if ( productMonthMap!= null && productMonthMap.size() > 0 )
            productMonthMap.forEach(
                (k, v) -> sql.append(" union all values('").append(k).append("',")
                        .append(v.getSales()).append(",")
                        .append(v.getAmt()).append(",")
                        .append(v.getGrossProfit()).append(",'")
                        .append(v.getProductType()).append("') ")
        );
        
   }

不多说大致看sql

<select id="getProductStatistical" resultType="com.sinoxk.order.protocol.response.web.category.StatisticStoreProductMonthVO">
        select a.product_type as productType,
        sum(case when b.amt != 0 then b.amt else a.amt end) as amt,
        sum(case when b.gross_profit != 0 then b.gross_profit else a.gross_profit end) as grossProfit,
        sum(order_count) orderCount
        from statistic_store_product_month a left join
        (with store_mapping(drug_id, sales, amt, gross_profit, product_type) as (${sql}) select * from store_mapping) as b on a.drug_id = b.drug_id
         WHERE a.CUSTOMER_ID = #{customerId} and month = #{month}
        <if test="storeNos != null and storeNos.size > 0">
            AND store_no in
            <foreach collection="storeNos" item="item" separator="," open="(" close=")">
                #{item}
            </foreach>
        </if>
        group by a.product_type
    </select>