需求,原型图如下:
最后,做出来的效果如图:
需求是,做个列表功能,名字叫第一次订单信息统计表。列出客户的第一次下单的信息(下单包括订单,生产单,出货单)。
当接到这个需求的时候,蛮高兴的,因为好久没有接这么简单的需求了。然而我还是太年轻了。仔细一想,客户编号,客户名称,一个表,这个简单。下订单日期,下订单号,两个表(橱柜订单表,门板订单表,这两个表的字段),客户有可能在橱柜下单,也有可能在门板下单,但不管怎么样,在两个表中要找出最早的下单。
下订单金额(门板报价表,橱柜报价表两个表中的一个字段,两个订单表与两个报价表关联的字段是订单号),找出最早的订单之后,要找出它对应的订单金额。
比如在橱柜订单表找到了最早的订单,那么根据关联的是订单号,在橱柜报价表找到相应的下订单金额的值,
如果在门板订单表找到了最早的订单,那么根据关联的是订单号,在门板报价表找到相应的下订单金额的值。
那么问题来了,找出的最早订单怎么知道是在橱柜订单表还是在门板订单表。这个让我纠结了一下。
后面的下生产单日期,下生产单单号(三个表,橱柜生产单表,门板生产单表,单门板生产单表),下生产单金额(一样也在门板报价表,橱柜报价表,三个生产单表与两个报价表关联的字段是订单号,门板和单门板都属于门板,但这两个表的字段各不相同,具体不说了)。
出货日期,出货订单号(两个表,门板出库表,单门板出库表),出货金额(门板报价表,橱柜报价表,跟出库表关联的是订单号)。
列出来的这些表,刚好是10个表,后续需求增加了,有新的表,这个暂且不说。
客户表跟订单表,生产单表,出货表,这三个关联字段是客户编号,
订单表,生产单表,出货表,这三个跟报价表关联的都是订单号,
订单表要找出最早日期的,找出来之后,要找出对应的金额。
生产单表要找出最早日期的,找出来之后,要找出对应的金额。
出货表表要找出最早日期的,找出来之后,要找出对应的金额。
说了一大堆,终于把老板提出的需求讲完了。
简单一句话,假如客户先后下了订单A,B,C三个,接着先后下了B,C两个生产单,最后下了C出货单,那么列表出来的,订单是A,生产单是B,出货单是C,另外显示对应的金额。一句话概括了需求。一开始想着用SQL查出来,不过,那样会很麻烦,SQL不好写,因为,假如找出最早的订单,怎么才能知道这个订单是橱柜的,还是门板的,因为还要找出对应的报价金额。前面也说了,
比如在橱柜订单表找到了最早的订单,那么根据关联的是订单号,在橱柜报价表找到相应的下订单金额的值,
如果在门板订单表找到了最早的订单,那么根据关联的是订单号,在门板报价表找到相应的下订单金额的值。
用if来判断吗?每个都这样,会很冗余。事后证明,确实是,SQL会更长。
问了个同事,同事让我查出想要的字段,然后再构建对象,再用后台代码实现。我一听,哭笑不得。这得多复杂啊。
觉得这个方法不可行,行不通。还是通过SQL找出来。于是分割三部分,
先找出订单日期,订单号和订单金额,接着找出下生产单日期,下生产单号,下生产单金额,最后是出货表。
只要找出最早的订单日期,这个部分搞定了,那么剩下两个的依葫芦画瓢。
中途写SQL花了点时间,费脑细胞,没有写if这样的东西,问了另外一个同事,他教我用row_number ()函数,最后写出来了。
select cus_ord_quo_pro_stk.customer_no,cus_ord_quo_pro_stk.short_name,
cus_ord_quo_pro_stk.created_date_time,cus_ord_quo_pro_stk.ord_no1,cus_ord_quo_pro_stk.actual_amount1
from (
select row_number () over(partition by c.customer_no order by order_quo.created_date_time) ranknum,
c.customer_no,c.short_name,order_quo.created_date_time,order_quo.ord_no1,order_quo.actual_amount1
from customer c left join
(select ord.customer_no,ord.created_date_time,ord.order_no ord_no1,quo_order.order_no,quo_order.actual_amount actual_amount1 from
(
select d.customer_no,d.created_date_time,d.order_no from dp_order d where d.frozen = false
union
select s.customer_no,s.created_date_time,s.order_no from split_order s
)ord inner join
(select qu.order_no,quo.actual_amount
from quotation_unified_order qu
inner join quotation_unified_order_quotation_unified_bodies quo
on qu.id = quo.quotation_unified_order_id
union
select dqo.order_no,dqo.final_amount from dp_quotation_order dqo) quo_order
on ord.order_no = quo_order.order_no ) order_quo
on c.customer_no = order_quo.customer_no
) cus_ord_quo_pro_stk where ranknum =1
查询出来的结果如下:
这个是第一部分的SQL语句
接着依葫芦画瓢,写第二个SQL语句
select cus_ord_quo_pro_stk.customer_no,cus_ord_quo_pro_stk.short_name,
cus_ord_quo_pro_stk.make_date,cus_ord_quo_pro_stk.ord_no2,cus_ord_quo_pro_stk.actual_amount2
from (
select row_number () over(partition by c.customer_no order by production_quo.make_date) ranknum,
c.customer_no,c.short_name,production_quo.make_date,production_quo.ord_no2,production_quo.actual_amount2
from customer c left join
(select production.customer_no,production.make_date,production.order_no ord_no2,quo_order.actual_amount actual_amount2 from
(
select g.ref_order_no,g.customer_no,g.make_date,g.order_no from general_dp_pp_order g where g.frozen = false
union
select p.ref_order_no,p.customer_no,p.document_make_date,p.order_no from prod_process_cabinet_order p
union
select pr.ref_order_no,pr.customer_no,pr.document_make_date,pr.order_no from prod_process_door_panel_order pr
)production inner join
(select qu.order_no,quo.actual_amount
from quotation_unified_order qu
left join quotation_unified_order_quotation_unified_bodies quo
on qu.id = quo.quotation_unified_order_id
union
select dqo.order_no,dqo.final_amount from dp_quotation_order dqo) quo_order
on production.ref_order_no = quo_order.order_no ) production_quo
on c.customer_no = production_quo.customer_no
) cus_ord_quo_pro_stk where ranknum =1
查询出来的结果如下:
最后是第三个SQL的部分
select cus_ord_quo_pro_stk.customer_no,cus_ord_quo_pro_stk.short_name,
cus_ord_quo_pro_stk.stock_out_date,cus_ord_quo_pro_stk.ord_no,cus_ord_quo_pro_stk.actual_amount from (
select row_number () over(partition by c.customer_no order by stock_quo.stock_out_date) ranknum,
c.customer_no,c.short_name,stock_quo.stock_out_date,stock_quo.ord_no,stock_quo.actual_amount
from customer c left join
(select stock.customer_no,stock.stock_out_date,stock.order_no ord_no,quo_order.actual_amount from
(
select g_stock.customer_no,g_stock.stock_out_date,g_stock.order_no from gene_dp_stock_out_order g_stock where g_stock.frozen = false
union
select s_stock.customer_no,s_stock.stock_out_date,s_stock.order_no from stock_out_order s_stock
)stock inner join
(select qu.order_no,quo.actual_amount
from quotation_unified_order qu
left join quotation_unified_order_quotation_unified_bodies quo
on qu.id = quo.quotation_unified_order_id
union
select dqo.order_no,dqo.final_amount from dp_quotation_order dqo) quo_order
on stock.order_no = quo_order.order_no ) stock_quo
on c.customer_no = stock_quo.customer_no
) cus_ord_quo_pro_stk where ranknum =1
查询出来的结果如下:
最后,把这3个SQL语句拼接起来,弄成一条SQL
SQL:
select cus_ord_quo_pro_stk.customer_no,cus_ord_quo_pro_stk.short_name,
cus_ord_quo_pro_stk.created_date_time,cus_ord_quo_pro_stk.ord_no1,cus_ord_quo_pro_stk.actual_amount1,
cus_ord_quo_pro_stk.make_date,cus_ord_quo_pro_stk.ord_no2,cus_ord_quo_pro_stk.actual_amount2,
cus_ord_quo_pro_stk.stock_out_date,cus_ord_quo_pro_stk.ord_no,cus_ord_quo_pro_stk.actual_amount from (
select row_number () over(partition by c.customer_no order by order_quo.created_date_time) ranknum,
c.customer_no,c.short_name,order_quo.created_date_time,order_quo.ord_no1,order_quo.actual_amount1,
production_quo.make_date,production_quo.ord_no2,production_quo.actual_amount2,stock_quo.stock_out_date,stock_quo.ord_no,stock_quo.actual_amount
from customer c left join
(select ord.customer_no,ord.created_date_time,ord.order_no ord_no1,quo_order.order_no,quo_order.actual_amount actual_amount1 from
(
select d.customer_no,d.created_date_time,d.order_no from dp_order d where d.frozen = false
union
select s.customer_no,s.created_date_time,s.order_no from split_order s
)ord inner join
(select qu.order_no,quo.actual_amount
from quotation_unified_order qu
inner join quotation_unified_order_quotation_unified_bodies quo
on qu.id = quo.quotation_unified_order_id
union
select dqo.order_no,dqo.final_amount from dp_quotation_order dqo) quo_order
on ord.order_no = quo_order.order_no ) order_quo
on c.customer_no = order_quo.customer_no
left join
(select production.customer_no,production.make_date,production.order_no ord_no2,quo_order.actual_amount actual_amount2 from
(
select g.ref_order_no,g.customer_no,g.make_date,g.order_no from general_dp_pp_order g where g.frozen = false
union
select p.ref_order_no,p.customer_no,p.document_make_date,p.order_no from prod_process_cabinet_order p
union
select pr.ref_order_no,pr.customer_no,pr.document_make_date,pr.order_no from prod_process_door_panel_order pr
)production inner join
(select qu.order_no,quo.actual_amount
from quotation_unified_order qu
left join quotation_unified_order_quotation_unified_bodies quo
on qu.id = quo.quotation_unified_order_id
union
select dqo.order_no,dqo.final_amount from dp_quotation_order dqo) quo_order
on production.ref_order_no = quo_order.order_no ) production_quo
on c.customer_no = production_quo.customer_no
left join
(select stock.customer_no,stock.stock_out_date,stock.order_no ord_no,quo_order.actual_amount from
(
select g_stock.customer_no,g_stock.stock_out_date,g_stock.order_no from gene_dp_stock_out_order g_stock where g_stock.frozen = false
union
select s_stock.customer_no,s_stock.stock_out_date,s_stock.order_no from stock_out_order s_stock
)stock inner join
(select qu.order_no,quo.actual_amount
from quotation_unified_order qu
left join quotation_unified_order_quotation_unified_bodies quo
on qu.id = quo.quotation_unified_order_id
union
select dqo.order_no,dqo.final_amount from dp_quotation_order dqo) quo_order
on stock.order_no = quo_order.order_no ) stock_quo
on c.customer_no = stock_quo.customer_no
) cus_ord_quo_pro_stk where ranknum =1
sql有点长,没事,看得懂,不会很难。
老大看了我写的SQL语句,有点惊讶,那么长,而且看起来有点乱。他教我怎么优化SQL,
优化后的SQL:
select row_number () over(partition by c.customer_no order by first_order.created_date_time,first_production.make_date,first_stock.stock_out_date) ranknum,
c.customer_no,c.short_name,first_order.created_date_time,first_order.ord_no,first_order.actual_amount,
first_production.make_date,first_production.ord_no,first_production.actual_amount,
first_stock.stock_out_date,first_stock.ord_no,first_stock.actual_amount
from customer c
left join
(
select * from(
select row_number () over(partition by order_quo.customer_no order by order_quo.created_date_time) ranknum,
order_quo.customer_no, order_quo.created_date_time,order_quo.ord_no,order_quo.actual_amount
from (
(select d.customer_no,d.created_date_time,d.order_no ord_no from dp_order d where d.frozen = false
union
select s.customer_no,s.created_date_time,s.order_no from split_order s
union
select f.customer_no,f.order_date,f.order_no from first_order_history_data f
)ord
inner join
(select qu.order_no,qu.actual_amount
from quotation_unified_order qu
union
select dqo.order_no,dqo.final_amount from dp_quotation_order dqo
union
select f.order_no,f.order_amount from first_order_history_data f
)quo_order
on ord.ord_no = quo_order.order_no) order_quo
) aa
where ranknum=1
) first_order on c.customer_no = first_order.customer_no
left join (
select * from(
select row_number () over(partition by prod_quo.customer_no order by prod_quo.make_date) ranknum,
prod_quo.customer_no, prod_quo.make_date,prod_quo.ord_no, prod_quo.actual_amount
from (
(
select g.ref_order_no,g.customer_no,g.make_date,g.order_no ord_no from general_dp_pp_order g where g.frozen = false
union
select p.ref_order_no,p.customer_no,p.document_make_date,p.order_no from prod_process_cabinet_order p
union
select pr.ref_order_no,pr.customer_no,pr.document_make_date,pr.order_no from prod_process_door_panel_order pr
union
select f.order_no,f.customer_no,f.production_date,f.production_no from first_order_history_data f
)production
inner join
(select qu.order_no,qu.actual_amount
from quotation_unified_order qu
union
select dqo.order_no,dqo.final_amount from dp_quotation_order dqo
union
select f.order_no,f.order_amount from first_order_history_data f
) production_quo on production.ref_order_no = production_quo.order_no ) prod_quo
) bb
where ranknum=1
) first_production on c.customer_no = first_production.customer_no
left join(
select * from(
select row_number () over(partition by stk_quo.customer_no order by stk_quo.stock_out_date) ranknum,
stk_quo.customer_no, stk_quo.stock_out_date,stk_quo.ord_no,stk_quo.actual_amount
from (
(
select g_stock.customer_no,g_stock.stock_out_date,g_stock.order_no ord_no from gene_dp_stock_out_order g_stock where g_stock.frozen = false
union
select s_stock.customer_no,s_stock.stock_out_date,s_stock.order_no from stock_out_order s_stock
union
select f.customer_no,f.stock_date,f.stock_no from first_order_history_data f
)stock
inner join
(select qu.order_no,qu.actual_amount
from quotation_unified_order qu
union
select dqo.order_no,dqo.final_amount from dp_quotation_order dqo
union
select f.order_no,f.order_amount from first_order_history_data f
) stock_quo on stock.ord_no = stock_quo.order_no ) stk_quo
) cc
where ranknum=1
) first_stock on c.customer_no = first_stock.customer_no
虽然看起来更长了,但SQL语句更优美了,看起来更好看了
另外,老大叫我在Repository层把这个SQL拆成三部分,订单,生产单,出货单,这三个部分放在常量里。一个主体,拼接三个常量。
Repository层代码:
常量部分:
static final String FIRST_ORDER = "select * from( "
+ "select row_number () over(partition by order_quo.customer_no order by "
+ "order_quo.created_date_time) ranknum, "
+ "order_quo.customer_no, order_quo.created_date_time,order_quo.ord_no ord_no1, "
+ "order_quo.actual_amount actual_amount1 from ( "
+ "(select d.customer_no,d.created_date_time,d.order_no ord_no from dp_order d where d.frozen = false "
+ "union select s.customer_no,s.created_date_time,s.order_no from split_order s "
+ "union select f.customer_no,f.order_date,f.order_no from first_order_history_data f "
+ ")ord inner join (select qu.order_no,qu.actual_amount " + "from quotation_unified_order qu union "
+ "select dqo.order_no,dqo.final_amount from dp_quotation_order dqo union "
+ "select f.order_no,f.order_amount from first_order_history_data f )quo_order "
+ "on ord.ord_no = quo_order.order_no) order_quo ) aa where ranknum=1 ";
static final String FIRST_PRODUCTION = "select * from( "
+ "select row_number () over(partition by prod_quo.customer_no order by "
+ "prod_quo.make_date) ranknum, "
+ "prod_quo.customer_no, prod_quo.make_date,prod_quo.ref_order_no, "
+ "prod_quo.actual_amount actual_amount2 from ( ( "
+ "select g.ref_order_no,g.customer_no,g.make_date,g.order_no ord_no from "
+ "general_dp_pp_order g where g.frozen = false union "
+ "select p.ref_order_no,p.customer_no,p.document_make_date,p.order_no from "
+ "prod_process_cabinet_order p union "
+ "select pr.ref_order_no,pr.customer_no,pr.document_make_date,pr.order_no from "
+ "prod_process_door_panel_order pr union "
+ "select f.order_no,f.customer_no,f.production_date,f.production_no from "
+ "first_order_history_data f "
+ ")production inner join (select qu.order_no,qu.actual_amount "
+ "from quotation_unified_order qu union "
+ "select dqo.order_no,dqo.final_amount from dp_quotation_order dqo union "
+ "select f.order_no,f.order_amount from first_order_history_data f "
+ ") production_quo on production.ref_order_no = production_quo.order_no) prod_quo ) bb "
+ "where ranknum=1 ";
static final String FIRST_STOCK = "select * from( "
+ "select row_number () over(partition by stk_quo.customer_no order by "
+ "stk_quo.stock_out_date) ranknum, "
+ "stk_quo.customer_no, stk_quo.stock_out_date,stk_quo.ord_no ord_no3, "
+ "stk_quo.actual_amount actual_amount3 from ( ( "
+ "select g_stock.customer_no,g_stock.stock_out_date,g_stock.order_no ord_no from "
+ "gene_dp_stock_out_order g_stock where g_stock.frozen = false union "
+ "select s_stock.customer_no,s_stock.stock_out_date,s_stock.order_no from stock_out_order s_stock "
+ "union select f.customer_no,f.stock_date,f.stock_no from first_order_history_data f "
+ ")stock inner join (select qu.order_no,qu.actual_amount "
+ "from quotation_unified_order qu union "
+ "select dqo.order_no,dqo.final_amount from dp_quotation_order dqo union "
+ "select f.order_no,f.order_amount from first_order_history_data f "
+ ") stock_quo on stock.ord_no = stock_quo.order_no ) stk_quo ) cc where ranknum=1 ";
主体拼接常量:
@Query(value = "select row_number () over(partition by c.customer_no order by first_order.created_date_time,"
+ "first_production.make_date,first_stock.stock_out_date) ranknum, "
+ "c.customer_no,c.short_name,first_order.created_date_time,first_order.ord_no1,"
+ "first_order.actual_amount1, "
+ "first_production.make_date,first_production.ref_order_no,first_production.actual_amount2, "
+ " first_stock.stock_out_date,first_stock.ord_no3,first_stock.actual_amount3 "
+ " from customer c left join ( " + FIRST_ORDER
+ ") first_order on c.customer_no = first_order.customer_no left join ( " + FIRST_PRODUCTION
+ ") first_production on c.customer_no = first_production.customer_no left join( " + FIRST_STOCK
+ ") first_stock on c.customer_no = first_stock.customer_no "
+ "where c.short_name like %:shortName% limit :pageSize offset :pageOffset", nativeQuery = true)
List<Object[]> findAllByPageWithShortName(@Param(value = "shortName") String shortName,
@Param(value = "pageSize") Integer pageSize, @Param(value = "pageOffset") Integer pageOffset);
这样就好看多了。如果不拆分的话,看起来就一大堆,时间长了,我再倒回去看,我都能看晕,更何况别人,后期维护也很麻烦。
实体类代码,用来显示列表数据:
public class FirstOrderReport {
private String customerNo;
private String customerName;
private Date orderDate;
private String orderNo;
private String orderAmount;
private Date productionDate;
private String productionNo;
private String productionAmount;
private Date stockDate;
private String stockNo;
private String stockAmount;
//getter、setter...
}
service层代码,业务逻辑
@Transactional
@Service
public class FirstOrderReportServiceImpl implements FirstOrderReportService {
@Autowired
private CustomerRepository customerRepository;
@Override
//分页+查询列表查询
public Page<FirstOrderReport> getFirstOrderReports(String customerName, Integer pageIndex, Integer pageSize) {
Pageable pageable = new PageRequest(pageIndex, pageSize);
List<FirstOrderReport> firstorderReports = customerRepository
.findAllByPageWithShortName(customerName, pageSize, pageSize * pageIndex).stream()
.map(firstorder -> genFirstorderReport(firstorder)).collect(Collectors.toList());
Long totalCount = customerRepository.getCountByShortName(customerName);
return new PageImpl<FirstOrderReport>(firstorderReports, pageable, totalCount);
}
//对查询出来的数据构建出实体类对象
private FirstOrderReport genFirstorderReport(Object[] firstorder) {
FirstOrderReport firstorderReport = new FirstOrderReport();
if (firstorder != null && firstorder.length > 0) {
firstorderReport.setCustomerNo(firstorder[1].toString());
firstorderReport.setCustomerName(firstorder[2].toString());
firstorderReport.setOrderDate(
firstorder[3] == null ? null : DateFormat.getFormatDate("yyyy-MM-dd", firstorder[3].toString()));
firstorderReport.setOrderNo(firstorder[4] == null ? "" : firstorder[4].toString());
firstorderReport.setOrderAmount(firstorder[5] == null ? "" : firstorder[5].toString());
firstorderReport.setProductionDate(
firstorder[6] == null ? null : DateFormat.getFormatDate("yyyy-MM-dd", firstorder[6].toString()));
firstorderReport.setProductionNo(firstorder[7] == null ? "" : firstorder[7].toString());
firstorderReport.setProductionAmount(firstorder[8] == null ? "" : firstorder[8].toString());
firstorderReport.setStockDate(
firstorder[9] == null ? null : DateFormat.getFormatDate("yyyy-MM-dd", firstorder[9].toString()));
firstorderReport.setStockNo(firstorder[10] == null ? "" : firstorder[10].toString());
firstorderReport.setStockAmount(firstorder[11] == null ? "" : firstorder[11].toString());
}
return firstorderReport;
}
}
controller层代码,这个不多说
@RestController
@RequestMapping("api/marketingmanagement/first-order-report")
public class FirstOrderReportController extends ExceptionResponse {
@Autowired
private FirstOrderReportService firstOrderReportService;
@RequestMapping(value = "/gene-first-order-reports", method = RequestMethod.GET, produces = "application/json;charset=UTF-8")
public Page<FirstOrderReport> getFirstorderReports(@RequestParam String customerName,
@RequestParam Integer pageIndex, @RequestParam Integer pageSize) {
return firstOrderReportService.getFirstOrderReports(customerName.trim(), pageIndex, pageSize);
}
}