根据 年 月 日 季 筛选统计日期
查询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)