Optimize
2023-12-18 17:44:11
package huizhi.service.flower; import cn.hutool.core.collection.CollectionUtil; import cn.hutool.core.util.StrUtil; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.ArrayList; public class Optimize { public static void main(String[] args) throws SQLException, ClassNotFoundException { // allValue("1704435229558108162","10","2023-11-05","2023-11-08"); allValue("1704435229558108162",null,"",null); } /** * /** * * 查询条件: * * 地区、品类、时间区间(yyyy-MM-dd) * * * * 返回值: * * 返回上述满足条件的数据 * * 客户数量 * * 发货金额 * * 各个地区的统计:地区名称+金额 * */ // * @param baseId 基地值 // * @param areaId 地区值 // * @param localDate1 时间1 // * @param localDate2 时间2 // * @throws SQLException // * @throws ClassNotFoundException // */ public static void allValue(String baseId, String areaId, String localDate1,String localDate2) throws SQLException, ClassNotFoundException { Connection connection = ConnectionRequest.requestConnection(); Statement statement = connection.createStatement(); // String sql = "select count(DISTINCT bis_flower_order.custom_id)," + // "sum(bis_flower_order.goods_amount) from bis_flower_order " + // "LEFT JOIN bis_custom_delivery_address on bis_flower_order.custom_id = " + // "bis_custom_delivery_address.custom_id where \n" + // "bis_flower_order.base_id = 1704435229558108162 and bis_custom_delivery_address.area_id " + // "= 10 and DATE_FORMAT(bis_flower_order.place_order_time, '%Y-%m-%d')" + // " BETWEEN '2023-11-05' AND '2023-11-08';"; String sql = "select count(DISTINCT bis_flower_order.custom_id), " + "sum(bis_flower_order.goods_amount) from bis_flower_order " + "LEFT JOIN bis_custom_delivery_address on bis_flower_order.custom_id = " + "bis_custom_delivery_address.custom_id "; String sql2 = "select china_area.full_name,sum(bis_flower_order.goods_amount) " + "from bis_flower_order LEFT JOIN bis_custom_delivery_address on bis_flower_order.custom_id " + "= bis_custom_delivery_address.custom_id LEFT JOIN china_area on bis_custom_delivery_address.area_id" + " = china_area.id "; ArrayList<String> sqlString = CollectionUtil.newArrayList(); if (StrUtil.isNotEmpty(baseId)){ sqlString.add(StrUtil.format("bis_flower_order.base_id = '{}' ",baseId)); } if (StrUtil.isNotEmpty(areaId)){ sqlString.add(StrUtil.format("bis_custom_delivery_address.area_id = '{}' ",areaId)); } if (StrUtil.isAllNotEmpty(localDate1,localDate2)){ sqlString.add(StrUtil.format("DATE_FORMAT(bis_flower_order.place_order_time, '%Y-%m-%d') " + " BETWEEN '{}' AND '{}';",localDate1,localDate2)); } if (CollectionUtil.isNotEmpty(sqlString)){ sql += "where {}"; sql2 += "where {}"; sql = StrUtil.format(sql,CollectionUtil.join(sqlString," and ")); sql2 = StrUtil.format(sql2,CollectionUtil.join(sqlString," and ")); } sql2 += " GROUP BY bis_custom_delivery_address.area_id; "; ResultSet resultSet = statement.executeQuery(sql); System.out.println("基地为"+baseId+" 地区为"+areaId+" 时间范围在 "+localDate1+"-"+localDate2+"之间的数据为"); while (resultSet.next()){ System.out.println("客户数量为:"+resultSet.getString(1)); System.out.println("发货金额为:"+resultSet.getString(2)); } // System.out.println(sql2); // String sql2 ="select china_area.full_name,sum(bis_flower_order.goods_amount) " + // "from bis_flower_order LEFT JOIN bis_custom_delivery_address on bis_flower_order.custom_id " + // "= bis_custom_delivery_address.custom_id LEFT JOIN china_area on bis_custom_delivery_address.area_id = china_area.id " + // "where \n" + // "bis_flower_order.base_id = 1704435229558108162 and bis_custom_delivery_address.area_id = " + // "10 and DATE_FORMAT(bis_flower_order.place_order_time, '%Y-%m-%d')" + // " BETWEEN '202-131-05' AND '2023-11-08' GROUP BY bis_custom_delivery_address.area_id;"; ResultSet resultSet2 = statement.executeQuery(sql2); while (resultSet2.next()){ System.out.println("地区为:"+resultSet2.getString(1)); System.out.println("金额为:"+resultSet2.getString(2)); } } }
文章来源:https://blog.csdn.net/m0_65037354/article/details/135067053
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。 如若内容造成侵权/违法违规/事实不符,请联系我的编程经验分享网邮箱:veading@qq.com进行投诉反馈,一经查实,立即删除!
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。 如若内容造成侵权/违法违规/事实不符,请联系我的编程经验分享网邮箱:veading@qq.com进行投诉反馈,一经查实,立即删除!