java 如何将sql 改为一行

转载

mob64ca13f446df 2024-09-09 07:37:17

文章标签 java 如何将sql 改为一行 java Powered by 金山文档 Code Math 文章分类 Java 后端开发

一、准备工作

1.1 建表

CREATE TABLE `xy_stock_plan` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `distributor_id` int(11) NOT NULL COMMENT '经销商ID',
  `customer_id` int(11) DEFAULT NULL COMMENT '客户ID',
  `plan_code` varchar(255) DEFAULT NULL COMMENT '批次编号',
  `examine_state` tinyint(4) NOT NULL COMMENT '状态(0-草稿，1-审核中，2-通过，3-不通过)',
  `examine_start_time` datetime DEFAULT NULL COMMENT '提交审核时间',
  `examine_time` datetime DEFAULT NULL COMMENT '审批结束时间',
  `examine_user` varchar(255) DEFAULT NULL COMMENT '审批人',
  `examine_reason` varchar(500) DEFAULT NULL COMMENT '审批评价/原因',
  `distributor_name` varchar(255) DEFAULT NULL COMMENT '经销商名称',
  `product_code` varchar(255) DEFAULT NULL COMMENT '产品型号',
  `product_package` varchar(255) DEFAULT NULL COMMENT '封装',
  `unit_price_including_tax` decimal(20,6) DEFAULT NULL COMMENT '含税单价',
  `plan_month` datetime DEFAULT NULL COMMENT '月份',
  `first_half_month` int(11) DEFAULT NULL COMMENT '上半月数量',
  `latter_half_month` int(11) DEFAULT NULL COMMENT '下半月数量',
  `jan_count` int(11) DEFAULT NULL COMMENT '一月数量',
  `feb_count` int(11) DEFAULT NULL COMMENT '二月数量',
  `mar_count` int(11) DEFAULT NULL COMMENT '三月数量',
  `apr_count` int(11) DEFAULT NULL COMMENT '四月数量',
  `may_count` int(11) DEFAULT NULL COMMENT '五月数量',
  `jun_count` int(11) DEFAULT NULL COMMENT '六月数量',
  `jul_count` int(11) DEFAULT NULL COMMENT '七月数量',
  `aug_count` int(11) DEFAULT NULL COMMENT '八月数量',
  `sept_count` int(11) DEFAULT NULL COMMENT '九月数量',
  `oct_count` int(11) DEFAULT NULL COMMENT '十月数量',
  `nov_count` int(11) DEFAULT NULL COMMENT '十一月数量',
  `dec_count` int(11) DEFAULT NULL COMMENT '十二月数量',
  `aggregate_demand` bigint(20) DEFAULT NULL COMMENT '总需求',
  `amount_including_tax` decimal(20,6) DEFAULT NULL COMMENT '含税金额',
  `amount_not_tax` decimal(20,6) DEFAULT NULL COMMENT '未税金额',
  `create_by` varchar(50) DEFAULT NULL COMMENT '创建人',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_by` varchar(50) DEFAULT NULL COMMENT '更新人',
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `remark` varchar(500) DEFAULT NULL COMMENT '备注',
  `del_flag` tinyint(1) NOT NULL DEFAULT '0' COMMENT '删除标志（0代表存在 1代表删除）',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `distributor_id` (`distributor_id`),
  KEY `customer_id` (`customer_id`)
)

1.2 插入数据

INSERT INTO xysemi_test.xy_stock_plan
(id, distributor_id, customer_id, plan_code, examine_state, examine_start_time, examine_time, examine_user, examine_reason, distributor_name, product_code, product_package, unit_price_including_tax, plan_month, first_half_month, latter_half_month, jan_count, feb_count, mar_count, apr_count, may_count, jun_count, jul_count, aug_count, sept_count, oct_count, nov_count, dec_count, aggregate_demand, amount_including_tax, amount_not_tax, create_by, create_time, update_by, update_time, remark, del_flag)
VALUES(78, 68, 118, NULL, 1, '2019-02-21 17:48:59', NULL, NULL, NULL, '微科技有限公司', 'XB8886A', 'ESOP8', 0.420000, '2022-02-21 17:48:59', 55, 99, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 154, 57.240000, 50.650000, 'DT2-003', '2022-02-21 17:48:59', NULL, '2022-11-03 15:42:06', NULL, 0);

二、目标

java 如何将sql 改为一行_Powered by 金山文档

目的：将上面的计划部分数据变成下面这张图的效果，然后合并上面两个结果集

java 如何将sql 改为一行_java_02

三、java处理数据源，跑定时任务

SalesForecastTask

/**
 * 销售大数据表格-预测
 */
@Component
public class SalesForecastTask {

    private final SalesForecastService salesForecastService;

    public SalesForecastTask(SalesForecastService salesForecastService) {
        this.salesForecastService = salesForecastService;
    }

 /**
     *数据来源-计划部分--表 xy_plan_table_thirteen_two
     */
    @Scheduled(cron = "0 5 1 * * ?")
//    @Scheduled(cron = "*/5 * * * * ?")//插入数据时候测试用
    public void stockPlanFromTableThirteenTwo(){
        salesForecastService.stockPlanFromTableThirteenTwo();
    }
  /**
     * 目标表
     */
//    @Scheduled(cron = "*/5 * * * * ?")
    @Scheduled(cron = "0 13 1 * * ?")
    public void distributorSalesPlanByYearMonth() {
        salesForecastService.distributorSalesPlanByYearMonth();
    }
}

SalesForecastService

/**
 * 销售大数据表格-预测
 */
public interface SalesForecastService {

    /**
     * 13-2 数据源- 计划部分
     */
    void stockPlanFromTableThirteenTwo();

    /**
     * 目标表
     */
    void distributorSalesPlanByYearMonth();
}

SalesForecastServiceImpl

/**
 * 销售大数据表格-预测
 */
@Service
public class SalesForecastServiceImpl implements SalesForecastService {

    @Autowired
    private SalesForecastMapper salesForecastSMapper;
    /**
     *  数据源-计划部分
     */
    @Override
    public void stockPlanFromTableThirteenTwo() {
        List<stockPlanFromTableThirteenTwoPreBean> stockPlanFromTableThirteenTwoPres = salesForecastSMapper.stockPlanFromTableThirteenTwoPre();
        List<stockPlanFromTableThirteenTwoBean> stockPlanFromTableThirteenTwo = new ArrayList<>();
        for (stockPlanFromTableThirteenTwoPreBean plan : stockPlanFromTableThirteenTwoPres) {
            String planYear = plan.getPlanYear();
            String distributorId = plan.getDistributorId();
            String distributorName = plan.getDistributorName();
            String salesMan = plan.getSalesMan();
            String customerName = plan.getCustomerName();
            String productCode = plan.getProductCode();
            BigDecimal unitPriceIncludingTax = plan.getUnitPriceIncludingTax();
            BigDecimal unitPriceNotTax = plan.getUnitPriceNotTax();


            Long janPlanCount = plan.getJanPlanCount();
            Long febPlanCount = plan.getFebPlanCount();
            Long marPlanCount = plan.getMarPlanCount();
            Long aprPlanCount = plan.getAprPlanCount();
            Long mayPlanCount = plan.getMayPlanCount();
            Long junPlanCount = plan.getJunPlanCount();
            Long julPlanCount = plan.getJulPlanCount();
            Long augPlanCount = plan.getAugPlanCount();
            Long septPlanCount = plan.getSeptPlanCount();
            Long octPlanCount = plan.getOctPlanCount();
            Long novPlanCount = plan.getNovPlanCount();
            Long decPlanCount = plan.getDecPlanCount();

            BigDecimal janPlanMoney = plan.getJanPlanMoney();
            BigDecimal febPlanMoney = plan.getFebPlanMoney();
            BigDecimal marPlanMoney = plan.getMarPlanMoney();
            BigDecimal aprPlanMoney = plan.getAprPlanMoney();
            BigDecimal mayPlanMoney = plan.getMayPlanMoney();
            BigDecimal junPlanMoney = plan.getJunPlanMoney();
            BigDecimal julPlanMoney = plan.getJulPlanMoney();
            BigDecimal augPlanMoney = plan.getAugPlanMoney();
            BigDecimal septPlanMoney = plan.getSeptPlanMoney();
            BigDecimal octPlanMoney = plan.getOctPlanMoney();
            BigDecimal novPlanMoney = plan.getNovPlanMoney();
            BigDecimal decPlanMoney = plan.getDecPlanMoney();
            for (int i = 1; i < 13; i++) {
                stockPlanFromTableThirteenTwoBean bean = new stockPlanFromTableThirteenTwoBean();
                if (i == 1) {//1月
                    bean.setYearMonth(planYear + "-" + "01");
                    bean.setDistributorId(distributorId);
                    bean.setDistributorName(distributorName);
                    bean.setSalesMan(salesMan);
                    bean.setCustomerName(customerName);
                    bean.setProductCode(productCode);
                    bean.setUnitPriceIncludingTax(unitPriceIncludingTax);
                    bean.setUnitPriceNotTax(unitPriceNotTax);
                    bean.setPlanCount(Math.toIntExact(janPlanCount));
                    bean.setPlanMoney(janPlanMoney);
                    stockPlanFromTableThirteenTwo.add(bean);
                }
                if (i == 2) {//2月
                    bean.setYearMonth(planYear + "-" + "02");
                    bean.setDistributorId(distributorId);
                    bean.setDistributorName(distributorName);
                    bean.setSalesMan(salesMan);
                    bean.setCustomerName(customerName);
                    bean.setProductCode(productCode);
                    bean.setUnitPriceIncludingTax(unitPriceIncludingTax);
                    bean.setUnitPriceNotTax(unitPriceNotTax);
                    bean.setPlanCount(Math.toIntExact(febPlanCount));
                    bean.setPlanMoney(febPlanMoney);
                    stockPlanFromTableThirteenTwo.add(bean);
                }
                if (i == 3) {//3月
                    bean.setYearMonth(planYear + "-" + "03");
                    bean.setDistributorId(distributorId);
                    bean.setDistributorName(distributorName);
                    bean.setSalesMan(salesMan);
                    bean.setCustomerName(customerName);
                    bean.setProductCode(productCode);
                    bean.setUnitPriceIncludingTax(unitPriceIncludingTax);
                    bean.setUnitPriceNotTax(unitPriceNotTax);
                    bean.setPlanCount(Math.toIntExact(marPlanCount));
                    bean.setPlanMoney(marPlanMoney);
                    stockPlanFromTableThirteenTwo.add(bean);
                }
                if (i == 4) {//4月
                    bean.setYearMonth(planYear + "-" + "04");
                    bean.setDistributorId(distributorId);
                    bean.setDistributorName(distributorName);
                    bean.setSalesMan(salesMan);
                    bean.setCustomerName(customerName);
                    bean.setProductCode(productCode);
                    bean.setUnitPriceIncludingTax(unitPriceIncludingTax);
                    bean.setUnitPriceNotTax(unitPriceNotTax);
                    bean.setPlanCount(Math.toIntExact(aprPlanCount));
                    bean.setPlanMoney(aprPlanMoney);
                    stockPlanFromTableThirteenTwo.add(bean);
                }
                if (i == 5) {//5月
                    bean.setYearMonth(planYear + "-" + "05");
                    bean.setDistributorId(distributorId);
                    bean.setDistributorName(distributorName);
                    bean.setSalesMan(salesMan);
                    bean.setCustomerName(customerName);
                    bean.setProductCode(productCode);
                    bean.setUnitPriceIncludingTax(unitPriceIncludingTax);
                    bean.setUnitPriceNotTax(unitPriceNotTax);
                    bean.setPlanCount(Math.toIntExact(mayPlanCount));
                    bean.setPlanMoney(mayPlanMoney);
                    stockPlanFromTableThirteenTwo.add(bean);
                }
                if (i == 6) {//6月
                    bean.setYearMonth(planYear + "-" + "06");
                    bean.setDistributorId(distributorId);
                    bean.setDistributorName(distributorName);
                    bean.setSalesMan(salesMan);
                    bean.setCustomerName(customerName);
                    bean.setProductCode(productCode);
                    bean.setUnitPriceIncludingTax(unitPriceIncludingTax);
                    bean.setUnitPriceNotTax(unitPriceNotTax);
                    bean.setPlanCount(Math.toIntExact(junPlanCount));
                    bean.setPlanMoney(junPlanMoney);
                    stockPlanFromTableThirteenTwo.add(bean);
                }
                if (i == 7) {//7月
                    bean.setYearMonth(planYear + "-" + "07");
                    bean.setDistributorId(distributorId);
                    bean.setDistributorName(distributorName);
                    bean.setSalesMan(salesMan);
                    bean.setCustomerName(customerName);
                    bean.setProductCode(productCode);
                    bean.setUnitPriceIncludingTax(unitPriceIncludingTax);
                    bean.setUnitPriceNotTax(unitPriceNotTax);
                    bean.setPlanCount(Math.toIntExact(julPlanCount));
                    bean.setPlanMoney(julPlanMoney);
                    stockPlanFromTableThirteenTwo.add(bean);
                }
                if (i == 8) {//8月
                    bean.setYearMonth(planYear + "-" + "08");
                    bean.setDistributorId(distributorId);
                    bean.setDistributorName(distributorName);
                    bean.setSalesMan(salesMan);
                    bean.setCustomerName(customerName);
                    bean.setProductCode(productCode);
                    bean.setUnitPriceIncludingTax(unitPriceIncludingTax);
                    bean.setUnitPriceNotTax(unitPriceNotTax);
                    bean.setPlanCount(Math.toIntExact(augPlanCount));
                    bean.setPlanMoney(augPlanMoney);
                    stockPlanFromTableThirteenTwo.add(bean);
                }
                if (i == 9) {//9月
                    bean.setYearMonth(planYear + "-" + "09");
                    bean.setDistributorId(distributorId);
                    bean.setDistributorName(distributorName);
                    bean.setSalesMan(salesMan);
                    bean.setCustomerName(customerName);
                    bean.setProductCode(productCode);
                    bean.setUnitPriceIncludingTax(unitPriceIncludingTax);
                    bean.setUnitPriceNotTax(unitPriceNotTax);
                    bean.setPlanCount(Math.toIntExact(septPlanCount));
                    bean.setPlanMoney(septPlanMoney);
                    stockPlanFromTableThirteenTwo.add(bean);
                }
                if (i == 10) {//10月
                    bean.setYearMonth(planYear + "-" + "10");
                    bean.setDistributorId(distributorId);
                    bean.setDistributorName(distributorName);
                    bean.setSalesMan(salesMan);
                    bean.setCustomerName(customerName);
                    bean.setProductCode(productCode);
                    bean.setUnitPriceIncludingTax(unitPriceIncludingTax);
                    bean.setUnitPriceNotTax(unitPriceNotTax);
                    bean.setPlanCount(Math.toIntExact(octPlanCount));
                    bean.setPlanMoney(octPlanMoney);
                    stockPlanFromTableThirteenTwo.add(bean);
                }
                if (i == 11) {//11月
                    bean.setYearMonth(planYear + "-" + "11");
                    bean.setDistributorId(distributorId);
                    bean.setDistributorName(distributorName);
                    bean.setSalesMan(salesMan);
                    bean.setCustomerName(customerName);
                    bean.setProductCode(productCode);
                    bean.setUnitPriceIncludingTax(unitPriceIncludingTax);
                    bean.setUnitPriceNotTax(unitPriceNotTax);
                    bean.setPlanCount(Math.toIntExact(octPlanCount));
                    bean.setPlanCount(Math.toIntExact(novPlanCount));
                    bean.setPlanMoney(novPlanMoney);
                    stockPlanFromTableThirteenTwo.add(bean);
                }
                if (i == 12) {//12月
                    bean.setYearMonth(planYear + "-" + "12");
                    bean.setDistributorId(distributorId);
                    bean.setDistributorName(distributorName);
                    bean.setSalesMan(salesMan);
                    bean.setCustomerName(customerName);
                    bean.setProductCode(productCode);
                    bean.setUnitPriceIncludingTax(unitPriceIncludingTax);
                    bean.setUnitPriceNotTax(unitPriceNotTax);
                    bean.setPlanCount(Math.toIntExact(decPlanCount));
                    bean.setPlanMoney(decPlanMoney);
                    stockPlanFromTableThirteenTwo.add(bean);
                }
            }
        }
        if (stockPlanFromTableThirteenTwo.size() > 0) {
            salesForecastSMapper.truncateDataByTableName("xy_plan_table_thirteen_two");
            int records = salesForecastSMapper.insertstockPlanFromTableThirteenTwo(stockPlanFromTableThirteenTwo);
            System.out.println("13-2 数据源计划部分记录数" + records);
        }
    }
}

涉及的类：

stockPlanFromTableThirteenTwoPreBean

/**
 * 数据源计划部分
 * @author xhx
 * @date 2023-01-17 15:25
 */
@Data
public class stockPlanFromTableThirteenTwoPreBean {

    /**
     * 计划年
     */
    private String planYear;

    /**
     * 经销商id
     */
    private String distributorId;

    /**
     * 经销商名称
     */
    private String distributorName;

    /**
     * 业务员名称
     */
    private String salesMan;

    /**
     * 客户名称
     */
    private String customerName;

    /**
     * 出货型号-即产品型号
     */
    private String productCode;

    /**
     * 含税单价-计划
     */
    private BigDecimal unitPriceIncludingTax;

    /**
     * 不含税单价-计划
     */
    private BigDecimal unitPriceNotTax;



    /**
     * 一到十二个月计划出货数量
     */
    private Long     janPlanCount    ;
    private Long     febPlanCount    ;
    private Long     marPlanCount    ;
    private Long     aprPlanCount    ;
    private Long     mayPlanCount    ;
    private Long     junPlanCount    ;
    private Long     julPlanCount    ;
    private Long     augPlanCount    ;
    private Long     septPlanCount   ;
    private Long     octPlanCount    ;
    private Long     novPlanCount    ;
    private Long     decPlanCount    ;

    /**
     * 一到十二个月即贷款金额（含税）
     */
    private BigDecimal janPlanMoney ;
    private BigDecimal  febPlanMoney ;
    private BigDecimal  marPlanMoney ;
    private BigDecimal  aprPlanMoney ;
    private BigDecimal  mayPlanMoney ;
    private BigDecimal  junPlanMoney ;
    private BigDecimal  julPlanMoney ;
    private BigDecimal  augPlanMoney ;
    private BigDecimal  septPlanMoney;
    private BigDecimal  octPlanMoney ;
    private BigDecimal  novPlanMoney ;
    private BigDecimal  decPlanMoney ;
}

stockPlanFromTableThirteenTwoBean

package com.xysemi.distributor.domain.bean;

import lombok.Data;

import java.math.BigDecimal;

/**
 *
 *  数据源（计划和实际部分）
 * @author xhx
 * @date 2023-01-17 15:27
 */
@Data
public class stockPlanFromTableThirteenTwoBean {

    /**
     * id
     */
    private String id;

    /**
     * 计划年月
     */
    private String yearMonth;

    /**
     * 经销商Id
     */
    private String distributorId;

    /**
     * 经销商名称
     */
    private String distributorName;

    /**
     * 业务员名称
     */
    private String salesMan;

    /**
     * 客户名称
     */
    private String customerName;

    /**
     * 出货型号-即产品型号
     */
    private String productCode;

    /**
     * 含税单价-计划
     */
    private BigDecimal unitPriceIncludingTax;

    /**
     * 不含税单价-计划
     */
    private BigDecimal unitPriceNotTax;

    /**
     * 计划数量-即表13-2的数量字段
     */
    private Integer planCount;


    /**
     * 贷款金额（含税）
     */
    private BigDecimal planMoney;

}

SalesForecastMapper

/**
 * 销售大数据表格-预测
 */
public interface SalesForecastMapper {
    
    /**
     * 数据源-计划
     * @return
     */
    List<stockPlanFromTableThirteenTwoPreBean> stockPlanFromTableThirteenTwoPre();

 /**
     *  数据源-计划  插入数据
     * @return
     */
    int insertstockPlanFromTableThirteenTwo(@Param("list") List<stockPlanFromTableThirteenTwoBean> stockPlanFromTableThirteenTwoBeanList);

    /**
     * 根据表名清空表数据
     */
    void truncateDataByTableName(@Param("name") String tName);

 /**
     * 从数据库获取目标表的数据
     *
     * @return 返回目标表的数据
     */
    List<DistributorSalesPlanByYearMonthBean> distributorSalesPlanByYearMonth();

    /**
     * 把目标表的数据插入到数据库的表
     *
     * @return 返回目标表的数据distributorSalesPlanByYearMonthBeanList
     */
    int insertDistributorSalesPlanByYearMonthBeans(@Param("list") List<DistributorSalesPlanByYearMonthBean> distributorSalesPlanByYearMonthBeans);

}

SalesForecastMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.xysemi.distributor.mapper.SalesForecastMapper">
 <!--  数据源-计划部分 映射关系-->
 <resultMap type="StockPlanFromTableThirteenTwoPreBean" id="stockPlanFromTableThirteenTwoPreMapping">
        <result property="planYear" column="plan_year"/>
        <result property="distributorId" column="distributor_id"/>
        <result property="distributorName" column="distributor_name"/>
        <result property="salesMan" column="salesman"/>
        <result property="customerName" column="customer_name"/>
        <result property="productCode" column="product_code"/>
        <result property="unitPriceIncludingTax" column="unit_price_including_tax"/>
        <result property="unitPriceNotTax" column="unit_price_not_tax"/>
        <result property="janPlanMoney" column="jan_plan_money"/>
        <result property="janPlanCount" column="jan_plan_count"/>
        <result property="febPlanMoney" column="feb_plan_money"/>
        <result property="febPlanCount" column="feb_plan_count"/>
        <result property="marPlanMoney" column="mar_plan_money"/>
        <result property="marPlanCount" column="mar_plan_count"/>
        <result property="aprPlanMoney" column="apr_plan_money"/>
        <result property="aprPlanCount" column="apr_plan_count"/>
        <result property="mayPlanMoney" column="may_plan_money"/>
        <result property="mayPlanCount" column="may_plan_count"/>
        <result property="junPlanMoney" column="jun_plan_money"/>
        <result property="junPlanCount" column="jun_plan_count"/>
        <result property="julPlanMoney" column="jul_plan_money"/>
        <result property="julPlanCount" column="jul_plan_count"/>
        <result property="augPlanMoney" column="aug_plan_money"/>
        <result property="augPlanCount" column="aug_plan_count"/>
        <result property="septPlanMoney" column="sept_plan_money"/>
        <result property="septPlanCount" column="sept_plan_count"/>
        <result property="octPlanMoney" column="oct_plan_money"/>
        <result property="octPlanCount" column="oct_plan_count"/>
        <result property="novPlanMoney" column="nov_plan_money"/>
        <result property="novPlanCount" column="nov_plan_count"/>
        <result property="decPlanMoney" column="dec_plan_money"/>
        <result property="decPlanCount" column="dec_plan_count"/>
    </resultMap>
    <sql id="stockPlanFromTableThirteenTwoPreQuerySql">
        <!-- 数据源 计划部分-->
        select
        DATE_FORMAT(plan_month,'%Y')  plan_year
        ,xd.distributor_id
        ,xd.enterprise_name distributor_name
        ,su.nick_name salesman
        ,xc.customer_name
        ,xsp.product_code
        ,round(xsp.unit_price_including_tax,2) unit_price_including_tax
        ,round(xsp.unit_price_including_tax / 1.13,2) unit_price_not_tax
        ,sum(xsp.jan_count) jan_plan_count
        ,round(sum((xsp.unit_price_including_tax * xsp.jan_count)),2) jan_plan_money
        ,sum(xsp.feb_count) feb_plan_count
        ,round(sum((xsp.unit_price_including_tax * xsp.feb_count)),2) feb_plan_money
        ,sum(xsp.mar_count) mar_plan_count
        ,round(sum((xsp.unit_price_including_tax * xsp.mar_count)),2) mar_plan_money
        ,sum(xsp.apr_count) apr_plan_count
        ,round(sum((xsp.unit_price_including_tax * xsp.apr_count)),2) apr_plan_money
        ,sum(xsp.may_count) may_plan_count
        ,round(sum((xsp.unit_price_including_tax * xsp.may_count)),2) may_plan_money
        ,sum(xsp.jun_count) jun_plan_count
        ,round(sum((xsp.unit_price_including_tax * xsp.jun_count)),2) jun_plan_money
        ,sum(xsp.jul_count) jul_plan_count
        ,round(sum((xsp.unit_price_including_tax * xsp.jul_count)),2) jul_plan_money
        ,sum(xsp.aug_count) aug_plan_count
        ,round(sum((xsp.unit_price_including_tax * xsp.aug_count)),2) aug_plan_money
        ,sum(xsp.sept_count) sept_plan_count
        ,round(sum((xsp.unit_price_including_tax * xsp.sept_count)),2) sept_plan_money
        ,sum(xsp.oct_count) oct_plan_count
        ,round(sum((xsp.unit_price_including_tax * xsp.oct_count)),2) oct_plan_money
        ,sum(xsp.nov_count) nov_plan_count
        ,round(sum((xsp.unit_price_including_tax * xsp.nov_count)),2) nov_plan_money
        ,sum(xsp.dec_count) dec_plan_count
        ,round(sum((xsp.unit_price_including_tax * xsp.dec_count)),2) dec_plan_money
        FROM
        xy_stock_plan xsp
        left join xy_distributor xd
        on xsp.distributor_id = xd.distributor_id
        LEFT JOIN sys_user su
        on xd.salesman_id = su.user_id
        LEFT JOIN xy_customers xc
        on xsp.customer_id = xc.id
        WHERE xsp.del_flag = 0
        AND xc.customer_name IS NOT NULL
        AND xsp.jan_count IS NOT NULL
        AND product_code IS NOT NULL
        AND xsp.examine_state = 2
        group by xsp.distributor_id,xsp.product_code,DATE_FORMAT(xsp.plan_month,'%Y')
    </sql>
<resultMap type="DistributorSalesPlanByYearMonthBean" id="distributorSalesPlanByYearMonthBeanResult">
        <result property="yearMonth" column="year_month"/>
        <result property="distributorId" column="distributor_id"/>
        <result property="enterpriseName" column="enterprise_name"/>
        <result property="salesMan" column="salesman"/>
        <result property="customerName" column="customer_name"/>
        <result property="productCode" column="product_code"/>
        <result property="quantity" column="quantity"/>
        <result property="unitPriceIncludingTax" column="unit_price_including_tax"/>
        <result property="unitPriceNotTax" column="unit_price_not_tax"/>
        <result property="amountIncludingTax" column="amount_including_tax"/>
        <result property="salesNotTax" column="sales_not_tax"/>
    </resultMap>
 <sql id="distributorSalesPlanByYearMonthSql">
        -- 13-2 third version
        select
        t1.year_month
        ,t1.distributor_id
        ,t1.distributor_name enterprise_name
        ,t1.salesman
        ,t1.customer_name
        ,t1.product_code
        ,IFNULL(t1.unit_price_including_tax,0) unit_price_including_tax
        ,IFNULL(t1.unit_price_not_tax,0) unit_price_not_tax
        ,IFNULL(t1.plan_count,0) quantity
        ,IFNULL(t1.plan_money,0) amount_including_tax
        ,IFNULL(t2.total_price_not_tax_sum,0)  sales_not_tax
        FROM xy_plan_table_thirteen_two t1
        left join
        (
        SELECT
        DATE_FORMAT(t2.inbound_date,'%Y-%m') inbound_date ,
        t2.distributor_name ,
        t1.product_model ,
        -- sum(t1.inbound_quantity) inbound_quantity_sum ,
        sum(t1.total_price_not_tax) total_price_not_tax_sum
        from xy_erp_inbound_info t1
        left join xy_erp_inbound t2 on t1.inbound_id = t2.id
        where t2.inbound_date is not null
        AND t1.del_flag = 0
        and t2.del_flag = 0
        and t2.inbound_type = 1
        group by
        DATE_FORMAT(t2.inbound_date,'%Y-%m') ,
        t2.distributor_name ,
        t1.product_model
        order by DATE_FORMAT(t2.inbound_date,'%Y-%m') DESC
        )t2
        ON t1.year_month = t2.inbound_date AND t1.distributor_name = t2.distributor_name AND t1.product_code = t2.product_model
        ORDER BY t1.year_month
</sql>
<select id="stockPlanFromTableThirteenTwoPre" resultMap="stockPlanFromTableThirteenTwoPreMapping">
        <include refid="stockPlanFromTableThirteenTwoPreQuerySql"/>
    </select>
 <select id="distributorSalesPlanByYearMonth" resultMap="distributorSalesPlanByYearMonthBeanResult">
        <include refid="distributorSalesPlanByYearMonthSql"/>
    </select>
 <delete id="truncateDataByTableName" parameterType="String">
        truncate ${name}
    </delete>
 <insert id="insertstockPlanFromTableThirteenTwo" parameterType="java.util.List" useGeneratedKeys="true" keyProperty="id">
        insert into  xy_plan_table_thirteen_two
        (
        `year_month`
        ,distributor_id
        ,distributor_name
        ,salesman
        ,customer_name
        ,product_code
        ,unit_price_including_tax
        ,unit_price_not_tax
        ,plan_count
        ,plan_money
        )
        values
        <foreach collection="list" item="bean" index="index" separator=",">
            (
            #{bean.yearMonth},
            #{bean.distributorId},
            #{bean.distributorName},
            #{bean.salesMan},
            #{bean.customerName},
            #{bean.productCode},
            #{bean.unitPriceIncludingTax},
            #{bean.unitPriceNotTax},
            #{bean.planCount},
            #{bean.planMoney}
            )
        </foreach>
    </insert>
 <insert id="insertDistributorSalesPlanByYearMonthBeans" parameterType="java.util.List" useGeneratedKeys="true"
            keyProperty="id">
        insert into stat_distributor_sales_plan
        (
        `year_month`
        ,distributor_id
        ,enterprise_name
        ,salesman
        ,customer_name
        ,product_code
        ,quantity
        ,unit_price_including_tax
        ,unit_price_not_tax
        ,amount_including_tax
        ,sales_not_tax
        )
        values
        <foreach collection="list" item="bean" index="index" separator=",">
            (
            #{bean.yearMonth},
            #{bean.distributorId},
            #{bean.enterpriseName},
            #{bean.salesMan},
            #{bean.customerName},
            #{bean.productCode},
            #{bean.quantity},
            #{bean.unitPriceIncludingTax},
            #{bean.unitPriceNotTax},
            #{bean.amountIncludingTax},
            #{bean.salesNotTax}
            )
        </foreach>
</mapper>

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。

上一篇：HMM CRF CNN RNN 属于文本分块能力技术

下一篇：mysql中 signed

提问和评论都可以，用心的回复会被更多人看到评论

发布评论

相关文章

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	51CTO学堂
51CTO	软考资讯

java 如何将sql 改为一行

java 如何将sql 改为一行

一、准备工作

1.1 建表

1.2 插入数据

二、目标

三、java处理数据源，跑定时任务

SalesForecastTask

SalesForecastService

SalesForecastServiceImpl

stockPlanFromTableThirteenTwoPreBean

stockPlanFromTableThirteenTwoBean

SalesForecastMapper

SalesForecastMapper.xml

51CTO博客