Echarts图表 年 季 月 日 统计，sql查询 ，后台xml写法

根据 年 月 日  季 筛选统计日期

查询sql语句

相关字段 f_position '衡器名称'，  f_reality_in_date时间， f_suttle净重。

SELECT f_position '衡器名称', 
YEAR(f_reality_in_date)'年',
QUARTER(f_reality_in_date)'季',
MONTH(f_reality_in_date)'月',
DAY(f_reality_in_date)'日',
cast(sum(f_suttle)as DECIMAL(15,3))'净重'
 from truck_scale_measurement WHERE f_reality_in_date IS NOT NULL  
GROUP BY YEAR(f_reality_in_date),MONTH(f_reality_in_date) ,
DAY(f_reality_in_date) ,QUARTER(f_reality_in_date),f_position
 ORDER BY f_position, YEAR(f_reality_in_date),
MONTH(f_reality_in_date) ,DAY(f_reality_in_date)

效果展示

 java后台xml写法

   // 汽车衡计量（三级看板-统计各计量衡净重）
    @Select("SELECT f_position 'positionName', \n" +
            "CASE \n" +
            "\tWHEN #{fRealityDate}='1' THEN YEAR(f_reality_in_date)\n" +
            "\tWHEN #{fRealityDate}='2' THEN QUARTER(f_reality_in_date)\n" +
            "\tWHEN #{fRealityDate}='3' THEN MONTH(f_reality_in_date)\n" +
            "\tELSE\n" +
            "\t\tDAY(f_reality_in_date)\n" +
            "END 'dateName',\n" +
            "cast(sum(f_suttle)as DECIMAL(15,3))'suttleNum' \n" +
            "from truck_scale_measurement \n" +
            "${ew.customSqlSegment}\n" +     //注意相当于wehere   在serviceimpl中（查询条件）
            "GROUP BY f_position,\n" +
            "CASE \n" +
            "\tWHEN #{fRealityDate}='1' THEN YEAR(f_reality_in_date)\n" +
            "\tWHEN #{fRealityDate}='2' THEN QUARTER(f_reality_in_date)\n" +
            "\tWHEN #{fRealityDate}='3' THEN MONTH(f_reality_in_date)\n" +
            "\tELSE\n" +
            "\t\t\tDAY(f_reality_in_date)\n" +
            "END\n" +
            "ORDER BY f_position,\n" +
            "CASE \n" +
            "\tWHEN #{fRealityDate}='1' THEN YEAR(f_reality_in_date)\n" +
            "\tWHEN #{fRealityDate}='2' THEN QUARTER(f_reality_in_date)\n" +
            "\tWHEN #{fRealityDate}='3' THEN MONTH(f_reality_in_date)\n" +
            "\tELSE\n" +
            "\t\t\tDAY(f_reality_in_date)\n" +
            "END \n")
    List<Map> positionSuttleInfo(@Param(Constants.WRAPPER) QueryWrapper<TruckScaleMeasurementEntity> query,
                                 @Param("fRealityDate") String fRealityDate);

Serviceimpl中代码

    public Map positionSuttleInfo(Map<String, Object> params) {
        Map result=new HashMap();
       QueryWrapper<TruckScaleMeasurementEntity> query= new QueryWrapper<>();
       String fRealityDateType= (String) params.get("fRealityDateType");//年季月（1.2.3）
       String fRealityInDate= (String) params.get("fRealityInDate");//进厂时间
       String fBillType= (String) params.get("fBillType");//进厂类型
       String fPosition= (String) params.get("fPosition");//计量点（秤号名称）
        if (fPosition != null&&fPosition.length()>0) {
            query=query.like("f_position",fPosition);
        }
        //入场时间不为空isnotnull
        query=query.isNotNull("f_reality_in_date");
        if (fRealityInDate != null&&fRealityInDate.length()>0) {
            query=query.like("f_reality_in_date",fRealityInDate);
        }
        if (fBillType != null&&fBillType.length()>0) {
            query=query.eq("f_bill_type",fBillType);
        }

        List<Map> list = this.getBaseMapper().positionSuttleInfo(
                query,fRealityDateType//传入到sql中的值
        );
        result.put("list",list);
        return result;
    }

postman测试

注意数据传值

年
 "fRealityDateType": "1",
    "fRealityInDate":""
季
 "fRealityDateType": "2",
    "fRealityInDate":"2021"（yyyy-MM-dd）
月
 "fRealityDateType": "3",
    "fRealityInDate":"2021"（yyyy-MM-dd）
日
 "fRealityDateType": "",
    "fRealityInDate":"2021-11"（yyyy-MM-dd）